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

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.

---

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 normalised to 'owner'. Legacy Sheets value 'pm' is normalised 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.

---

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.

---

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.

transferred_from_allocation_id was dropped in migration 0021 — it was never populated by any tool. Do not reference it in SQL.

---

disciplines

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

---

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).