src/instructions/database-design-instructions.md

Database Design Instructions

Parent Document: main-instructions.md - Read that first!

🎯 Database Design Principles

Rule #1: Mirror the UI Structure

Every table and column must correspond to what users see and interact with in the UI.

Example: If the Work Order form has fields for:

• Title
• Description
• Status
• Assigned To
• Due Date

Then the work_orders table must have exactly these columns (plus system fields like id, createdAt, updatedAt).

Schema Design Process

1. Start with the UI:

   • List all forms and their fields
   • Identify all data displayed in lists/tables
   • Note all filters and search criteria
   • Document all relationships shown

2. Design the Tables:

   • One table per entity (User, WorkOrder, Project, etc.)
   • Include only fields that appear in UI or are needed for system function
   • Add system fields: id, createdAt, updatedAt, (optionally deletedAt)
   • Define relationships based on UI connections

3. Verify Completeness:

   • Can every UI element be populated from the database?
   • Can every user action be persisted to the database?
   • Are all filters and sorts supported by the schema?

⚡ Performance & Scalability

Indexes for Frequently Queried Columns

ALWAYS add indexes for frequently queried columns:

import {
  pgTable,
  uuid,
  text,
  timestamp,
  index,
  pgEnum,
} from "drizzle-orm/pg-core";

export const workOrderStatusEnum = pgEnum("work_order_status", [
  "draft",
  "pending",
  "approved",
  "completed",
]);

export const workOrders = pgTable(
  "work_orders",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    title: text("title").notNull(),
    status: workOrderStatusEnum("status").default("draft").notNull(),
    clientId: uuid("client_id").references(() => clients.id),
    assignedTo: uuid("assigned_to").references(() => users.id),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (table) => ({
    // Index for status filtering (common query)
    statusIdx: index("work_orders_status_idx").on(table.status),
    // Index for foreign keys
    clientIdIdx: index("work_orders_client_id_idx").on(table.clientId),
    assignedToIdx: index("work_orders_assigned_to_idx").on(table.assignedTo),
    // Index for sorting by date
    createdAtIdx: index("work_orders_created_at_idx").on(table.createdAt),
  }),
);

When to add indexes:

• Fields used in WHERE clauses
• Fields used in ORDER BY clauses
• Foreign keys
• Status/enum fields frequently filtered
• Date fields used for sorting

Query Optimization

1. Always Use Pagination:

// ❌ BAD - loads all records
const projects = await db.select().from(projects);

// ✅ GOOD - paginated results
const pageSize = 50;
const projects = await db
  .select()
  .from(projects)
  .limit(pageSize)
  .offset(page * pageSize)
  .orderBy(desc(projects.createdAt));

2. Select Only Needed Fields:

// ❌ BAD - loads all columns
const projects = await db.select().from(projects);

// ✅ GOOD - select only needed fields
const projects = await db
  .select({
    id: projects.id,
    title: projects.title,
    status: projects.status,
    createdAt: projects.createdAt,
  })
  .from(projects);

3. Avoid N+1 Query Problems:

// ❌ BAD - N+1 query problem
const projects = await db.select().from(projects);
for (const project of projects) {
  // This runs a query for EACH project!
  const client = await db
    .select()
    .from(clients)
    .where(eq(clients.id, project.clientId));
}

// ✅ GOOD - single query with join
const projectsWithClients = await db
  .select({
    projectId: projects.id,
    projectTitle: projects.title,
    clientName: clients.name,
  })
  .from(projects)
  .leftJoin(clients, eq(projects.clientId, clients.id));

// ✅ ALSO GOOD - batch query
const clientIds = projects.map((p) => p.clientId).filter(Boolean);
const clientList = await db
  .select()
  .from(clients)
  .where(inArray(clients.id, clientIds));

4. Use Database Aggregations:

// ❌ BAD - loading all data to count in memory
const projects = await db.select().from(projects);
const activeCount = projects.filter((p) => p.status === "active").length;

// ✅ GOOD - database-level aggregation
const [result] = await db
  .select({ count: sql<number>`count(*)` })
  .from(projects)
  .where(eq(projects.status, "active"));

Connection Pooling

Use basic connection pooling (good enough for now, easy to optimize later):

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";

// Simple pool configuration - good for single instance
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10, // Start small, increase when needed
  idleTimeoutMillis: 30000,
});

