# Current database schema — final state

Complete, exact reference of the **current** database (Supabase Cloud
Postgres) as it exists after applying all 41 migrations in
`supabase/migrations/` in filename order (`20260520120001_init.sql` …
`20260609000010_projects_rls_org_member.sql`). This is the state the
migration scripts (05-migration-plan.md) read FROM. Verified against
the migration SQL directly, 2026-07-06.

Scope: tables, columns, types, defaults, constraints, FKs (with
on-delete behavior), indexes, triggers, enums, extensions — plus the
schema history a data migration must know (renames, drops, backfills,
semantic re-purposings). **RPCs, RLS policies, and non-trigger
functions are in the sibling doc** (`rpcs-and-authz.md`); they appear
here only where they explain a schema artifact.

Conventions in this doc:

- All tables are in schema `public`. All have RLS **enabled**.
- `auth.users` is Supabase's managed auth table (not ours); several
  FKs target it. In the rebuild these FK targets disappear with
  Supabase (00-vision-and-scope.md phase-outs).
- "→ D:x" after an FK = `on delete x` (CASCADE / SET NULL).
- Types are written exactly as declared (`numeric(10,4)`,
  `timestamptz`, naive `timestamp`, etc.).

---

## Extensions

| extension | why |
|---|---|
| `pgcrypto` | `gen_random_uuid()` for every uuid PK default |

## Enums

| enum | values | used by |
|---|---|---|
| `public.org_role` | `'owner'`, `'manager'`, `'member'` | `organization_members.role` only |

That is the ONLY enum/domain. Every other enum-like column is `text`:
`invoices.status` and `pending_imports.source` have CHECK constraints;
`clockify_imports.source` and `cc_expense_lines.category` are
free-text by convention (see per-table notes).

## Trigger function

```sql
create or replace function public.set_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;
```

Attached `BEFORE UPDATE … FOR EACH ROW` on every table that has an
`updated_at` column (full trigger inventory at the end). There are
**no other triggers** in the database — no audit triggers, no
FK-maintenance triggers, no profile-auto-creation trigger (profile
rows are created app-side by `scripts/seed.ts`).

---

# Tables (18 total; 17 live + `auth.users` referenced)

## 1. `public.organizations`

Tenant orgs. In practice exactly one row: slug `bowden-works`.
Table comment: `Tenant orgs. App data is scoped to "bowden-works".`

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `name` | text | NOT NULL | — | **UNIQUE** (`organizations_name_key`) |
| `slug` | text | NOT NULL | — | **UNIQUE** (`organizations_slug_key`) |
| `default_hourly_rate_usd` | numeric(8,2) | NULL | — | Added 20260521170001. **DEAD** — superseded by `teams.base_rate_usd` (20260523030001), which was itself deprecated by the proportion elimination (20260524000013). Seeded from Adi's `team_members.hourly_rate_usd`, fallback `14.00` |
| `created_at` | timestamptz | NOT NULL | `now()` | |

No `updated_at`, no trigger, no extra indexes.

## 2. `public.profiles`

Extends `auth.users` 1:1. Created app-side (seed script / user
creation action) — **no DB trigger creates these**.

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | — | **PK**; FK → `auth.users(id)` D:CASCADE |
| `email` | text | NOT NULL | — | **UNIQUE** (`profiles_email_key`). Duplicates `auth.users.email`; can drift |
| `full_name` | text | NULL | — | |
| `is_super_admin` | boolean | NOT NULL | `false` | rian only in practice |
| `must_change_password` | boolean | NOT NULL | `true` | First-login forced password change |
| `created_at` | timestamptz | NOT NULL | `now()` | |
| `updated_at` | timestamptz | NOT NULL | `now()` | trigger `profiles_set_updated_at` |

## 3. `public.organization_members`

| column | type | null | default | notes |
|---|---|---|---|---|
| `organization_id` | uuid | NOT NULL | — | FK → `organizations(id)` D:CASCADE; PK part |
| `user_id` | uuid | NOT NULL | — | FK → `auth.users(id)` D:CASCADE; PK part |
| `role` | public.org_role | NOT NULL | — | owner / manager / member |
| `created_at` | timestamptz | NOT NULL | `now()` | |

- **PK** `(organization_id, user_id)`.
- Index `idx_org_members_user` on `(user_id)`.

## 4. `public.teams` (created 20260523030001)

