database-schema

Database Schema Guidelines

Kaneo uses Drizzle ORM with PostgreSQL. This document outlines schema conventions and best practices.

Schema Definition

All schemas are defined in apps/api/src/database/schema.ts:

import { pgTable, text, timestamp, boolean, integer, index } from "drizzle-orm/pg-core";
import { createId } from "@paralleldrive/cuid2";

export const taskTable = pgTable(
  "task",
  {
    id: text("id")
      .$defaultFn(() => createId())
      .primaryKey(),
    projectId: text("project_id")
      .notNull()
      .references(() => projectTable.id, {
        onDelete: "cascade",
        onUpdate: "cascade",
      }),
    title: text("title").notNull(),
    description: text("description"),
    status: text("status").notNull().default("to-do"),
    createdAt: timestamp("created_at", { mode: "date" }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { mode: "date" })
      .defaultNow()
      .$onUpdate(() => new Date())
      .notNull(),
  },
  (table) => [
    index("task_projectId_idx").on(table.projectId),
  ],
);

Naming Conventions

1. Table names: Use singular, lowercase with underscores: task, workspace_user
2. Column names: Use snake_case: project_id, created_at, updated_at
3. Index names: Format: {table}_{column}_idx
4. Foreign key columns: End with _id: project_id, user_id

// Good: Consistent naming
export const workspaceUserTable = pgTable("workspace_user", {
  id: text("id").primaryKey(),
  workspaceId: text("workspace_id").notNull(),
  userId: text("user_id").notNull(),
  role: text("role").notNull(),
});

// Bad: Inconsistent naming
export const workspaceUserTable = pgTable("WorkspaceUsers", {
  ID: text("ID").primaryKey(),
  workspace: text("workspace").notNull(),
  user: text("user").notNull(),
});

Required Fields

Every table should include:

1. Primary Key: id field using CUID2
2. Timestamps: created_at and updated_at with proper defaults
3. Foreign Keys: Proper references with cascade options