export const db = drizzle(pool);

Pool sizing:

• Start with max: 10 (enough for development and initial production)
• Increase when you see connection timeout errors
• When scaling horizontally: adjust per instance (e.g., 5-10 per instance)

Performance Monitoring

Add these to your queries for debugging:

// Log slow queries
const startTime = Date.now();
const result = await db.select().from(largeTable);
const duration = Date.now() - startTime;

if (duration > 1000) {
  console.warn(`Slow query detected: ${duration}ms`);
}

// Use EXPLAIN ANALYZE in development
const explained = await db.execute(sql`
  EXPLAIN ANALYZE
  SELECT * FROM projects 
  WHERE data->>'status' = 'active'
`);
console.log(explained);

🗄️ Drizzle ORM Standards

Table Definition Template

import {
  pgTable,
  text,
  timestamp,
  integer,
  boolean,
  uuid,
  jsonb,
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

export const tableName = pgTable("table_name", {
  // Primary Key - always UUID
  id: uuid("id").defaultRandom().primaryKey(),

  // Business Fields (based on UI)
  // Add fields that appear in the UI here

  // Foreign Keys
  userId: uuid("user_id")
    .notNull()
    .references(() => users.id),

  // System Fields - ALWAYS include these
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),

  // Optional: Soft Delete
  deletedAt: timestamp("deleted_at"),
});

// Define relations
export const tableNameRelations = relations(tableName, ({ one, many }) => ({
  user: one(users, {
    fields: [tableName.userId],
    references: [users.id],
  }),
  // Add other relations here
}));

Column Type Guidelines

UI Element	Database Type	Example
Short text input	text('column_name')	Name, Title
Long text / Textarea	text('column_name')	Description, Notes
Number input	integer('column_name') or real('column_name')	Age, Price
Checkbox	boolean('column_name')	isActive, isCompleted
Date picker	timestamp('column_name')	dueDate, startDate
Dropdown (predefined)	text('column_name') + enum type	Status: 'draft' | 'active' | 'completed'
Multi-select	jsonb('column_name') or relation table	Tags, Categories
File upload	text('column_name') (store URL/path)	avatarUrl, documentPath
Rich text editor	text('column_name')	Content, Description (HTML/Markdown)

Enum Types

For fields with predefined values (like status), define TypeScript enums:

// Define enum
export const workOrderStatusEnum = pgEnum("work_order_status", [
  "draft",
  "pending_review",
  "approved",
  "in_progress",
  "completed",
  "cancelled",
]);

// Use in table
export const workOrders = pgTable("work_orders", {
  id: uuid("id").defaultRandom().primaryKey(),
  status: workOrderStatusEnum("status").default("draft").notNull(),
  // ... other fields
});

// Export type
export type WorkOrderStatus = (typeof workOrders.status.enumValues)[number];

Relationship Patterns

One-to-Many

// One User has many WorkOrders
export const users = pgTable('users', { ... });
export const workOrders = pgTable('work_orders', {
  id: uuid('id').defaultRandom().primaryKey(),
  userId: uuid('user_id').notNull().references(() => users.id),
});

export const usersRelations = relations(users, ({ many }) => ({
  workOrders: many(workOrders),
}));

export const workOrdersRelations = relations(workOrders, ({ one }) => ({
  user: one(users, {
    fields: [workOrders.userId],
    references: [users.id],
  }),
}));

Many-to-Many

// Many Projects have many Talents (through junction table)
export const projects = pgTable('projects', { ... });
export const talents = pgTable('talents', { ... });

export const projectTalents = pgTable('project_talents', {
  projectId: uuid('project_id').notNull().references(() => projects.id),
  talentId: uuid('talent_id').notNull().references(() => talents.id),
  // Additional junction data
  role: text('role'),
  rate: real('rate'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  pk: primaryKey(table.projectId, table.talentId),
}));

🔐 Row Level Security (RLS)

RLS Policy Template

Every table that stores user-specific data MUST have RLS policies.

-- Enable RLS on table
ALTER TABLE work_orders ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see their own work orders or ones assigned to them
CREATE POLICY "Users can view own work orders"
ON work_orders FOR SELECT
USING (
  auth.uid() = user_id
  OR auth.uid() = assigned_to
  OR EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_id = auth.uid()
    AND role IN ('admin', 'manager')
  )
);

