Tracking State & Audit — Supabase Design (triunalabs-internal)

Status: proposal / review — nothing is applied to the database until the migration in migrations/0001_internal_tracker.sql is run by a human. Target project: triunalabs-internal (Supabase project xhtoksoxunpfnkhfemdb, org Triune Amigos). Internal-only; separate from the estate-sale product DB.

Goal

Make tracker actions dynamic and accountable — "move to backlog", "move to sprint", "change status" become real writes that record who / when / why — without turning internal tooling into a full product-grade app.

Principle: git = plan, Supabase = state + audit

Layer	Source of truth	Why
Plan — phases, gates, product milestones, work-item definitions	roadmap.json + status.json in git	deliberate, reviewed, diffable, offline; the commit history is the record of why the plan changed
Activity — status, sprint, owner, and every change with actor + reason	Supabase (internal_tracker_*)	who/when/why is exactly a DB's strength and exactly what JSON-in-git can't do ergonomically

This also consolidates: the same triunalabs-internal project holds the HITL tasks (replacing the earlier GitHub-issues idea), so there's one internal system, not three. Slack stays the notification layer; internal_files stays the evidence store.

Reuse what's already there

The project already has the building blocks:

• profiles — actor identity (auth.uid() → profile) for who did it.
• file_events — an append-only events pattern we mirror for the audit log.
• internal_projects — TroveSnap is a row here; the tracker ties to a project, so this generalizes to GCMD / TalkTheTic later.
• internal_files — HITL evidence (S-corp plan, trademark proof) links here.

Schema (3 new tables, internal_ convention)

internal_tracker_items — mutable state

One row per work item. Holds state only; gate/phase definitions stay in roadmap.json. code = the WI-### shown in the tracker; slug = the status.json id / item-page filename.

internal_tracker_events — append-only audit (the who/when/why ledger)

One row per change. actor_profile_id from auth.uid(); reason is the text captured by the "why?" dialog. No update/delete — append-only.

internal_tracker_tasks — HITL human tasks (consolidates A4)

Owner/reviewer as profiles, evidence as an internal_files link, status + waiting-on + milestone. Replaces hitl-tasks.json as the live source (JSON becomes a generated snapshot).

Full DDL + RLS: migrations/0001_internal_tracker.sql.

Auth & RLS

• Writes require login so the actor is captured (auth.uid() → profiles).
• RLS enabled on all three tables. Policy: authenticated users may select / insert / update items and tasks; internal_tracker_events is insert + select only (append-only — no update/delete). Tighten to a team allow-list later if the user base grows.
• A static page using supabase-js ships the anon key (publishable) — safe because RLS gates everything; for extra safety the write UI can sit behind Cloudflare Access.

Write path

action button (Move to sprint / backlog / status)
  → "why?" dialog (required reason)
  → supabase-js (authed): insert internal_tracker_events  +  update internal_tracker_items
  → tracker re-reads (live board) or sync regenerates the static snapshot

Decided (D14): Cloudflare-Access-gated Worker. The internal site is behind Cloudflare Access, so users already log in once via Cloudflare — a second in-page Supabase login would be redundant. The page POSTs the change to a Worker (worker/tracker-write.mjs) behind the same Access app; the Worker verifies Cf-Access-Jwt-Assertion, then writes to Supabase with the service role (actor = the Access email). No Supabase key or login in the browser; the service role never leaves the Worker. Local dev uses a TRACKER_DEV_ACTOR fallback. See worker/README.md.

Sync & the static snapshot (hybrid)

Phase 1 keeps the published tracker static so partners keep a read-only view and the build stays fast/offline:

Supabase (state+audit)  --sync script-->  status.json snapshot  -->  npm run docs:tracker  -->  static HTML

The sync script (scripts/tracker-sync.mjs, to be written) pulls internal_tracker_items for the TroveSnap project, merges state onto the git-owned plan, writes status.json, and regenerates. Run manually, on a git hook, or a Cloudflare cron. (A live in-page board reading Supabase directly is a later option, not required now.)

Phasing

1. Schema + seed (read-only mirror). Run the migration; seed the TroveSnap internal_projects row; one-time seed internal_tracker_items from the current status.json. Tracker still static. No behavior change yet.
2. Write actions + dialog. Wire the item-page / board buttons to write events + state with the required-reason dialog; add the sync-back script.
3. HITL consolidation. Move hitl-tasks.json into internal_tracker_tasks with profiles owners and internal_files evidence; render "Waiting on humans" from it.
4. (Optional) live board. A small authed in-page board reading Supabase in real time, if the static+sync cadence proves too slow.

Non-goals

• Not moving the plan (phases/gates/milestones) into the DB — that stays in git.
• Not building a full project-management app — scope stays "state + audit + HITL" for the founding team.

Open decisions (for Paul + Codex)

1. Confirm triunalabs-internal as the home and the internal_tracker_* naming.
2. Owners: agents (Codex/Claude) as free-text owner_label, humans as profiles — OK? (migration supports both.)
3. Write path: supabase-js + RLS (recommended) vs Edge Function.
4. Sync cadence: manual / git-hook / Cloudflare cron.
5. Whether to gate the write UI behind Cloudflare Access in addition to RLS.

How to apply (when approved)

Run migrations/0001_internal_tracker.sql in the Supabase SQL editor for triunalabs-internal (or via the Migrations tab). It is idempotent (create table if not exists, create policy guarded). Then seed and wire per the phasing above.