Database Schema

HIMARPL uses two databases for different purposes:

1. CockroachDB with Prisma ORM - For core projects and main infrastructure
2. PostgreSQL (NeonDB) with Drizzle ORM - For the bot functionality

Core Database Structure

Main Classes

Prisma Schema

If you want to integrate your new project with the database, you can use the Prisma Schema below.

View Complete Prisma Schema

// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "rhel-openssl-3.0.x"]
}

datasource db {
  provider = "cockroachdb"
  // NOTE: When using mysql or sqlserver, uncomment the @db.Text annotations in model Account below
  // Further reading:
  // https://next-auth.js.org/adapters/prisma#create-the-prisma-schema
  // https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#string
  url      = env("DATABASE_URL")
}

enum DepartmentType {
  BE
  DP
}

enum Role {
  admin
  member
}

model PostTag {
  id        String    @id @default(cuid())
  title     String    @unique
  slug      String    @unique
  createdAt DateTime  @default(now())
  updatedAt DateTime  @default(now()) @updatedAt
  parentId  String?
  parent    PostTag?  @relation("ParentChildren", fields: [parentId], references: [id])
  children  PostTag[] @relation("ParentChildren")
  posts     Post[]
}

model Post {
  id          String    @id @default(cuid())
  authorId    String
  title       String
  metaTitle   String
  slug        String
  content     String
  rawHtml     String
  image       String?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @default(now()) @updatedAt
  publishedAt DateTime?
  author      User      @relation(fields: [authorId], references: [id])
  tags        PostTag[]

  @@unique([authorId, slug])
}

model Department {
  id          String         @id @default(cuid())
  name        String
  acronym     String
  image       String?
  description String?
  programs    String[]
  type        DepartmentType
  createdAt   DateTime       @default(now())
  updatedAt   DateTime       @default(now()) @updatedAt
  users       User[]

  @@unique([type, acronym])
}

model SocialMedia {
  userId   String
  name     String
  username String
  url      String
  user     User   @relation(fields: [userId], references: [id])

  @@unique([userId, name, username])
}

model User {
  id            String        @id @default(cuid())
  name          String?
  email         String?       @unique
  emailVerified DateTime?
  image         String?
  username      String?       @unique
  bio           String?
  position      String?
  periods       String[]
  role          Role          @default(member)
  lastLoginAt   DateTime?
  createdAt     DateTime      @default(now())
  updatedAt     DateTime      @default(now()) @updatedAt
  departmentId  String?
  department    Department?   @relation(fields: [departmentId], references: [id])
  posts         Post[]
  socialMedia   SocialMedia[]
  accounts      Account[]
  sessions      Session[]
}

model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String? // @db.Text
  access_token      String? // @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String? // @db.Text
  session_state     String?
  user              User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

1. User Management

• Users

  • Core user profile information (name, email, bio, position)
  • Can belong to one department
  • Has role-based access (admin/member)
  • Tracks periods of involvement and login activity

• Authentication

  • Accounts: Handles OAuth connections (e.g., Google login)
  • Sessions: Manages active user sessions
  • VerificationToken: For email verification

2. Content Management

• Posts

  • Blog articles and content entries
  • Contains metadata (title, slug, image)
  • Tracks publishing status and dates
  • Each post has one author (User)

• PostTags

  • Hierarchical content categorization
  • Can have parent-child relationships
  • Used to organize posts by topics

3. Organization Structure

• Departments

  • Two main types: BE (Badan Eksekutif) and DP (Dewan Perwakilan)
  • Contains department info (name, acronym, description)
  • Tracks associated programs
  • Users can be assigned to departments

• SocialMedia

  • Links users to their social media profiles
  • Stores platform name, username, and URL

Key Relationships

1. Users & Content

• Each post must have one author
• Users can create multiple posts
• Users can have multiple social media profiles

2. Content Organization

• Posts can have multiple tags
• Tags can have parent-child relationships
• Posts have unique slugs per author

3. Organizational

• Users can belong to one department
• Departments can have multiple users
• Each user has a specific role (admin/member)

Prisma Schema Design Principles

• Type-safe with defined enums
• Proper foreign key constraints
• Scalable for future expansion
• Clear hierarchical relationships

Prisma Schema Rules

• Use PascalCase for all models
• Use camelCase for all attributes

Bot Database Structure

The bot uses PostgreSQL (NeonDB) with Drizzle ORM for managing notifications and user interactions.

Main Classes

Drizzle Schema

If you want to integrate your new project with the database, you can use the Drizzle Schema below.

View Complete Drizzle Schema

import { sql } from "drizzle-orm";
import {
  bigserial,
  index,
  pgTableCreator,
  text,
  timestamp,
  varchar,
} from "drizzle-orm/pg-core";

export const createTable = pgTableCreator((name) => `bot-himarpl_${name}`);

export const notifications = createTable(
  "notification",
  {
    chatId: bigserial("chat_id", { mode: "number" }).primaryKey(),
    firstName: varchar("first_name", { length: 256 }),
    lastName: varchar("last_name", { length: 256 }),
    username: varchar("username", { length: 256 }),
    createdAt: timestamp("created_at", { withTimezone: true })
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    notifying: text("notifying")
      .array()
      .notNull()
      .default(sql`ARRAY[]::text[]`),
  },
  (notifications) => ({
    chatIdIndex: index("chat_id_idx").on(notifications.chatId),
  })
);

Main Table: notifications

• Purpose: Tracks Telegram chat notifications and user preferences
• Table Prefix: bot-himarpl_ (uses multi-project schema pattern)

Key Fields:

• chatId: Unique identifier for Telegram chats (primary key)
• firstName: User’s first name from Telegram
• lastName: User’s last name from Telegram
• username: Telegram username
• createdAt: Timestamp of chat creation
• notifying: Array of notification topics the user is subscribed to

Technical Details:

• Uses PostgreSQL-specific types (bigserial, varchar, timestamp)
• Includes indexing on chatId for faster lookups
• Implements timezone-aware timestamps
• Uses array type for notification preferences

Drizzle Schema Design Principles

• Uses table prefix for multi-project isolation
• Implements proper PostgreSQL types
• Includes performance optimization through indexing
• Follows Drizzle ORM best practices

Drizzle Schema Rules

• Use camelCase for all models in the schema
• Use camelCase for all attributes in the models
• Use snake_case for all table names in the database
• Use snake_case for all attribute names in the database