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:**

```typescript
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:**

```typescript
// ❌ 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:**

```typescript
// ❌ 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:**

```typescript
// ❌ 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:**

```typescript
// ❌ 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):**

```typescript
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:**

```typescript
// 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

```typescript
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:

```typescript
// 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

```typescript
// 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

```typescript
// 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.

```sql
-- 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

```typescript
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:

```typescript
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**

   ```typescript
   // ❌ 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**

   ```typescript
   // 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**:

   ```typescript
   // ✅ 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**

   ```typescript
   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

```bash
# 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)

<!-- Zaktualizowano: 2026-02-13 -->

**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**.

```typescript
// ❌ 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:

```typescript
// 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**

```typescript
// 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**

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

**Step 4: Create Zod Schemas for Validation**

```typescript
// 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:**

```typescript
// 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

<!-- Dodaj tutaj zasady dotyczące indeksowania -->

### Data Archival Rules

<!-- Dodaj tutaj zasady archiwizacji danych -->

### Backup & Recovery

<!-- Dodaj tutaj procedury backup i recovery -->

### ⚠️ CRITICAL: Database Connection Security

<!-- Dodano: 2026-02-12 -->

**NEVER hardcode database credentials**:

```typescript
// ❌ 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**:

```typescript
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