-- Policy: Users can insert their own work orders
CREATE POLICY "Users can create work orders"
ON work_orders FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Policy: Users can update their own work orders
CREATE POLICY "Users can update own work orders"
ON work_orders FOR UPDATE
USING (auth.uid() = user_id OR auth.uid() = assigned_to);

-- Policy: Only admins can delete
CREATE POLICY "Admins can delete work orders"
ON work_orders FOR DELETE
USING (
  EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_id = auth.uid() AND role = 'admin'
  )
);

RLS Best Practices

1. Always enable RLS on tables with user data
2. Test policies with different user roles
3. Use auth context (auth.uid()) to filter data
4. Combine policies - Postgres ORs them together
5. Performance: Index columns used in RLS policies
6. Debugging: Use EXPLAIN to see if RLS is applied

📊 Query Patterns with Drizzle

Basic CRUD

import { db } from "@/lib/getDatabase";
import { workOrders, users } from "@/schema/schema";
import { eq, and, or, desc, like, gte } from "drizzle-orm";

// Create
const newWorkOrder = await db
  .insert(workOrders)
  .values({
    title: "New Work Order",
    description: "Description here",
    userId: currentUserId,
  })
  .returning();

// Read (single)
const workOrder = await db.query.workOrders.findFirst({
  where: eq(workOrders.id, workOrderId),
  with: {
    user: true, // Include related user
  },
});

// Read (multiple with filters)
const filteredOrders = await db.query.workOrders.findMany({
  where: and(
    eq(workOrders.status, "active"),
    gte(workOrders.dueDate, new Date()),
  ),
  orderBy: [desc(workOrders.createdAt)],
  limit: 10,
  offset: 0,
});

// Update
await db
  .update(workOrders)
  .set({
    status: "completed",
    updatedAt: new Date(),
  })
  .where(eq(workOrders.id, workOrderId));

// Delete (soft delete preferred)
await db
  .update(workOrders)
  .set({ deletedAt: new Date() })
  .where(eq(workOrders.id, workOrderId));

// Hard delete (use sparingly)
await db.delete(workOrders).where(eq(workOrders.id, workOrderId));

Transactions

Use transactions for operations that must succeed or fail together:

await db.transaction(async (tx) => {
  // Create work order
  const [workOrder] = await tx
    .insert(workOrders)
    .values({
      title: "New Order",
      userId: currentUserId,
    })
    .returning();

  // Create related tasks
  await tx.insert(tasks).values([
    { workOrderId: workOrder.id, title: "Task 1" },
    { workOrderId: workOrder.id, title: "Task 2" },
  ]);

  // Update project status
  await tx
    .update(projects)
    .set({ status: "in_progress" })
    .where(eq(projects.id, projectId));
});

CRITICAL RULES FOR TRANSACTIONS:

1. Always use transactions when modifying multiple tables

   // ❌ WRONG - No transaction for multi-table operation
   await db.insert(orders).values({ ... })
   await db.insert(orderItems).values({ ... }) // If this fails, order still created!
   
   // ✅ CORRECT - Transaction ensures atomicity
   await db.transaction(async (tx) => {
     await tx.insert(orders).values({ ... })
     await tx.insert(orderItems).values({ ... }) // All or nothing
   })
   

2. Use transactions for dependent operations

   • Creating parent + children records
   • Updating related data
   • Moving data between tables
   • Any operation where partial completion would leave invalid state

3. Pessimistic Locking when needed

   // Use FOR UPDATE to lock rows during transaction
   await db.transaction(async (tx) => {
     // Lock the row to prevent concurrent modifications
     const [inventory] = await tx
       .select()
       .from(inventoryTable)
       .where(eq(inventoryTable.productId, productId))
       .for("update"); // Pessimistic lock
   
     if (inventory.quantity < requestedAmount) {
       throw new Error("Insufficient inventory");
     }
   
     // Update with locked row - safe from race conditions
     await tx
       .update(inventoryTable)
       .set({ quantity: inventory.quantity - requestedAmount })
       .where(eq(inventoryTable.productId, productId));
   });
   

4. When to use pessimistic locking:

   • Inventory/stock management (prevent overselling)
   • Financial transactions (prevent double-spending)
   • Ticket/seat reservations (prevent double-booking)
   • Counter increments that must be accurate
   • Any scenario where concurrent updates could cause inconsistency

