/packages/db/src/schema/
├── organizations.ts         # Organizations and platform connections
├── users.ts                 # Users (+ mustChangePassword), platform identities, ramp-up config
├── projects.ts              # Projects with Slack channel links and overdue escalation config
├── clients.ts               # Clients (+ slackChannelId, description, contextNotes), deploy schedules, assigned members
├── tasks.ts                 # Tasks (+ stageId, displayId), drafts, and task logs (+ taskType, size, weight, clientId, carryForwardCount)
├── pipeline-stages.ts       # Kanban pipeline stages per org (name, slug, color, position, WIP limit)
├── moods.ts                 # Mood entries and journals
├── flags.ts                 # Flags, inquiries, and messages
├── audit.ts                 # Audit logs and security events
├── notifications.ts         # Notifications and deliveries
├── memory.ts                # Conversation sessions and user memory
├── reminders.ts             # User reminders (set via LLM, fired via BullMQ delayed jobs)
├── cards.ts                 # Yellow/red accountability cards
├── morning-threads.ts       # Daily morning threads
├── thread-submissions.ts    # User replies to morning threads
├── task-statuses.ts         # Daily task status snapshots (done/carry-forward/dropped)
├── ooo-records.ts           # Out-of-office records (manual + parsed)
├── task-type-map.ts         # Task type → size/weight keyword mapping
├── blockers.ts              # Blocker tracking with escalation ladder
├── reports.ts               # Generated reports (daily, weekly, monthly)
├── interventions.ts         # Invisible labor / intervention logs
├── action-items.ts          # Action items from reports
├── reinforcements.ts        # Positive reinforcement records
├── kpi-targets.ts           # KPI target configuration per org
├── kpi-snapshots.ts         # Weekly KPI snapshot history
├── checklists.ts            # Role-based checklists and completions
├── sops.ts                  # SOPs and SOP compliance records
├── channel-messages.ts      # Raw Slack channel messages for context extraction
├── context-observations.ts  # Extracted facts with pgvector embeddings
├── context-summaries.ts     # Entity profile summaries with embeddings
└── backfill-state.ts        # Channel message backfill progress tracking

organizations
    │
    ├── users ─────────────┬── tasks ──── task_logs
    │       │              │       └── (stageId → pipeline_stages, displayId for org-scoped IDs)
    │       │              │
    │       │              ├── task_drafts
    │       │              │
    │       │              ├── mood_entries
    │       │              │
    │       │              ├── journals
    │       │              │
    │       │              ├── flags ──── inquiry_threads ──── inquiry_messages
    │       │              │
    │       │              ├── notifications ──── notification_deliveries
    │       │              │
    │       │              ├── cards (yellow/red accountability)
    │       │              │
    │       │              ├── task_statuses (daily snapshots)
    │       │              │
    │       │              ├── ooo_records
    │       │              │
    │       │              ├── blockers (task-level blocker tracking)
    │       │              │
    │       │              ├── reminders (user reminders via BullMQ)
    │       │              │
    │       │              ├── interventions (invisible labor logs)
    │       │              │
    │       │              └── reinforcements (positive reinforcement)
    │       │
    │       └── platform_identities
    │
    ├── pipeline_stages (kanban stages per org)
    │
    ├── projects ──── tasks (via projectId)
    │
    ├── clients ──── tasks (via clientId)
    │       └── (slackChannelId, description, contextNotes)
    │
    ├── morning_threads ──── thread_submissions
    │
    ├── reports (daily, weekly, monthly) ──── action_items
    │
    ├── kpi_targets / kpi_snapshots
    │
    ├── checklists ──── checklist_completions
    │
    ├── sops ──── sop_records
    │
    ├── task_type_map
    │
    ├── channel_messages → context_observations → context_summaries
    │
    ├── backfill_state (channel backfill progress)
    │
    └── platform_connections

// Deletes:
// 1. Expired drafts (expiresAt < now)
// 2. Confirmed drafts older than 30 days
// 3. Abandoned/unconfirmed drafts older than 7 days

// Deletes:
// - channel_messages older than CONTEXT_RAW_RETENTION_DAYS (default: 7)

// Deletes:
// - security_audit_logs older than 90 days
// - rate_limit_events older than 90 days

CREATE EXTENSION IF NOT EXISTS vector;

CREATE INDEX context_observations_embedding_idx ON context_observations USING hnsw (embedding vector_cosine_ops);
CREATE INDEX context_summaries_embedding_idx ON context_summaries USING hnsw (embedding vector_cosine_ops);

SELECT *, embedding <=> $1::vector AS distance
FROM context_observations
WHERE organization_id = $2
ORDER BY embedding <=> $1::vector
LIMIT 10;

# Generate migration after schema changes
pnpm db:generate

# Apply migrations to database
pnpm db:migrate

# Push schema directly (dev only, no migration file)
pnpm db:push

/packages/db/drizzle/
├── 0000_initial.sql
├── 0001_*.sql
├── 0002_*.sql
└── 0003_*.sql   # FK constraint updates

// CASCADE: Child is meaningless without parent
references(() => parent.id, { onDelete: "cascade" });

// SET NULL: Keep record but remove reference
references(() => parent.id, { onDelete: "set null" });

// NO ACTION (default): Prevent deletion if referenced
references(() => parent.id); // Use sparingly

-- Delete old confirmed drafts
DELETE FROM task_drafts
WHERE id IN (
  SELECT td.id FROM task_drafts td
  JOIN tasks t ON t.draft_id = td.id
  WHERE td.created_at < NOW() - INTERVAL '30 days'
);

-- Delete expired drafts
DELETE FROM task_drafts
WHERE expires_at IS NOT NULL
AND expires_at < NOW();

-- Delete abandoned drafts (never confirmed)
DELETE FROM task_drafts
WHERE id NOT IN (SELECT draft_id FROM tasks WHERE draft_id IS NOT NULL)
AND created_at < NOW() - INTERVAL '7 days';

pnpm db:migrate

EXPLAIN ANALYZE SELECT * FROM tasks WHERE user_id = '...' AND status = 'pending';