Database Schema Overview

Column-by-column reference: see columns.md. This page is the tables-and-relationships overview only.

VERA's data lives in Postgres (Supabase). Every tenant's rows are isolated with row-level security keyed off a tenant_id UUID set per request via SET LOCAL app.tenant_id. The bot reads and writes exclusively through the DAL (lib/dal.js) so RLS scoping is uniform across every code path.

---

Tables

Table	Purpose	Key operations
time_entries	Every logged hour	INSERT, UPDATE, DELETE
projects	Project definitions (parents + period/sprint children)	INSERT, per-column UPDATE
users	Team members	INSERT, UPDATE role/status
clients	Client organisations	INSERT, UPDATE name/status
tasks	Work categories	INSERT, UPDATE name
project_tasks	Tasks enabled per project	INSERT (enable), DELETE (disable)
budgets	Per-person hour envelopes	INSERT, UPDATE hours/status/task
disciplines	Functional roles	INSERT, UPDATE name/status
conversations	24-hour chat history; entities JSONB column holds typed entity references from each turn's tool calls	INSERT
pending_pto	Time off requests	INSERT, UPDATE approval/rejection
unresolved_items	Flagged operational issues	INSERT, UPDATE status
company_calendar	Holidays and events	INSERT, DELETE
rate_history	Hourly cost per person (versioned)	INSERT only
liabilities	Vendor payment commitments	INSERT, UPDATE status

The migration files (migrations/*.js) are the schema-of-record. docs/reference/schema/columns.md is the working column reference. The DAL reader functions in lib/dal.postgres.js project each table into the object shape callers consume.

---

Tenant isolation

Every connection enters a withTenant(callback) block that opens a transaction and runs:

SET LOCAL app.tenant_id = '<tenant uuid>';

The runtime role vera_app is NOBYPASSRLS, so policies on every table filter tenant_id = current_setting('app.tenant_id')::uuid automatically. Application code never filters by tenant — RLS does it. A query that "forgets" to scope still returns only the current tenant's rows.

The privileged postgres role (set via MIGRATION_DATABASE_URL) bypasses RLS and is used only by migrations.

---

ID strategy

Per-tenant integer IDs are computed at write time as MAX(existing) + 1 within the tenant. IDs are permanent and sequential within a tenant — never reused, even when records are deleted. Time entry #42 always refers to the same entry, enabling reliable audit trails and edit/delete operations via natural language.

---

Foreign keys

users ──┬── user_id          ─→ time_entries, budgets, pending_pto, rate_history
        ├── pm_user_id       ─→ projects
        ├── discipline_id    ─→ disciplines
        ├── created_by       ─→ budgets, liabilities
        ├── vendor_user_id   ─→ liabilities
        └── affected_user_id ─→ unresolved_items

projects ──┬── project_id       ─→ time_entries, project_tasks, budgets
           ├── client_id        ─→ clients
           ├── parent_project_id ─→ projects (self-ref, periods/sprints)
           └── affected_project_id ─→ unresolved_items

clients ──── client_id ─→ projects

tasks ──┬── task_id ─→ time_entries, project_tasks, budgets

---

Migrations

Schema is managed by node-pg-migrate (migrations/ directory). To apply pending migrations locally:

npm run migrate:up

In production this runs automatically on boot via scripts/start.js when MIGRATION_DATABASE_URL is set. An applied migration is recorded in the pgmigrations bookkeeping table; re-running is idempotent.

For schema changes:

1. Create the migration: npx node-pg-migrate create <descriptive_name>
2. Implement up and down
3. Apply locally: npm run migrate:up
4. Update the relevant reader in lib/dal.postgres.js and the typed updater (if applicable)
5. Add or update tests in tests/mcp-routes.test.js and tests/dal-postgres.test.js
6. Update docs/reference/schema/index.md (if a table is added or removed) and docs/reference/schema/columns.md (every schema change touches columns)