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 organizations	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/window/shape
`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
`proposals`	Prospective engagements before they become projects (sales-side modelling); console-only, owner/admin	INSERT, UPDATE, DELETE (draft only)
`proposal_roles`	Planned staffing lines on a proposal (discipline/person, planned hours, optional T&M rate, optional window/shape)	INSERT, UPDATE, DELETE
`proposal_liabilities`	Fixed-fee vendor commitments modelled on a proposal	INSERT, UPDATE, DELETE
`discipline_levels`	Per-(discipline, level) salary bands (`salary_min`/`salary_max`, levels 1–4) for compensation benchmarking; owner-only, console-only	INSERT, UPDATE
`user_attribute_history`	Audit trail for `users.level` / `users.discipline_id` changes; owner-only, console-only, audit-silent, forward-only (no backfill)	INSERT only
`message_log`	Scheduled-DM delivery log — one row per morning/evening/renewal `sendDM` attempt, success or failure; owner/admin-only, console-only, pruned to 90 days	INSERT, DELETE (prune)
`time_approvals`	T&M-only periodic time approval — one row per `(project_id, period)` (business rule 28 amendment); owner/admin or PM-of-record, console-only, audit-silent	INSERT, UPDATE (re-approve, in place)

budgets.start_date / budgets.end_date (migration 0036) are an optional window on an envelope — both nullable, no default. Null (the default, and every envelope before this migration) means today's behavior: a project-lifetime budget with no sense of timing. A window is a bound, not a schedule, and as of this migration it is storage only — nothing reads it yet.

budgets.week_hours (migration 0037) is an optional shape for an envelope's hours across the weeks of its window — a JSONB array of { week, hours }, week always a Monday. Null (the default) means unshaped. A window is a prerequisite: the weeks the shape can describe are exactly the Monday-aligned weeks the window spans. Console-only (owner/manager, never reachable from Slack). The shape is a forecast the PM owns, never an instruction handed to the person whose hours they are — no calculation, alert, chip, or signal anywhere may derive from a deviation between the shape and logged time, now or in any future issue. As of this migration it is storage only — nothing consumes it in computeAvailableHours, the capacity heatmap, or the burn-up yet.

proposal_roles.start_date / end_date / week_hours (migration 0038) mirror budgets' window and shape exactly, on a proposal staffing line instead of an allocation envelope — same nullable-by-default columns, same validators (lib/week-shape.js, extracted from envelopes.js so this table doesn't have to import an operational router), same "a window is a bound, not a schedule" and "the shape is a forecast, never an instruction" rules. Storage + editing only as of this migration — lib/proposal-capacity.js does not read these columns yet; making the capacity simulation overlay proposed demand week-by-week is a deliberate follow-up.

user_attribute_history (migration 0039) records every change to users.level and users.discipline_id — the two compensation-adjacent fields that were previously updated in place with no trace. One row per field changed (field is 'level' or 'discipline_id'), storing old_value/new_value as text (discipline_id as the id, never the name — names are resolved at render time so a later discipline rename doesn't rewrite history) so the table can carry other fields later without a migration. user_id is a soft reference, same precedent as discipline_levels.discipline_id. Forward-only: there is nothing to backfill — history starts the day this shipped. Written from update_user_role (mcp-server.js) only when the resolved new value differs from the current one — a save that doesn't actually change level or discipline writes zero rows. The write is best-effort: a history-write failure is logged and swallowed, and never fails the user update it's attached to (same pattern as checkExceptionQueue's post-write hook on log_time). Owner-only, console-only (absent from MCP_TOOLS, unreachable from Slack), and audit-silent — same reasoning as set_rate's rule-21 suppression, since level is compensation-adjacent and the audit channel is team-visible. Never read by any operational query in lib/dashboard.js, lib/forecast.js, or lib/availability.js. Displayed as a second "Other changes" section in the rate-history accordion on the console Rates tab (/console/users?tab=rates).

message_log (migration 0043) records every scheduled DM VERA sends — one row per sendDM attempt in reminders.js (morning summary, evening reminder, renewal alert), recorded whether the send succeeded or failed. user_id is a soft reference resolved via a subquery against slack_user_id at write time (null when the Slack ID doesn't match any current user row); slack_user_id, kind, and body are always recorded, and error carries the Slack error string on a status = 'failed' row. The write happens at the single choke point (sendDM), not at its three callers, and is wrapped so a logging failure can never break delivery (same pattern as checkExceptionQueue's post-write hook on log_time). Pruned to the last 90 days once per morning run, not on every write. Owner/admin-only (isOwnerLevel), console-only (absent from MCP_TOOLS, unreachable from Slack) — these messages carry a person's own envelope figures and logged hours, so a manager must not see anyone else's. Never read by any operational query in lib/dashboard.js, lib/forecast.js, or lib/availability.js. Displayed at /console/messages.

time_approvals (migration 0046) and the companion time_entries.billable_hours column implement a scoped amendment to business rule 28 for T&M projects only (issue #2179; see CLAUDE.md for the full amendment). On a T&M project a recorded hour is the invoice line, so the project's PM (or an owner/admin) gets a periodic, hours-only approval step — time_entries.billable_hours is nullable, no backfill, no CHECK, and null means "bill every recorded hour" (today's behavior, every pre-existing entry unaffected); 0 is a distinct, legitimate value ("bill none of this"). time_approvals is one row per (project_id, period), unique together, written by a look-up-then-update-or-insert upsert so re-approving a period updates the same row rather than creating a second one. time_entries.hours itself is never modified by anything in this arc — recorded time stays immutable. Both are console-only (absent from MCP_TOOLS), audit-silent (rule 27 — an approval is neither a create nor a retroactive change to recorded time), and storage/validation/endpoints only as of this migration — no UI yet, and nothing in lib/dashboard.js, lib/forecast.js, lib/availability.js, or reminders.js reads billable_hours or time_approvals.

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:

sql

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:

bash

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:

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

Database Schema Overview ​

Tables ​

Tenant isolation ​

ID strategy ​

Foreign keys ​