> ## Documentation Index
> Fetch the complete documentation index at: https://mako-docs.devinagiffy.xyz/llms.txt
> Use this file to discover all available pages before exploring further.

# Database

> Database schema, relationships, and maintenance

# Database

Hitler uses PostgreSQL with Drizzle ORM for type-safe database access.

## Schema Overview

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

## Entity Relationships

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

## Foreign Key Constraints

All FK constraints are designed for safe deletion without leaving orphaned data.

### CASCADE Delete (child deleted when parent deleted)

| Parent            | Child                 | Reason                                             |
| ----------------- | --------------------- | -------------------------------------------------- |
| `users`           | `platform_identities` | User's platform links are meaningless without user |
| `tasks`           | `task_logs`           | Task history is meaningless without task           |
| `inquiry_threads` | `inquiry_messages`    | Messages are meaningless without thread            |

### SET NULL on Delete (reference nullified, record kept)

| Parent        | Child Column                | Reason                                    |
| ------------- | --------------------------- | ----------------------------------------- |
| `task_drafts` | `tasks.draftId`             | Allow draft cleanup, task keeps its data  |
| `users`       | `flags.resolvedBy`          | Keep flag history even if resolver leaves |
| `users`       | `inquiry_messages.senderId` | Keep message even if sender deleted       |
| `flags`       | `inquiry_threads.flagId`    | Keep thread even if flag resolved/deleted |

### Why This Matters

Without proper ON DELETE actions:

* You can't delete task drafts that have been confirmed (FK violation)
* You can't delete users who have sent inquiry messages
* You can't clean up old data without manual cascading

## Automatic Data Cleanup

Scheduled jobs automatically clean up old data to prevent database bloat.

### Task Drafts Cleanup (Daily at 2 AM)

```typescript theme={null}
// Deletes:
// 1. Expired drafts (expiresAt < now)
// 2. Confirmed drafts older than 30 days
// 3. Abandoned/unconfirmed drafts older than 7 days
```

**Why keep confirmed drafts for 30 days?**

* Audit trail: see what user said vs what LLM parsed
* Debugging: investigate if task was created incorrectly
* After 30 days, the task itself is the source of truth

### Channel Messages Cleanup (Daily at 2 AM)

```typescript theme={null}
// Deletes:
// - channel_messages older than CONTEXT_RAW_RETENTION_DAYS (default: 7)
```

**Why 7 days?**

* Raw messages are processed into context\_observations within minutes
* Observations contain the extracted intelligence; raw text is no longer needed
* Keeps storage costs manageable for high-volume channels

### Security Audit Logs Cleanup (Monthly)

```typescript theme={null}
// Deletes:
// - security_audit_logs older than 90 days
// - rate_limit_events older than 90 days
```

**Why 90 days?**

* Sufficient for security investigations
* Compliance with typical log retention policies
* Prevents unlimited growth

## pgvector Extension

The database uses the [pgvector](https://github.com/pgvector/pgvector) extension for vector similarity search. This powers the context memory system's semantic search capabilities.

### Setup

The Docker Postgres image is `pgvector/pgvector:pg16` (not the standard `postgres:16-alpine`). The migration creates the extension:

```sql theme={null}
CREATE EXTENSION IF NOT EXISTS vector;
```

### Vector Columns

Two tables use `vector(1536)` columns for OpenAI `text-embedding-3-small` embeddings:

* `context_observations.embedding` — per-observation semantic vector
* `context_summaries.embedding` — per-entity-summary semantic vector

### HNSW Indexes

HNSW (Hierarchical Navigable Small World) indexes enable fast approximate nearest neighbor search:

```sql theme={null}
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);
```

### Similarity Search

Queries use the cosine distance operator (`<=>`):

```sql theme={null}
SELECT *, embedding <=> $1::vector AS distance
FROM context_observations
WHERE organization_id = $2
ORDER BY embedding <=> $1::vector
LIMIT 10;
```

Lower distance = more similar. A threshold of 0.3 filters out irrelevant results.

## Migrations

Drizzle generates migrations automatically when schema changes.

```bash theme={null}
# 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
```

### Migration Files

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

## Indexes

All tables have performance indexes defined in their schema files.

### Key Indexes

**tasks:**

* `user_id` - Filter by user
* `organization_id` - Filter by org
* `status` - Filter by status
* `(user_id, status)` - Composite for user's pending tasks
* `due_date` - Sort/filter by due date
* `created_at` - Sort by creation
* `client_id` - Filter by client
* `stage_id` - Filter by pipeline stage
* `(organization_id, display_id)` - Unique org-scoped display IDs

**pipeline\_stages:**

* `organization_id` - Filter by org
* `(organization_id, position)` - Ordered stage listing
* Unique `(organization_id, slug)` - Prevent duplicate slugs per org

**task\_drafts:**

* `user_id` - Filter by user
* `organization_id` - Filter by org
* `expires_at` - Cleanup job efficiency

**mood\_entries:**

* `user_id` - Filter by user
* `recorded_at` - Time-based queries
* `(user_id, recorded_at)` - User's mood history

**flags:**

* `user_id`, `organization_id` - Basic filtering
* `severity`, `status` - Dashboard queries
* `(user_id, status)` - User's active flags

**security\_audit\_logs:**

* `organization_id` - Org dashboard
* `threat_level` - Severity filtering
* `created_at` - Time-based cleanup
* `(organization_id, threat_level, created_at)` - Dashboard queries

## Best Practices

### Adding New Tables

1. Define schema in `packages/db/src/schema/`
2. Export from `packages/db/src/schema/index.ts`
3. Add appropriate FK constraints with ON DELETE actions
4. Add indexes for common query patterns
5. Generate and apply migration

### FK Constraint Guidelines

```typescript theme={null}
// 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
```

### When to Use Each

| Use Case                              | ON DELETE |
| ------------------------------------- | --------- |
| Logs/audit trail for entity           | CASCADE   |
| Optional reference (can be null)      | SET NULL  |
| Reference to lookup/config table      | NO ACTION |
| User-created content referencing user | SET NULL  |

## Manual Cleanup

If you need to manually clean up data:

```sql theme={null}
-- 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';
```

## Troubleshooting

### "Cannot delete: FK constraint violation"

This means a child table references the record. Check:

1. Is the FK set up with proper ON DELETE action?
2. If not, delete children first, then parent
3. Or update the schema and run migration

### "Relation does not exist"

Run migrations:

```bash theme={null}
pnpm db:migrate
```

### Slow queries

Check if indexes exist for your query patterns:

```sql theme={null}
EXPLAIN ANALYZE SELECT * FROM tasks WHERE user_id = '...' AND status = 'pending';
```

If no index is used, add one to the schema.