5. Transaction best practices:

   // ✅ GOOD - Keep transactions short and focused
   await db.transaction(async (tx) => {
     // Only database operations here
     const result = await tx.insert(...)
     await tx.update(...)
     return result
   })
   
   // ❌ BAD - Don't do slow operations in transactions
   await db.transaction(async (tx) => {
     await tx.insert(...)
     await sendEmail(...) // ❌ Slow! Locks database
     await callExternalAPI(...) // ❌ Slow! Locks database
   })
   
   // ✅ CORRECT - Do slow operations after transaction
   const result = await db.transaction(async (tx) => {
     return await tx.insert(...)
   })
   await sendEmail(...) // Outside transaction
   await callExternalAPI(...) // Outside transaction
   

6. Atomic operations:

   • Each transaction is atomic (all or nothing)
   • If any operation fails, entire transaction rolls back
   • Database remains consistent

7. Example: Complete order processing

   async function processOrder(userId: string, items: OrderItem[]) {
     // Start transaction
     const orderId = await db.transaction(async (tx) => {
       // 1. Create order
       const [order] = await tx
         .insert(orders)
         .values({
           data: { userId, status: "pending", total: 0 },
         })
         .returning();
   
       let total = 0;
   
       // 2. Process each item with pessimistic locking
       for (const item of items) {
         // Lock inventory row
         const [inventory] = await tx
           .select()
           .from(inventoryTable)
           .where(eq(inventoryTable.productId, item.productId))
           .for("update");
   
         if (!inventory || inventory.quantity < item.quantity) {
           throw new Error(`Insufficient stock for ${item.productId}`);
         }
   
         // Deduct inventory
         await tx
           .update(inventoryTable)
           .set({ quantity: inventory.quantity - item.quantity })
           .where(eq(inventoryTable.productId, item.productId));
   
         // Create order item
         await tx.insert(orderItems).values({
           data: {
             orderId: order.id,
             productId: item.productId,
             quantity: item.quantity,
             price: inventory.price,
           },
         });
   
         total += inventory.price * item.quantity;
       }
   
       // 3. Update order total
       await tx
         .update(orders)
         .set({ data: { ...order.data, total, status: "confirmed" } })
         .where(eq(orders.id, order.id));
   
       return order.id;
     });
   
     // 4. Send confirmation email (OUTSIDE transaction)
     await sendOrderConfirmationEmail(orderId);
   
     return orderId;
   }
   

🎯 Migration Strategy

Creating Migrations

# Generate migration from schema changes
npx drizzle-kit generate:pg

# Apply migrations
npx drizzle-kit push:pg

Migration Best Practices

1. Never edit generated migrations - change schema and regenerate
2. Test migrations on local database first
3. Backup before migration in production
4. Make migrations reversible when possible
5. Add indexes separately for large tables (avoid locking)

---

💡 Custom Database Guidelines

⚠️ CRITICAL: Proper Schema Design (NO JSONB)

MANDATORY: Design proper database schemas with typed columns

Every table MUST have properly defined columns that match the UI fields. DO NOT use JSONB for business data.

