Column-by-Column Schema Reference

Every column for every table, derived from the migration files (migrations/*.js) and cross-checked against the DAL reader functions in lib/dal.postgres.js. The migration files are the schema-of-record; this doc is the working reference — keep both in sync per the Schema-change protocol in CLAUDE.md.

When writing SQL in specs or tests, check here first. The three most common hallucination traps:

• users has status ('active' / 'inactive'), not is_active
• company_calendar has no status column — only id, date, name, type
• time_entries date column is date, not entry_date

---

All tables include tenant_id uuid NOT NULL as part of their composite primary key (tenant_id, id). It is omitted from the per-column tables below to reduce noise; assume it is always present and always the first primary key component.

---

time_entries

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component (with tenant_id)
date	date	—	NO	Entry date. Column is named date, not entry_date.
user_id	integer	—	NO	→ users.id
project_id	integer	—	NO	→ projects.id
task_id	integer	—	YES	→ tasks.id. Nullable — some overhead entries have no task.
hours	numeric(5,2)	—	NO
notes	text	—	YES
budget_id	integer	—	YES	→ budgets.id. Nullable, no backfill — names the specific envelope this entry belongs to (issue #2044). Null for every entry that predates this column and for every internal-project entry; no CHECK.
billable_hours	numeric(5,2)	—	YES	Nullable, no backfill, no CHECK — a PM's approved billable figure for this entry, part of the T&M-only time-approval arc (issue #2179). Null means "bill every recorded hour" (today's behavior, every pre-existing entry). 0 is a distinct, legitimate value ("bill none of this") — never conflated with null. This column is never modified by anything that changes hours itself; recorded time stays immutable.

The person column from the legacy Sheets schema was not migrated to Postgres. The DAL reader synthesises person: '' on every row for Sheets-compatibility — it is not a real column.

---

projects

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component
name	text	—	NO
client_id	integer	—	YES	→ clients.id
type	text	—	NO	'Retainer' | 'retainer_period' | 'agile' | 'sprint' | 'T&M' | 'Fixed Price' | 'Pro Bono' | 'Internal'
is_billable	boolean	false	NO
contract_value	numeric(12,2)	—	YES
hours_budget	numeric(8,2)	—	YES	Empty on Retainer/Agile parents — lives on children.
billing_rate	numeric(10,2)	—	YES
alert_threshold	integer	80	NO	Percentage.
status	text	'active'	NO	'active' | 'completed' | 'cancelled'
pm_user_id	integer	—	YES	→ users.id
project_category	text	'client'	NO	'client' | 'internal' | 'pto' | 'sick' | 'leave'
notes	text	—	YES
deadline	date	—	YES
parent_project_id	integer	—	YES	→ projects.id. Set on period/sprint children; null on parents.
period_number	integer	—	YES	Null on parents.
period_status	text	—	YES	'planned' | 'active'. Null on parents.
period_start	date	—	YES	Null on parents.
period_end	date	—	YES	Null on parents.
renewal_cadence	text	—	YES	'monthly' | 'quarterly' | 'biannual' | 'annual'. Null on non-retainer parents.
start_date	date	CURRENT_DATE	NO	Added in migration 0020. Backfilled from first time entry date on existing rows.
billing_schedule	jsonb	—	YES	Optional plan for how a Fixed Price project's contract value is billed across the months of its life — sparse array of { month, amount } (month is YYYY-MM, amount a positive dollar figure). Null means unscheduled. Added in migration 0045. Describes billing only, never revenue recognition.

---

users

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component
slack_user_id	text	—	YES	Nullable since migration 0018 — contractors/vendors may have no Slack presence.
name	text	—	NO
role	text	'user'	NO	'owner' | 'manager' | 'user' | 'admin' (migration 0023). admin has owner-level access but is excluded from team-delivery statistics (see lib/roles.js); it is never normalized to 'owner'. Legacy Sheets value 'pm' is normalized to 'manager' by the DAL reader.
status	text	'active'	NO	'active' | 'inactive'. Not a boolean — never use is_active.
discipline_id	integer	—	YES	→ disciplines.id
employment_type	text	'fte'	NO	'fte' | 'contractor' | 'vendor'
hours_per_week	numeric(5,2)	40	NO	FTE defaults 40. Contractors require explicit value.
email	text	—	YES	Email address for OAuth console login (migration 0024). Lowercase-stored; used for cross-tenant email lookup.
level	integer	—	YES	Compensation level 1–4, CHECK (level IS NULL OR level BETWEEN 1 AND 4). Added in migration 0034. Unlevelled people simply are not benchmarked.

---

clients

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component
name	text	—	NO
status	text	'active'	NO	'active' | 'inactive'
notes	text	—	YES

---

tasks

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component
name	text	—	NO
is_billable	boolean	false	NO
status	text	'active'	NO	'active' | 'archived'. Added in migration 0019.
discipline_id	integer	—	YES	Which discipline this task's work belongs to (many tasks → one discipline; id → disciplines.id, soft reference). Added in migration 0033; backfilled from the discipline whose default_task_id pointed at this task, so day-one values are byte-identical to the old inversion.
notes	text	—	YES	Free-text elaboration on the task, separate from its short name — surfaced on hover (_taskNameWithNotes) rather than as its own column. Added in migration 0044 (issue #2150), no backfill. Console-only, owner-only, audit-silent — set via set_task_notes, deliberately absent from MCP_TOOLS. Max 200 characters, validated at the endpoint.

---

project_tasks

Column	Type	Default	Nullable	Notes
project_id	integer	—	NO	PK component. → projects.id
task_id	integer	—	NO	PK component. → tasks.id
budget_hours	numeric(8,2)	—	YES
alert_threshold	integer	80	NO	Percentage.

This table has no id column. PK is (tenant_id, project_id, task_id). Presence of a row means the task is enabled for that project; there is no enabled flag.

---

budgets

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component
user_id	integer	—	NO	→ users.id
project_id	integer	—	NO	→ projects.id
allocated_hours	numeric(8,2)	—	NO
status	text	'active'	NO	'active' | 'cancelled' | 'transferred'
created_by	integer	—	YES	→ users.id. Nullable — older envelopes predate this column.
created_at	timestamptz	now()	NO
task_id	integer	—	YES	→ tasks.id. Optional work-category tag on the envelope.
start_date	date	—	YES	Optional envelope window start. Null means project-lifetime (no window). Added in migration 0036.
end_date	date	—	YES	Optional envelope window end. Null means project-lifetime (no window). Added in migration 0036.
week_hours	jsonb	—	YES	Optional shape of the envelope's hours across the weeks of its window: a sparse array of { week, hours }, week always a Monday (YYYY-MM-DD). Null means unshaped. Requires a window — cannot be set without start_date/end_date. Added in migration 0037.
label	text	—	YES	Optional free-text wayfinder distinguishing several envelopes on the same (user, project, task) — e.g. two "Photography" envelopes for one person, one per shoot location. Max 40 characters, validated in the endpoint (not the database). Console-only — absent from MCP_TOOLS, both for setting and display. Added in migration 0041.

transferred_from_allocation_id was dropped in migration 0021 — it was never populated by any tool. Do not reference it in SQL. start_date/end_date are a bound, not a schedule — storage only as of migration 0036; no proration, capacity, or availability calculation reads them yet. week_hours is a forecast the PM owns, never an instruction handed to the person whose hours they are — storage only as of migration 0037; no calculation, alert, chip, or signal anywhere derives from a deviation between this shape and logged time. See CLAUDE.md "week_hours". label is a name, not an attribution key — it does not make logged time attributable per envelope (time entries still carry only (user_id, project_id, task_id)); several same-(user, project, task) envelopes still merge into one usage bucket, and the label is carried per constituent envelope on the merged bucket, not promoted to a bucket-level field. Never rendered in the Slack morning briefing.

---

disciplines

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component
name	text	—	NO
status	text	'active'	NO	'active' | 'inactive'
default_task_id	integer	—	YES	Optional default task for this discipline (id → 05. Tasks); display/convenience only
soc_code	text	—	YES	O*NET-SOC occupation code (e.g. 15-1252.00), explicitly chosen by an owner from the taxonomy committed at data/onet-soc-occupations.json; soft reference, never inferred. Added in migration 0035.
color	text	—	YES	Owner-chosen identity color token name (e.g. accent-azure), never a raw CSS value — validated against the DISCIPLINE_PALETTE allowlist in lib/discipline-palette.js on write and again at render time. Drives the small dot rendered beside the discipline's name across the console. Added in migration 0040.

---

discipline_levels

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component
discipline_id	integer	—	NO	→ disciplines.id, soft reference (no FK) — archiving a discipline must never be blocked by a band pointing at it.
level	integer	—	NO	CHECK (level BETWEEN 1 AND 4).
salary_min	numeric(12,2)	—	YES	Empty means "not set".
salary_max	numeric(12,2)	—	YES	Empty means "not set".

One row = one (discipline, level) salary band, UNIQUE (tenant_id, discipline_id, level). Added in migration 0034 (issue #1675). Owner-only, console-only — the endpoints that read/write this table are deliberately absent from MCP_TOOLS (Slack-unreachable) and never post to the audit channel. Compensation is always derived from rate_history.hourly_cost; this table only records the bands it's compared against.

---

conversations

Column	Type	Default	Nullable	Notes
row_id	bigserial	—	NO	Surrogate PK (single column). Not the conversation ID.
id	text	—	NO	Conversation ID — not unique; multiple rows share the same id (one per exchange).
timestamp	timestamptz	—	NO
user_id	text	—	NO	Slack user ID (text). No FK to users.
user_name	text	—	YES
message	text	—	NO
response	text	—	YES
summary	text	—	YES
entities	jsonb	'[]'	NO	Typed entity references from this turn's tool calls. Added in migration 0022. Shape: array of entity objects consumed by the Phase 1 entity-resolver.

conversations uses row_id bigserial as its primary key, not (tenant_id, id) like other tables. tenant_id is still present and indexed for isolation.

---

pending_pto

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component
user_id	integer	—	NO	→ users.id
date	date	—	NO
hours	numeric(5,2)	—	NO
status	text	'pending'	NO	'pending' | 'approved' | 'rejected'
created_at	timestamptz	now()	NO
approved_by	text	—	YES	Slack user ID of the approving manager (text, not internal user id).
resolved_at	timestamptz	—	YES	Set when status moves to approved or rejected.
notes	text	—	YES

---

unresolved_items

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component
type	text	—	NO	Freeform category string (e.g. 'time_entry').
description	text	—	NO
affected_project_id	integer	—	YES	→ projects.id. May be null if item is user-scoped only.
affected_user_id	integer	—	YES	→ users.id. May be null if item is project-scoped only.
created_at	timestamptz	now()	NO
status	text	'open'	NO	'open' | 'resolved' | 'dismissed'
resolved_at	timestamptz	—	YES
resolved_by	text	—	YES	Slack user ID of whoever resolved/dismissed the item (text).

---

company_calendar

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component
date	date	—	NO
name	text	—	NO	Holiday or event name.
type	text	'holiday'	NO	Currently always 'holiday'. CHECK kept loose to allow future values without a migration.

There is no status column on company_calendar. The full column list is: tenant_id, id, date, name, type.

---

rate_history

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component
user_id	integer	—	NO	→ users.id
hourly_cost	numeric(10,2)	—	NO
effective_from	date	—	NO	Effective-date versioning. The rate in effect for a given entry date = MAX(effective_from) WHERE effective_from <= entry_date.

Append-only table — no UPDATE or DELETE in the DAL. New rates are always new rows.

---

liabilities

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component
project_id	integer	—	NO	→ projects.id
vendor_user_id	integer	—	NO	→ users.id. Must reference a user with employment_type = 'vendor' (enforced at application layer, not schema).
description	text	—	NO
agreed_amount	numeric(12,2)	—	NO	Fixed payment commitment. Accrues pro-rata to deadline.
status	text	'active'	NO	'active' | 'cancelled'
created_by	text	—	NO	Slack user ID of the creating owner (text, not internal user id).
created_at	timestamptz	now()	NO
agreed_hours	numeric(10,2)	0	NO	Contracted effort in hours. Added in migration 0017. Accrues linearly from created_at to project deadline as implied hours in burn-down calculations. Rows predating the column have agreed_hours = 0 (inert).

---

proposal_roles

Sales-side staffing lines on a proposal (console-only, owner/admin, unreachable from Slack — see CLAUDE.md "Proposals Phase 1"). No operational query reads this table.

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component (app-assigned, MAX(id)+1)
proposal_id	integer	—	NO	→ proposals.id, ON DELETE CASCADE
discipline_id	integer	—	YES	Soft reference (no FK) → a discipline. One of discipline_id/user_id costs the line; a specific person overrides the discipline for costing.
user_id	integer	—	YES	Soft reference (no FK) → users.id.
planned_hours	numeric(10,2)	0	NO
billing_rate	numeric(10,2)	—	YES	T&M proposals only.
task	text	—	YES	Free-text label, not a tasks.id reference.
created_at	timestamptz	now()	NO
start_date	date	—	YES	Optional window start, mirroring budgets.start_date. Null means unwindowed (today's behavior). Added in migration 0038.
end_date	date	—	YES	Optional window end, mirroring budgets.end_date. Null means unwindowed. Added in migration 0038.
week_hours	jsonb	—	YES	Optional shape of this line's planned hours across the weeks of its window: a sparse array of { week, hours }, week always a Monday (YYYY-MM-DD). Null means unshaped. Requires a window — cannot be set without start_date/end_date. Capped at planned_hours. Added in migration 0038.

start_date/end_date/week_hours share validation (lib/week-shape.js) with an allocation envelope's identical fields on budgets — storage only as of migration 0038; lib/proposal-capacity.js does not read them yet. The shape is a forecast, never an instruction — see CLAUDE.md "week_hours".

---

user_attribute_history

Audit trail for users.level and users.discipline_id changes (console-only, owner/admin, unreachable from Slack — absent from MCP_TOOLS). Audit-silent — never posts to the audit channel (same reasoning as set_rate's rule-21 suppression). Forward-only: nothing was backfilled, history starts at migration 0039. No operational query reads this table.

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component (app-assigned, MAX(id)+1)
user_id	integer	—	NO	Soft reference (no FK) → users.id, same precedent as discipline_levels.discipline_id.
field	text	—	NO	'level' | 'discipline_id'. No CHECK constraint — validated at the endpoint.
old_value	text	—	YES	The value before the change, stringified. discipline_id is stored as the id, never the name — names are resolved at render time so a later discipline rename doesn't rewrite history. Null means "was unset".
new_value	text	—	YES	The value after the change, stringified. Null means "cleared".
changed_by	integer	—	YES	Soft reference (no FK) → users.id of the acting owner/admin. Null when unresolvable.
changed_at	timestamptz	now()	NO

One row per field actually changed — a save that resubmits the same level/discipline writes zero rows. Written from update_user_role (mcp-server.js) as a best-effort side effect: a write failure is logged and swallowed, never failing the user update itself (same pattern as checkExceptionQueue's post-write hook on log_time). Displayed as the "Other changes" section of the rate-history accordion on the console Rates tab.

---

message_log

Scheduled-DM delivery log — one row per sendDM attempt (morning summary, evening reminder, renewal alert), success or failure (console-only, owner/admin, unreachable from Slack — absent from MCP_TOOLS). No operational query reads this table.

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component (app-assigned, MAX(id)+1)
user_id	integer	—	YES	Soft reference (no FK) → users.id, 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	text	—	NO	The Slack ID the DM was addressed to.
kind	text	—	NO	'morning' | 'evening' | 'renewals'. No CHECK constraint — validated at the call site.
body	text	—	NO	The exact message text sent (or attempted).
status	text	—	NO	'sent' | 'failed'. No CHECK constraint — validated at the call site.
error	text	—	YES	The Slack error string when status = 'failed'. Null on a successful send.
sent_at	timestamptz	now()	NO

Recorded from the single choke point, sendDM (reminders.js) — never at its three callers. The write is best-effort and wrapped so it can never affect delivery: a logging failure is logged and swallowed, never breaking the DM itself (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) via pruneMessageLog. Owner/admin-only (isOwnerLevel) — a manager sees percentages and their own projects, and these messages carry a person's own envelope figures and logged hours. Displayed at /console/messages.

---

time_approvals

T&M time approval — one row per (project_id, period), a scoped amendment to business rule 28 (issue #2179; see CLAUDE.md). Console-only, owner/admin or the project's PM-of-record — absent from MCP_TOOLS, audit-silent (rule 27 — an approval is neither a create nor a retroactive change to recorded time). No operational query reads this table.

Column	Type	Default	Nullable	Notes
id	integer	—	NO	PK component (app-assigned, MAX(id)+1)
project_id	integer	—	NO	→ projects.id. Must be a T&M project — enforced at the endpoint, not a CHECK.
period	text	—	NO	YYYY-MM. Unique together with project_id (and tenant_id) — re-approving a period updates this row in place rather than creating a second one.
approved_by	integer	—	YES	→ users.id — the owner/admin or PM who approved.
approved_at	timestamptz	—	YES	Set on every approve/re-approve.
total_hours	numeric(7,2)	—	YES	Recorded hours in the period at the moment of approval (a snapshot, not a live query).
billable_hours	numeric(7,2)	—	YES	Approved billable total for the period — the sum of each entry's time_entries.billable_hours where set, else its full recorded hours.

UNIQUE (tenant_id, project_id, period). Written by upsertTimeApproval — looks up the existing row for (project_id, period) and updates it in place, or computes MAX(id)+1 and inserts. time_entries.hours is never modified by this arc; time_entries.billable_hours (see above) is the separate, per-entry judgment this table's totals are computed from.