One billing team per (org, owner). Historically the rate-bearing unit
(ADR #032); the rate role is deprecated since proportion elimination.
Still live as the unit of team-membership grouping and importer
visibility.

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `org_id` | uuid | NOT NULL | — | FK → `organizations(id)` D:CASCADE |
| `owner_user_id` | uuid | NOT NULL | — | FK → `auth.users(id)` D:CASCADE |
| `name` | text | NULL | — | "Adi's team", "Rian's team" |
| `base_rate_usd` | numeric(10,2) | NOT NULL | `50` | **DEPRECATED for money math** since 20260524000013. Column comment: changing it does NOT retroactively change historical entries |
| `created_at` | timestamptz | NOT NULL | `now()` | |
| `updated_at` | timestamptz | NOT NULL | `now()` | trigger `teams_set_updated_at` |

- **UNIQUE** `teams_org_id_owner_user_id_key` on `(org_id, owner_user_id)`.
- Seeded rows: "Adi's team" (owner `info@adipramono.com`, rate =
  legacy org rate, fallback 35) and "Rian's team" (owner
  `rian@rian.ca`, rate 50), both in Bowden Works.

## 5. `public.team_members` (created 20260520120002; team_id added 20260523030001)

Workers — people who appear in imported time data. **No auth
accounts.** Reference table for rate/name resolution at import time.

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `org_id` | uuid | NOT NULL | — | FK → `organizations(id)` D:CASCADE |
| `team_id` | uuid | NOT NULL | — | FK → `teams(id)` D:CASCADE. Added nullable, backfilled, SET NOT NULL in 20260523030001 |
| `email` | text | NOT NULL | — | Unique per team (below) |
| `display_name` | text | NOT NULL | — | |
| `rate_proportion` | numeric(6,5) | NOT NULL | — | CHECK `rate_proportion >= 0 and rate_proportion <= 1` (auto-named `team_members_rate_proportion_check`). **DEPRECATED for money math** since 20260524000013; still stamps `converted_duration_seconds` at write (Toggl-export only) |
| `hourly_rate_usd` | numeric(8,2) | NULL | — | **DEAD** — v1 base-rate proxy (Adi's rate); value moved to `organizations.default_hourly_rate_usd` 20260521170001; never dropped |
| `cost_rate_usd` | numeric(10,2) | NULL | — | Added 20260524000013. **Canonical cost rate: USD per SOURCE hour** the team owner pays this person. Backfilled `= round(teams.base_rate_usd × rate_proportion, 2)`; `gary@rian.ca` (an AI) forced to `0` |
| `billout_rate_usd` | numeric(10,2) | NULL | — | Added 20260524000004 as USD per CONVERTED hour; **RESCALED IN PLACE ×rate_proportion in 20260524000013** — now means **USD per SOURCE hour**. NULL = no billout amount computed for this member's entries |
| `consolidate_as` | text | NULL | — | If set (e.g. `'Tingang'`), replaces display_name in `converted_user` |
| `is_active` | boolean | NOT NULL | `true` | |
| `notes` | text | NULL | — | |
| `created_at` | timestamptz | NOT NULL | `now()` | |
| `updated_at` | timestamptz | NOT NULL | `now()` | trigger `team_members_set_updated_at` |

- **UNIQUE** `team_members_team_id_email_key` on `(team_id, email)` —
  replaced the original `team_members_org_id_email_key` on
  `(org_id, email)` (dropped 20260523030001). The same email may exist
  in multiple teams (e.g. `rian@rian.ca`).
- Index `idx_team_members_org_email` on `(org_id, lower(email))`
  (non-unique).
- Migration-seeded rows: `rian@rian.ca` "Rian Bowden" proportion 1.0
  (added 20260523000001, moved to Rian's team 20260523030001);
  `matmoncoo@gmail.com` "Matthew Monti-Cooper" proportion 0.7 (Rian's
  team). `gary@rian.ca` was created via the app but is referenced by
  migration 20260524000013 (cost forced to 0).

## 6. `public.clockify_imports` (created 20260520120002)

One row per committed import batch. **Name is historical/misleading**
— it also holds Toggl batches and the spreadsheet backfill.

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `org_id` | uuid | NOT NULL | — | FK → `organizations(id)` D:CASCADE |
| `imported_by` | uuid | NULL | — | FK → `auth.users(id)` D:SET NULL. Drives importer-scoped visibility |
| `imported_at` | timestamptz | NOT NULL | `now()` | |
| `filename` | text | NULL | — | |
| `name` | text | NULL | — | Added 20260520120003. Human batch label |
| `row_count` | integer | NOT NULL | `0` | |
| `source` | text | NULL | — | Added 20260523000001. Values by convention: `'backfill'` / `'clockify'` / `'toggl'`. **No CHECK constraint** (free text; documented only in a column comment) |
| `notes` | text | NULL | — | |

- Index `idx_clockify_imports_org_date` on `(org_id, imported_at desc)`.
- No updated_at / trigger.
- Backfill facts (20260523000001): the pre-existing "Initial 2026
  migration" batch (filename
  `Time Sheet _ Time Log _ Hours _ Tingang.id _ Adi.xlsx`) was
  attributed to Adi (`imported_by` ← Adi's profile id) and stamped
  `source='backfill'`; every other then-existing batch stamped
  `source='clockify'`.

## 7. `public.time_entries` — the core table (created 20260520120002)

One row per source time entry. Raw source fields + resolved/stamped
fields + lock state. **No updated_at, no trigger.**

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `org_id` | uuid | NOT NULL | — | FK → `organizations(id)` D:CASCADE |
| `import_id` | uuid | NULL | — | FK → `clockify_imports(id)` D:SET NULL |
| `source_user_name` | text | NULL | — | Raw from source |
| `source_user_email` | text | NULL | — | Raw; key for team_member resolution |
| `operator` | text | NULL | — | **RENAMED FROM `client`** 20260521230001. The operating agency ("Bowden Works" / "PlusROI"). Since 20260524000001 a denormalized cache of the `operators` entity |
| `client` | text | NULL | — | **RE-ADDED** 20260521230001 (new meaning): the actual end client, parsed from `"{client} : {project}"` on the FIRST `' : '` (space-colon-space). Denormalized cache of `clients` |
| `project` | text | NULL | — | Denormalized cache of `projects.name` |
| `description` | text | NULL | — | |
| `billable` | boolean | NULL | — | |
| `start_at` | timestamp (naive) | NOT NULL | — | **Deliberately timezone-naive**: Clockify exports local wall-clock without TZ; preserved exactly. All RPC date params are naive `timestamp` too |
| `end_at` | timestamp (naive) | NULL | — | |
| `duration_seconds` | integer | NULL | — | SOURCE duration. Back-computed for spreadsheet-backfilled rows in 20260521170001: `round(converted_duration_seconds / rate_proportion)::int` (skipping proportion=0) |
| `source_rate_usd` | numeric(10,4) | NULL | — | Raw from source |
| `source_amount_usd` | numeric(10,4) | NULL | — | Raw from source |
| `team_member_id` | uuid | NULL | — | FK → `team_members(id)` D:SET NULL. Resolved at insert from `source_user_email` |
| `converted_user` | text | NULL | — | Stamped at write: `consolidate_as ?? display_name`. Toggl-export legacy |
| `converted_duration_seconds` | integer | NULL | — | Stamped at write: `duration_seconds × rate_proportion`. Since 20260524000013 consumed ONLY by the Toggl CSV export |
| `billout_cost_usd` | numeric(10,4) | NULL | — | Added 20260521190001. **Locked-at-write stamp** (ADR #014). Current semantics (since 20260524000013): `(duration_seconds/3600) × team_members.cost_rate_usd`, rounded 2dp (stored in a 4dp column) |
| `billout_amount_usd` | numeric(12,2) | NULL | — | Added 20260524000004. **Locked-at-write stamp.** Current semantics: `(duration_seconds/3600) × effective_billout_rate(team_member_id, project_id)`, rounded 2dp. NULL when duration is NULL |
| `project_id` | uuid | NULL | — | Added 20260524000001. FK → `projects(id)` D:SET NULL. **Source of truth**; the operator/client/project text triple is a cache kept in sync by app rename actions. NULL ⇒ entry "Blocked" |
| `transferred_at` | timestamptz | NULL | — | **Legacy audit only** since 20260525000010. Was the "sent to Toggl" lock |
| `transferred_by` | uuid | NULL | — | FK → `auth.users(id)` D:SET NULL. Legacy audit |
| `transfer_batch_id` | uuid | NULL | — | **No FK** — free uuid. Legacy audit |
| `invoice_id` | uuid | NULL | — | Added 20260525000010. FK → `invoices(id)` D:SET NULL. **NOT NULL = locked** (only owners can detach + edit). THE canonical lock (ADR #038) |
| `invoice_applied_at` | timestamptz | NULL | — | Audit: when attached (backfilled from `transferred_at`) |
| `invoice_applied_by` | uuid | NULL | — | FK → `auth.users(id)` D:SET NULL (backfilled from `transferred_by`) |
| `created_at` | timestamptz | NOT NULL | `now()` | |

Indexes on `time_entries`:

| index | definition |
|---|---|
| `idx_time_entries_org_start` | `(org_id, start_at desc)` |
| `idx_time_entries_org_transferred` | `(org_id, transferred_at) WHERE transferred_at IS NULL` — partial, legacy |
| `idx_time_entries_org_project_month` | `(org_id, project, start_at)` |
| `idx_time_entries_source_email` | `(org_id, source_user_email)` |
| `idx_time_entries_org_project_start_proj` | `(org_id, project, start_at) WHERE converted_duration_seconds IS NOT NULL` — partial |
| `idx_time_entries_org_project_start_billout` | `(org_id, project, start_at) WHERE billout_cost_usd IS NOT NULL` — partial (added 20260521190001) |
| `time_entries_project_id_idx` | `(project_id)` |
| `time_entries_invoice_id_idx` | `(invoice_id) WHERE invoice_id IS NOT NULL` — partial |

Note the three overlapping `(org_id, project, start_at)` btrees — do
not replicate; see "Rebuild deltas."

## 8. `public.comments` (created 20260522020001)

Org-wide comment/note board. **Replaced the per-entry
`time_entry_comments` table, which was dropped with its data** (see
History).

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `org_id` | uuid | NOT NULL | — | FK → `organizations(id)` D:CASCADE |
| `author_id` | uuid | NULL | — | FK → `auth.users(id)` D:SET NULL |
| `body` | text | NOT NULL | — | CHECK `length(trim(body)) > 0` |
| `resolved_at` | timestamptz | NULL | — | |
| `resolved_by` | uuid | NULL | — | FK → `auth.users(id)` D:SET NULL |
| `created_at` | timestamptz | NOT NULL | `now()` | |

- Indexes: `idx_comments_org_created` `(org_id, created_at desc)`;
  `idx_comments_org_unresolved` `(org_id, created_at desc) WHERE
  resolved_at IS NULL` (partial).
- No updated_at / trigger.

## 9. `public.operators` (created 20260524000001)

First-class entity for the operating agency (formerly free-text
`time_entries.operator`). Backfilled from distinct
`time_entries.operator` values.

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `org_id` | uuid | NOT NULL | — | FK → `organizations(id)` D:CASCADE |
| `name` | text | NOT NULL | — | CHECK `length(trim(name)) > 0` |
| `notes` | text | NULL | — | |
| `created_at` | timestamptz | NOT NULL | `now()` | |
| `updated_at` | timestamptz | NOT NULL | `now()` | trigger `operators_set_updated_at` |

- **UNIQUE INDEX** `operators_org_lower_name_unique` on
  `(org_id, lower(name))` — case-insensitive per org.

## 10. `public.clients` (created 20260524000001)

End clients, **operator-scoped** (ADR #035): the same client name
under two operators is two rows. Backfilled from distinct
`(operator, client)` pairs in time_entries.

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `org_id` | uuid | NOT NULL | — | FK → `organizations(id)` D:CASCADE |
| `operator_id` | uuid | NOT NULL | — | FK → `operators(id)` D:CASCADE |
| `name` | text | NOT NULL | — | CHECK `length(trim(name)) > 0` |
| `notes` | text | NULL | — | |
| `created_at` | timestamptz | NOT NULL | `now()` | |
| `updated_at` | timestamptz | NOT NULL | `now()` | trigger `clients_set_updated_at` |

- **UNIQUE INDEX** `clients_operator_lower_name_unique` on
  `(operator_id, lower(name))`.

## 11. `public.projects` (created 20260523050001; refactored 20260524000001)

Project entity + owner-facing money metadata. Entity chain:
operator → client → project.

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `org_id` | uuid | NOT NULL | — | FK → `organizations(id)` D:CASCADE |
| `client_id` | uuid | NOT NULL | — | Added 20260524000001 (nullable → backfill → SET NOT NULL). FK → `clients(id)` D:CASCADE |
| `name` | text | NOT NULL | — | Added 20260524000001, populated from the old `project` text column |
| `income_usd` | numeric(12,2) | NULL | — | Manually entered income. **Owner-only at the APP layer only** (see RLS note) |
| `notes` | text | NULL | — | |
| `billout_adjustment_pct` | numeric(7,4) | NULL | — | Added 20260524000008. % markup (`10` = +10%); applied at READ time in summaries as `raw × (1 + pct/100)` — never stamped (ADR #040) |
| `billout_adjustment_amount` | numeric(12,2) | NULL | — | Added 20260524000008. Fixed $ added once per window/row at read time |
| `created_at` | timestamptz | NOT NULL | `now()` | |
| `updated_at` | timestamptz | NOT NULL | `now()` | trigger `projects_set_updated_at` |

- **UNIQUE INDEX** `projects_client_lower_name_unique` on
  `(client_id, lower(name))`.
- **Dropped columns** (lived 20260523050001 → 20260524000001):
  `operator text`, `client text`, `project text`, and the NULL-safe
  unique index `projects_org_op_client_project_unique` on
  `(org_id, coalesce(operator,''), coalesce(client,''), coalesce(project,''))`.
- **Schema-relevant RLS history:** originally SELECT and ALL were
  owner/super-admin-only (deliberate: income must not leak to managers
  even via UI bugs). Relaxed 20260609000010 to a single
  `projects_all_org_member` FOR ALL policy (`is_org_member`) because
  the import resolver, running as manager Adi, must INSERT project
  rows. Consequence: `income_usd` and both adjustment columns are
  DB-readable AND DB-writable by any org member; secrecy is enforced
  only by app-layer `canSeeIncome`/`canTransfer`.

## 12. `public.pending_imports` (created 20260524000003)

Staging for the two-step import flow (upload → resolver → finalize;
ADR #041). Rows deleted on commit/cancel.

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `org_id` | uuid | NOT NULL | — | FK → `organizations(id)` D:CASCADE |
| `uploaded_by` | uuid | NOT NULL | — | FK → `auth.users(id)` D:CASCADE |
| `uploaded_at` | timestamptz | NOT NULL | `now()` | |
| `expires_at` | timestamptz | NOT NULL | `(now() + interval '24 hours')` | **Cleanup was never built** (migration: "cleanup is a follow-up") — expired rows accumulate |
| `filename` | text | NULL | — | |
| `batch_name` | text | NULL | — | |
| `notes` | text | NULL | — | |
| `source` | text | NOT NULL | — | CHECK `source in ('clockify', 'toggl')` — note `'backfill'` is NOT valid here, unlike `clockify_imports.source` |
| `date_format` | text | NOT NULL | — | |
| `time_format` | text | NOT NULL | — | |
| `parsed_entries` | jsonb | NOT NULL | — | Full parsed payload (`lib/clockify.ts` `ParsedEntry[]`) |

- Indexes: `pending_imports_uploaded_by_idx` `(uploaded_by)`;
  `pending_imports_expires_at_idx` `(expires_at)`.

## 13. `public.project_rate_overrides` (created 20260524000009; pct added 20260524000012)

Per-project billout rate rules feeding `effective_billout_rate()`.
`team_member_id NULL` = project-wide default rule; SET =
member-specific rule (more specific wins); both fall back to
`team_members.billout_rate_usd`.

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `org_id` | uuid | NOT NULL | — | FK → `organizations(id)` D:CASCADE |
| `project_id` | uuid | NOT NULL | — | FK → `projects(id)` D:CASCADE |
| `team_member_id` | uuid | NULL | — | FK → `team_members(id)` D:CASCADE |
| `override_rate_usd` | numeric(10,2) | NULL | — | CHECK `override_rate_usd >= 0`. Was NOT NULL; DROP NOT NULL in 20260524000012. Absolute USD/hour — **per SOURCE hour since 20260524000013** (semantics silently changed; see History) |
| `override_pct` | numeric(7,4) | NULL | — | Added 20260524000012. Signed % on the member's fallback rate (`-20` = 20% discount) |
| `notes` | text | NULL | — | |
| `created_at` | timestamptz | NOT NULL | `now()` | |
| `updated_at` | timestamptz | NOT NULL | `now()` | trigger `project_rate_overrides_set_updated_at` |

- **CHECK** `project_rate_overrides_one_kind`: XOR —
  `(override_rate_usd is not null and override_pct is null) or
  (override_rate_usd is null and override_pct is not null)`.
- **UNIQUE INDEX** `project_rate_overrides_unique` on
  `(project_id, coalesce(team_member_id,
  '00000000-0000-0000-0000-000000000000'))` — the zero-uuid sentinel
  makes the NULL (project-wide) rule unique too: max one default rule
  per project, one rule per (project, member).
- Plain indexes: `project_rate_overrides_project_idx` `(project_id)`;
  `project_rate_overrides_team_member_idx` `(team_member_id)`.

## 14. `public.invoices` (created 20260525000010; invoice_date added 20260525000020)

Attaching an entry (`time_entries.invoice_id`) locks it from non-owner
edits — replaced the transferred_at lock (ADR #038). An invoice is a
bag of entries; there is **no invoice_lines table** in the current
system.

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `org_id` | uuid | NOT NULL | — | FK → `organizations(id)` D:CASCADE |
| `name` | text | NOT NULL | — | Case-insensitive unique per org (index below) |
| `status` | text | NOT NULL | `'open'` | CHECK `status in ('open', 'sent', 'paid')` |
| `operator_id` | uuid | NULL | — | FK → `operators(id)` D:SET NULL |
| `client_id` | uuid | NULL | — | FK → `clients(id)` D:SET NULL |
| `manual_total_usd` | numeric(10,2) | NULL | — | Owner override when the printed total differs from `sum(billout_amount_usd)`. NULL = use the sum. Per-row stamps stay untouched |
| `notes` | text | NULL | — | |
| `created_by` | uuid | NULL | — | FK → `auth.users(id)` D:SET NULL |
| `created_at` | timestamptz | NOT NULL | `now()` | Insertion timestamp |
| `invoice_date` | date | NULL | — | Added 20260525000020. User-facing accounting date. The create form defaults it to the last day of the previous month — **app-side, not a DB default** |
| `sent_at` | timestamptz | NULL | — | Independent of `status` — no constraint ties them (status='paid' with NULL paid_at is representable) |
| `paid_at` | timestamptz | NULL | — | |

- **UNIQUE INDEX** `invoices_org_name_lower_uq` on `(org_id, lower(name))`.
- Plain indexes: `invoices_org_status_idx` `(org_id, status)`;
  `invoices_operator_idx` `(operator_id)`; `invoices_client_idx`
  `(client_id)`.
- No updated_at / trigger.
- **Backfill (20260525000010):** one `'Legacy transfers'` invoice per
  org (status `'paid'`, explanatory notes) holding every entry with
  `transferred_at IS NOT NULL`; those entries got `invoice_id` set and
  `invoice_applied_at/by` copied verbatim from `transferred_at/by`.

## 15. `public.cc_expense_batches` (created 20260525000040)

Tools → "Bill Through Credit Card Expenses": one batch per statement
paste.

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `org_id` | uuid | NOT NULL | — | FK → `organizations(id)` D:CASCADE |
| `name` | text | NOT NULL | — | |
| `created_by` | uuid | NULL | — | FK → `auth.users(id)` D:SET NULL |
| `created_at` | timestamptz | NOT NULL | `now()` | |

- Index `cc_expense_batches_org_idx` `(org_id)`.
- RLS is org-member for read AND write; owner-only gating is
  **UI-layer only** (explicit in the migration comment).

## 16. `public.cc_expense_lines` (created 20260525000040; auto_assigned added 20260525000060)

**No org_id column** — tenancy flows through `batch_id` (RLS uses
EXISTS against the batch).

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `batch_id` | uuid | NOT NULL | — | FK → `cc_expense_batches(id)` D:CASCADE |
| `line_index` | integer | NOT NULL | — | Paste order. **No unique on (batch_id, line_index)** — duplicates representable |
| `raw_line` | text | NOT NULL | — | Original pasted line, for audit/re-parse |
| `expense_date` | date | NULL | — | Parsed; NULL = parser failed on this field |
| `description` | text | NULL | — | Parsed |
| `amount_usd` | numeric(12,2) | NULL | — | Parsed |
| `balance_usd` | numeric(12,2) | NULL | — | Parsed |
| `project_id` | uuid | NULL | — | FK → `projects(id)` D:SET NULL. Assignment target |
| `assignment_description` | text | NULL | — | |
| `category` | text | NULL | — | Free text — no enum/CHECK |
| `auto_assigned` | boolean | NOT NULL | `false` | Added 20260525000060. TRUE = filled by a cc_auto_rules match, not yet user-confirmed (renders orange); any user save flips FALSE (green) |
| `created_at` | timestamptz | NOT NULL | `now()` | |

- Indexes: `cc_expense_lines_batch_idx` `(batch_id)`;
  `cc_expense_lines_project_idx` `(project_id) WHERE project_id IS NOT
  NULL` (partial).

## 17. `public.cc_highlight_keywords` (created 20260525000050)

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `org_id` | uuid | NOT NULL | — | FK → `organizations(id)` D:CASCADE |
| `keyword` | text | NOT NULL | — | Substring match against `cc_expense_lines.description`; purely visual (yellow highlight) |
| `created_at` | timestamptz | NOT NULL | `now()` | |

- **UNIQUE INDEX** `cc_highlight_keywords_org_kw_uq` on
  `(org_id, lower(keyword))`.

## 18. `public.cc_auto_rules` (created 20260525000050)

Substring → (project, description, category) auto-assignment rules.
Applied at paste time only (new rules never retroactively touch
existing lines). First match wins ordered by `created_at desc`; no
priority column.

| column | type | null | default | notes |
|---|---|---|---|---|
| `id` | uuid | NOT NULL | `gen_random_uuid()` | **PK** |
| `org_id` | uuid | NOT NULL | — | FK → `organizations(id)` D:CASCADE |
| `keyword` | text | NOT NULL | — | |
| `project_id` | uuid | NOT NULL | — | FK → `projects(id)` D:CASCADE — deleting a project cascades to its rules |
| `assignment_description` | text | NULL | — | Pre-fill; NULL = leave the line column NULL |
| `category` | text | NULL | — | Pre-fill |
| `created_at` | timestamptz | NOT NULL | `now()` | |

- **UNIQUE INDEX** `cc_auto_rules_org_kw_uq` on `(org_id, lower(keyword))`.
- Plain index `cc_auto_rules_project_idx` `(project_id)`.

---

# Complete trigger inventory

| trigger | table | timing | function |
|---|---|---|---|
| `profiles_set_updated_at` | profiles | BEFORE UPDATE, FOR EACH ROW | `public.set_updated_at()` |
| `teams_set_updated_at` | teams | BEFORE UPDATE, FOR EACH ROW | `public.set_updated_at()` |
| `team_members_set_updated_at` | team_members | BEFORE UPDATE, FOR EACH ROW | `public.set_updated_at()` |
| `operators_set_updated_at` | operators | BEFORE UPDATE, FOR EACH ROW | `public.set_updated_at()` |
| `clients_set_updated_at` | clients | BEFORE UPDATE, FOR EACH ROW | `public.set_updated_at()` |
| `projects_set_updated_at` | projects | BEFORE UPDATE, FOR EACH ROW | `public.set_updated_at()` |
| `project_rate_overrides_set_updated_at` | project_rate_overrides | BEFORE UPDATE, FOR EACH ROW | `public.set_updated_at()` |

Tables with NO trigger (no updated_at): organizations,
organization_members, clockify_imports, time_entries, comments,
pending_imports, invoices, cc_expense_batches, cc_expense_lines,
cc_highlight_keywords, cc_auto_rules.

# Migration index (which of the 41 files touched the schema)

| migration | schema effect |
|---|---|
| 20260520120001_init | organizations, profiles, organization_members, `org_role`, `set_updated_at()`, pgcrypto |
| 20260520120002_time_tracking | team_members, clockify_imports, time_entries, `is_org_member()` |
| 20260520120003_batch_and_admin | + `clockify_imports.name` |
| 20260521090001_table_comments | COMMENT ON only |
| 20260521120001_fix_recursive_rls | RLS policies only |
| 20260521140001_summary_rpc | RPC only |
| 20260521170001_org_base_rate_and_source_backfill | + `organizations.default_hourly_rate_usd`; back-computed `duration_seconds` |
| 20260521190001_lock_cost_and_team_summary | + `time_entries.billout_cost_usd`; partial index `…_start_billout` |
| 20260521210001_entries_filter_totals | RPC only |
| 20260521230001_split_client_project | RENAME `client`→`operator`; re-add `client`; split backfill |
| 20260522000001_operator_filter_options_and_comments | + `time_entry_comments` table (short-lived) |
| 20260522020001_global_comments | DROP `time_entry_comments`; + `comments` |
| 20260522030001_summary_by_op_client_project | RPC only |
| 20260523000001_import_scoping | + `clockify_imports.source`; batch attribution backfill; rian team_member row; `user_can_access_entry()` |
| 20260523010001 / 020001 / 040001 | RPC only |
| 20260523030001_teams | + teams; + `team_members.team_id` (NOT NULL after backfill); email uniqueness org→team; `is_org_owner()`, `is_super_admin_caller()` |
| 20260523050001_projects | + projects v1 (text triple + NULL-safe unique index) |
| 20260524000001_operator_client_project_entities | + operators, clients; projects refactor (client_id+name, DROP text triple); + `time_entries.project_id` + backfill |
| 20260524000002 / 000005 / 000006 / 000007 / 000010 / 000011 | RPC only |
| 20260524000003_pending_imports | + pending_imports |
| 20260524000004_billout_rate | + `team_members.billout_rate_usd`; + `time_entries.billout_amount_usd` |
| 20260524000008_project_billout_adjustments | + `projects.billout_adjustment_pct/_amount` |
| 20260524000009_project_rate_overrides | + project_rate_overrides; `effective_billout_rate()` |
| 20260524000012_override_pct | `override_rate_usd` DROP NOT NULL; + `override_pct`; XOR CHECK |
| 20260524000013_eliminate_proportion | + `team_members.cost_rate_usd`; rescale `billout_rate_usd`; re-stamp both entry money stamps; RPC rewrites |
| 20260525000001_restamp_billout_for_entries | RPC only |
| 20260525000010_invoices | + invoices; + `time_entries.invoice_id/_applied_at/_applied_by`; 'Legacy transfers' backfill |
| 20260525000011_invoice_status_filter | RPC only (status predicate change) |
| 20260525000020_invoice_date | + `invoices.invoice_date` |
| 20260525000030_set_team_member_for_entries | RPC only |
| 20260525000040_cc_expenses | + cc_expense_batches, cc_expense_lines |
| 20260525000050_cc_rules | + cc_highlight_keywords, cc_auto_rules |
| 20260525000060_cc_auto_assigned | + `cc_expense_lines.auto_assigned` |
| 20260609000010_projects_rls_org_member | RLS only — projects opened to all org members (see §11) |

---

# Schema history that matters for migration

A migration script reading this database must understand how values
got their current meanings. The load-bearing facts:

## 1. `client` → `operator` rename + client split (20260521230001)

- The ORIGINAL `time_entries.client` column held the operating agency
  ("Bowden Works" / "PlusROI") — spreadsheet-naming legacy. It was
  **renamed to `operator`**.
- A NEW `client` column was added and populated by splitting `project`
  on the FIRST `' : '` (space-colon-space):
  `client = trim(left)`, `project = trim(right)`. Rows without
  `' : '` kept `project` as-is with `client` NULL (→ "Blocked").
- Toggl-export recombination contract (legacy): Toggl "Client" column
  = operator; Toggl "Project" column = `"{client} : {project}"`.

## 2. The entity migration + backfill (20260524000001)

The biggest structural change. Order of operations:

1. Created `operators` (backfilled from distinct
   `time_entries.operator`) and `clients` (backfilled from distinct
   `(operator, client)` pairs).
2. `projects` (then keyed by an org+text-triple unique index) gained
   `client_id` + `name`; existing rows linked by case-insensitive text
   match through the new entities; a projects row was inserted for
   every distinct (operator, client, project) triple in time_entries.
3. **Dropped** `projects.operator/client/project` and the old unique
   index; added `projects_client_lower_name_unique`; set
   `client_id`/`name` NOT NULL.
4. Added `time_entries.project_id`, backfilled by case-insensitive
   triple match through the entity chain.
5. **The text triple on `time_entries` was kept** as a denormalized
   cache. Every summary/filter RPC still groups/joins on
   `lower(text)`, not on `project_id`; app-layer rename actions update
   both. Cache drift is a known bug class ("Halo Programs" ghost).

## 3. The rate-model evolution (what stored dollars mean)

| ver | migration | model |
|---|---|---|
| v1 | init | `team_members.hourly_rate_usd` (Adi's rate as org proxy) + `rate_proportion`; `converted_duration_seconds = duration × proportion` |
| v2 | 20260521170001 | base rate → `organizations.default_hourly_rate_usd` (seeded from Adi's row, fallback 14.00). Backfilled missing `duration_seconds = round(converted / proportion)` |
| v3 | 20260521190001 | `billout_cost_usd` stamp added; historical backfill `= (converted/3600) × org rate` |
| v4 | 20260523030001 | per-owner `teams.base_rate_usd` replaces org rate (Adi = legacy rate/35 fallback, Rian = 50) |
| v5 | 20260524000004 | `team_members.billout_rate_usd` (per CONVERTED hour) + `billout_amount_usd` stamp |
| v6 | 20260524000009/12 | `project_rate_overrides` + `effective_billout_rate()` precedence: member-specific override → project-wide override → member rate; pct XOR absolute |
| v7 | 20260524000013 | **proportion elimination** (ADR #034) — see below |

v7 details (critical):

- `team_members.cost_rate_usd` added `= round(base_rate × proportion,
  2)`; `gary@rian.ca` forced to `0` (AI worker — zero cost, billable).
- `team_members.billout_rate_usd` **rescaled in place**
  `× rate_proportion` — its meaning changed from per-converted-hour to
  **per-source-hour**. Dollar-equivalent by construction:
  `source × new_rate == converted × old_rate`.
- Every entry re-stamped: `billout_cost_usd = round((duration/3600) ×
  cost_rate_usd, 2)`; `billout_amount_usd = round((duration/3600) ×
  coalesce(effective_billout_rate(...), 0), 2)` (NULL when duration
  NULL). Byte-identical for existing data.
- **Silent semantics change:** absolute-rate overrides switched from
  per-converted-hour to per-source-hour. No absolute overrides existed
  at that moment (the only rule, "Prompt Victoria", was pct), so no
  data changed — but any absolute override created since behaves
  per-source-hour.
- After v7, "billout_seconds" in the summary RPCs = source
  `duration_seconds` (was `converted_duration_seconds`).
- `rate_proportion` and `teams.base_rate_usd` stay in the schema but
  are dead for money math; proportion still stamps
  `converted_duration_seconds` at write, consumed ONLY by the Toggl
  CSV export (still present in code at `app/(app)/transfer/` +
  `lib/toggl.ts`, but confirmed for phase-out).

## 4. Transfer → invoice lock migration (20260525000010/11)

- `transferred_at/transferred_by/transfer_batch_id` retained verbatim
  as historical audit; **new writes never touch them**.
- Canonical lock became `invoice_id IS NOT NULL`.
- Backfill: one `'Legacy transfers'` invoice per org (status
  `'paid'`); every entry with `transferred_at IS NOT NULL` got
  `invoice_id` = that invoice, `invoice_applied_at =
  transferred_at`, `invoice_applied_by = transferred_by`.
- Status predicate change (20260525000011), needed to replicate
  filters: "pending" = these 7 all non-NULL — `team_member_id,
  operator, client, project, description, end_at, source_user_email` —
  AND `invoice_id IS NULL AND transferred_at IS NULL` (the pre-invoice
  predicate had additionally required `converted_duration_seconds` and
  `converted_user`). "applied"/"transferred" = `invoice_id IS NOT NULL
  OR transferred_at IS NOT NULL` (belt-and-braces).

## 5. Dropped table: `time_entry_comments`

Created 20260522000001 (per-entry comments: `id`, `org_id`,
`entry_id` FK→time_entries D:CASCADE, `author_id` FK→auth.users
D:SET NULL, `body` CHECK non-empty, `created_at`; two indexes).
**Dropped same-day** by 20260522020001 (plain `drop table` — any rows
were destroyed, no copy) and replaced by the org-wide `comments`
board. If per-entry threads return in the rebuild, that's a fresh
feature, not a migration.

## 6. `team_members` uniqueness change

`unique (org_id, email)` (`team_members_org_id_email_key`, from init)
→ dropped 20260523030001 → `unique (team_id, email)`
(`team_members_team_id_email_key`). Same email may appear in multiple
teams within an org (`rian@rian.ca` does).

## 7. Seeded/backfilled reference data (magic values a migration will meet)

- Org slug `bowden-works`.
- Magic emails: `info@adipramono.com` (Adi), `rian@rian.ca` (Rian —
  both a profile/auth user AND a team_members row),
  `matmoncoo@gmail.com` (Matt, proportion 0.7), `gary@rian.ca` (Gary
  the AI — cost 0, billout set via app).
- Magic row names: import batch `Initial 2026 migration` (source
  `backfill`, filename
  `Time Sheet _ Time Log _ Hours _ Tingang.id _ Adi.xlsx`); invoice
  `Legacy transfers` (status `paid`).
- `consolidate_as = 'Tingang'` on Tingang workers collapses their
  display identity on converted entries.
- Zero-uuid sentinel `'00000000-0000-0000-0000-000000000000'` inside
  `project_rate_overrides_unique`.

---

# Columns the rebuild drops

Cross-referenced to the phase-out table in 00-vision-and-scope.md and
the entity mapping in 02-domain-model.md §7. "Migrate?" = whether the
data must be carried (even if archived) vs. discarded.

| current column(s) | phase-out driver | migrate? |
|---|---|---|
| `time_entries.transferred_at`, `transferred_by`, `transfer_batch_id` | Toggl export dropped (confirmed by rian 2026-07-06) | Audit already copied to `invoice_applied_at/by` via 'Legacy transfers'; verify the backfill check in 05-migration-plan, then drop (02-domain-model: "transferred_at dropped after backfill check") |
| `time_entries.converted_user`, `converted_duration_seconds` | rate_proportion / converted-hours machinery (ADR #034) | Archive only if the diff harness needs them, else drop (00-vision-and-scope) |
| `team_members.rate_proportion` | same | No — cost_rate/billout_rate already carry the real numbers |
| `team_members.hourly_rate_usd` | dead since v2 of the rate model | No |
| `organizations.default_hourly_rate_usd` | dead since v4 | No |
| `teams.base_rate_usd` — and the whole `teams` table | teams dissolve into engagement scoping (02-domain-model §7) | Team membership → engagements; the rate column: no |
| `time_entries.operator`, `client`, `project` (the display-cache ROLE) | resolved rows display/group/join via FKs exclusively (02-domain-model §3) — the cache role dies, the columns carry | Yes — carried as `raw_operator`/`raw_client`/`raw_project`, an immutable import payload (provenance on resolved rows; the ONLY identity + display for unassigned/Blocked rows); migration must still verify cache/FK agreement first (drift = data bug to resolve, see rebuild deltas) |
| `profiles.must_change_password` | demoing-era password login replaced by id-auth accounts | No |
| `profiles` (whole table) + all `auth.users` FKs | Supabase retired; → `users` (idauth_username-keyed) + person parties | Identity mapping migrates (email → idauth_username) |
| `organizations` / `organization_members` (incl. `org_role` enum) | → tenants + seeded engagements (02-domain-model §1, §4) | Yes — as the seed graph, not as tables |
| `team_members` (whole table) | → person parties + engagements + compensation_terms; `consolidate_as` → import-mapping config on the engagement | Yes — restructured |
| `project_rate_overrides` (whole table) | → dedicated `rate_overrides` table, near-verbatim (02-domain-model §2, judgment call #25); NOT flattened into compensation_terms in R1 — R4 revisits | Yes — near-verbatim; keep XOR fixed/pct semantics, project-wide NULL-member rows, resolution-time semantics + specificity precedence |
| `clockify_imports.source = 'toggl'` rows / `pending_imports` toggl CHECK arm | Toggl import path dropped (judgment call #8) | Rows migrate as plain data; historical `'toggl'`/`'backfill'` values carry VERBATIM — the new CHECK admits them; the importer only ever writes `clockify` \| `manual` (02-domain-model §3) |

Kept (explicitly carried over): the locked-at-write stamps
(`billout_cost_usd`/`billout_amount_usd` → `cost_usd`/`billout_usd`),
the invoice lock (as `invoice_line_id`), the invoice attach audit
(`invoice_applied_at`/`invoice_applied_by` →
`invoice_lines.attached_at`/`attached_by`, 05-migration-plan step 2.7),
`time_entries.source_rate_usd`/`source_amount_usd` (live import
provenance, 02-domain-model §3),
`clockify_imports.imported_by`/`imported_at` (carried onto
`import_batches`, 05-migration-plan step 2.5),
`invoices.manual_total_usd` (the invoice shape is kept
column-for-column in R1 — 02-domain-model §3, judgment call #24; R4
revisits), `projects.income_usd` + both
billout adjustment columns (until R4), the whole CC expense feature,
`comments`, naive source timestamps (see deltas), and the
project-name-unique-per-client constraint.

---

# Rebuild deltas (schema-level flags for the new design)

Things the current schema gets wrong or leaves loose, which the
rebuild docs already decide differently or must decide:

1. **Column-level money secrecy is broken at the DB layer.** Since
   20260609000010, any org member can read AND write
   `projects.income_usd` and the adjustment columns; only app checks
   (`canSeeIncome`) protect them. The rebuild's authz engine
   (04-architecture.md) replaces RLS wholesale — the parity truth
   tables must restore the ORIGINAL intent (managers never see
   income), not the current DB reality.
2. **Money precision is inconsistent:** `billout_cost_usd`
   numeric(10,4) (but stamped 2dp since v7), `billout_amount_usd`
   numeric(12,2), `source_rate_usd`/`source_amount_usd` numeric(10,4),
   `manual_total_usd` numeric(10,2). 04-architecture.md standardizes
   on `Numeric(12,2)` decimals end-to-end; source_rate/amount are
   carried as raw import provenance (02-domain-model §3) and may
   justify 4dp — decide explicitly.
3. **`clockify_imports.source` has no CHECK** while
   `pending_imports.source` does (and their value sets differ:
   backfill/clockify/toggl vs clockify/toggl). New `import_batches`
   gets ONE constrained vocabulary whose CHECK also admits the
   historical `'toggl'`/`'backfill'` values verbatim; the importer
   only ever writes `clockify` | `manual` (02-domain-model §3).
4. **The denormalized text triple vs `project_id`** — every current
   RPC filters/groups on `lower(text)`. Migration prerequisite: assert
   `(operator, client, project)` text matches the `project_id` entity
   chain on every row; divergences are data bugs to fix BEFORE
   cutover, since the new system reads only FKs.
5. **Naive timestamps by design** — `start_at`/`end_at` are `timestamp`
   WITHOUT time zone (source wall-clock preserved; all RPC window
   params likewise). Multi-tenant rebuild must make an explicit call
   (per-tenant timezone? store naive + tenant TZ?). Do NOT blindly
   convert to timestamptz during migration — there is no TZ to convert
   from.
6. **Index debt, don't replicate:** three overlapping
   `(org_id, project, start_at)` btrees on time_entries plus the
   legacy partial `idx_time_entries_org_transferred`; index the new
   schema from its own query plans.
7. **`pending_imports.expires_at` cleanup never built** — expired
   staging rows accumulate. The rebuild's import_batches staging needs
   a real TTL job.
8. **No unique on `cc_expense_lines(batch_id, line_index)`** — add it.
9. **`transfer_batch_id` is an FK-less uuid** — dies with Toggl.
10. **Case-insensitive name uniqueness is via unique indexes on
    `lower(name)`** (operators, clients, projects, invoices, both cc
    keyword tables) — not citext, not constraints. Any ON CONFLICT
    must target the index expression. Rebuild: pick one mechanism
    (citext or the same expression-index pattern) consistently.
11. **`invoices.status` lifecycle is unconstrained** — `sent_at`/
    `paid_at` are independent of `status`. R1 keeps the current
    open/sent/paid shape as-is (02-domain-model §3, judgment call
    #24); the richer draft/issued/void vocabulary — and tying
    timestamps to transitions — is R4-only.
12. **`profiles.email` can drift from `auth.users.email`** — a
    Supabase-specific hazard the `users` table design avoids by having
    exactly one identity source (idauth_username).
13. **No audit trail exists anywhere in this schema** — the rebuild
    adds `services/audit.py` + audit rows (04-architecture.md); the
    migration cannot recover historical who-did-what beyond the few
    `*_by` columns.
14. **Durations are `integer` seconds** — fine; summary RPCs cast sums
    to bigint. Keep integer seconds in the rebuild.