// ❌ BAD - DO NOT USE JSONB for business data
export const workOrders = pgTable("work_orders", {
  id: uuid("id").defaultRandom().primaryKey(),
  data: jsonb("data").notNull(), // ❌ FORBIDDEN
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// ✅ GOOD - Proper schema with typed columns
export const workOrders = pgTable("work_orders", {
  id: uuid("id").defaultRandom().primaryKey(),
  title: text("title").notNull(),
  description: text("description"),
  status: workOrderStatusEnum("status").default("draft").notNull(),
  assignedTo: uuid("assigned_to").references(() => users.id),
  dueDate: timestamp("due_date"),
  budget: real("budget"),
  priority: integer("priority").default(0),
  createdBy: uuid("created_by")
    .notNull()
    .references(() => users.id),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
  deletedAt: timestamp("deleted_at"),
});

Why NO JSONB?

• ✅ Type safety at database level
• ✅ Better query performance with indexes
• ✅ Data integrity with constraints
• ✅ Proper foreign keys
• ✅ Easier migrations and schema evolution
• ✅ Clear documentation of data structure

📋 Complete Migration Files with Revert Functions

MANDATORY: Every schema change MUST include migration files with both UP and DOWN functions

When creating or modifying database schemas, you MUST provide complete migration files:

// drizzle/migrations/0001_create_work_orders.ts

import { sql } from "drizzle-orm";
import type { PostgresJsDatabase } from "drizzle-orm/postgres-js";

/**
 * Migration: Create work_orders table
 * Created: 2026-02-13
 */

// UP: Apply migration
export async function up(db: PostgresJsDatabase) {
  await db.execute(sql`
    -- Create enum for status
    CREATE TYPE work_order_status AS ENUM (
      'draft',
      'pending_review',
      'approved',
      'in_progress',
      'completed',
      'cancelled'
    );

    -- Create work_orders table
    CREATE TABLE work_orders (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      title TEXT NOT NULL,
      description TEXT,
      status work_order_status NOT NULL DEFAULT 'draft',
      assigned_to UUID REFERENCES users(id) ON DELETE SET NULL,
      due_date TIMESTAMP,
      budget REAL,
      priority INTEGER DEFAULT 0,
      created_by UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      created_at TIMESTAMP NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
      deleted_at TIMESTAMP
    );

    -- Create indexes
    CREATE INDEX idx_work_orders_status ON work_orders(status);
    CREATE INDEX idx_work_orders_assigned_to ON work_orders(assigned_to);
    CREATE INDEX idx_work_orders_created_by ON work_orders(created_by);
    CREATE INDEX idx_work_orders_due_date ON work_orders(due_date);
    CREATE INDEX idx_work_orders_created_at ON work_orders(created_at);

    -- Create updated_at trigger
    CREATE OR REPLACE FUNCTION update_updated_at_column()
    RETURNS TRIGGER AS $$
    BEGIN
      NEW.updated_at = NOW();
      RETURN NEW;
    END;
    $$ language 'plpgsql';

    CREATE TRIGGER update_work_orders_updated_at
      BEFORE UPDATE ON work_orders
      FOR EACH ROW
      EXECUTE FUNCTION update_updated_at_column();
  `);
}

// DOWN: Revert migration
export async function down(db: PostgresJsDatabase) {
  await db.execute(sql`
    -- Drop trigger first
    DROP TRIGGER IF EXISTS update_work_orders_updated_at ON work_orders;

    -- Drop function (only if not used by other tables)
    -- DROP FUNCTION IF EXISTS update_updated_at_column();

    -- Drop indexes
    DROP INDEX IF EXISTS idx_work_orders_status;
    DROP INDEX IF EXISTS idx_work_orders_assigned_to;
    DROP INDEX IF EXISTS idx_work_orders_created_by;
    DROP INDEX IF EXISTS idx_work_orders_due_date;
    DROP INDEX IF EXISTS idx_work_orders_created_at;

    -- Drop table
    DROP TABLE IF EXISTS work_orders;

    -- Drop enum type
    DROP TYPE IF EXISTS work_order_status;
  `);
}

Migration File Requirements:

1. UP function - Creates tables, columns, indexes, triggers
2. DOWN function - Reverts ALL changes made in UP (in reverse order)
3. Clean SQL - Use raw SQL for complex operations
4. Order matters - Drop in reverse order of creation
5. Comments - Document what each section does

📁 Migration File Structure

drizzle/
  migrations/
    0001_create_users.ts         # First migration
    0002_create_work_orders.ts   # Second migration
    0003_add_work_order_tags.ts  # Third migration
    meta/
      _journal.json              # Migration history

Complete Schema Design Process

Step 1: Analyze UI Requirements

UI shows Work Order form with:
- Title (text input, required)
- Description (textarea, optional)
- Status (dropdown: draft, pending, approved, in progress, completed)
- Assigned To (user select)
- Due Date (date picker)
- Budget (number input)
- Priority (1-5 selector)

Step 2: Design Drizzle Schema

// src/schema/work-orders.ts
import {
  pgTable,
  uuid,
  text,
  timestamp,
  real,
  integer,
  pgEnum,
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";
import { users } from "./users";

// Define enum for status values
export const workOrderStatusEnum = pgEnum("work_order_status", [
  "draft",
  "pending_review",
  "approved",
  "in_progress",
  "completed",
  "cancelled",
]);

// Define table with typed columns
export const workOrders = pgTable(
  "work_orders",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    title: text("title").notNull(),
    description: text("description"),
    status: workOrderStatusEnum("status").default("draft").notNull(),
    assignedTo: uuid("assigned_to").references(() => users.id, {
      onDelete: "set null",
    }),
    dueDate: timestamp("due_date"),
    budget: real("budget"),
    priority: integer("priority").default(0),
    createdBy: uuid("created_by")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
    deletedAt: timestamp("deleted_at"),
  },
  (table) => ({
    // Indexes for common queries
    statusIdx: index("idx_work_orders_status").on(table.status),
    assignedToIdx: index("idx_work_orders_assigned_to").on(table.assignedTo),
    createdByIdx: index("idx_work_orders_created_by").on(table.createdBy),
    dueDateIdx: index("idx_work_orders_due_date").on(table.dueDate),
  }),
);

// Define relations
export const workOrdersRelations = relations(workOrders, ({ one }) => ({
  assignee: one(users, {
    fields: [workOrders.assignedTo],
    references: [users.id],
  }),
  creator: one(users, {
    fields: [workOrders.createdBy],
    references: [users.id],
  }),
}));

// Export inferred types
export type WorkOrder = typeof workOrders.$inferSelect;
export type NewWorkOrder = typeof workOrders.$inferInsert;
export type WorkOrderStatus = (typeof workOrders.status.enumValues)[number];

Step 3: Create Migration File

// drizzle/migrations/0002_create_work_orders.ts
// Include BOTH up() and down() functions as shown above

Step 4: Create Zod Schemas for Validation

// src/lib/schemas/work-order-schemas.ts
import { z } from "zod";

export const workOrderStatusValues = [
  "draft",
  "pending_review",
  "approved",
  "in_progress",
  "completed",
  "cancelled",
] as const;

export const createWorkOrderSchema = z.object({
  title: z.string().min(1, "Title is required").max(200),
  description: z.string().max(2000).optional(),
  status: z.enum(workOrderStatusValues).default("draft"),
  assignedTo: z.string().uuid().nullable().optional(),
  dueDate: z.coerce.date().nullable().optional(),
  budget: z.number().positive().nullable().optional(),
  priority: z.number().int().min(0).max(5).default(0),
});

export const updateWorkOrderSchema = createWorkOrderSchema.partial();

export type CreateWorkOrderInput = z.infer<typeof createWorkOrderSchema>;
export type UpdateWorkOrderInput = z.infer<typeof updateWorkOrderSchema>;

Altering Existing Tables

Always provide migration with revert:

// drizzle/migrations/0005_add_work_order_tags.ts

import { sql } from "drizzle-orm";
import type { PostgresJsDatabase } from "drizzle-orm/postgres-js";

// UP: Add tags column
export async function up(db: PostgresJsDatabase) {
  await db.execute(sql`
    -- Add tags column (array of strings)
    ALTER TABLE work_orders
    ADD COLUMN tags TEXT[] DEFAULT '{}';

    -- Create GIN index for array searching
    CREATE INDEX idx_work_orders_tags ON work_orders USING GIN (tags);
  `);
}

// DOWN: Remove tags column
export async function down(db: PostgresJsDatabase) {
  await db.execute(sql`
    DROP INDEX IF EXISTS idx_work_orders_tags;
    ALTER TABLE work_orders DROP COLUMN IF EXISTS tags;
  `);
}

Indexing Strategy

Data Archival Rules

Backup & Recovery

⚠️ CRITICAL: Database Connection Security

NEVER hardcode database credentials:

// ❌ FORBIDDEN - NEVER DO THIS
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const client = postgres("postgresql://user:password123@localhost:5432/mydb");
const db = drizzle(client);

// ✅ CORRECT - Use environment variables
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const connectionString = process.env.DATABASE_URL!;
if (!connectionString) {
  throw new Error("DATABASE_URL not set in environment variables");
}

const client = postgres(connectionString);
const db = drizzle(client);

drizzle.config.ts:

import type { Config } from "drizzle-kit";

export default {
  schema: "./src/schema/schema.ts",
  out: "./drizzle",
  driver: "pg",
  dbCredentials: {
    // ✅ CORRECT - Use environment variable
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;

---

Last Updated: 2026-02-12 Version: 1.0.3