Identity & activity: how the tables actually connect

Traced from supabase/migrations/** and schema_dump.sql, not from memory. Last verified: 2026-07-18 The headline is not the shape of the graph. It’s that three different tables answer “who did this,” and the bridge between them is reportedly holding one row.

The identity core

Four tables, two separate uuid spaces. Everything downstream inherits this split.

  ID SPACE A                                              ID SPACE B
 ┌──────────────────────┐                                ┌─────────────────────┐
 │ auth.users           │──── auth_user_id ──┐           │ people              │
 │ Supabase auth        │                    │           │ own uuid PK         │
 │ uuid PK              │                    ▼           │ directory record    │
 └──────────┬───────────┘          ┌──────────────────┐  └─────────────────────┘
            │ same uuid            │ users_auth       │            ▲
            │ (shared PK)          │ THE BRIDGE       │─ person_id ┘
            ▼                      │ ~1 row (!)       │
 ┌──────────────────────┐          └──────────────────┘
 │ user_profiles        │
 │ id IS auth.users.id  │◀╌╌╌ people.auth_user_id ╌╌╌╌╌ (nullable, NO foreign key)
 │ is_admin             │
 └──────────────────────┘

Read it this way:

auth.users and user_profiles are effectively one thing — same uuid, shared primary key. user_profiles.id has a FK to auth.users(id) ON DELETE CASCADE, and a signup trigger populates it.
people is a genuinely different id space. It has exactly one foreign key (company_id → companies) and no FK to auth at all.
The only enforced route between the spaces is users_auth. Every project-scoped RLS policy resolves identity through it, documented verbatim in the migrations as: auth.uid() → users_auth.auth_user_id → person_id.
There is a second, unenforced path: people.auth_user_id. It’s nullable with no FK, so it can hold a stale or nonexistent auth id indefinitely.

Where every activity table hangs

The tables aren’t usefully grouped by what they do — they’re grouped by which identity they key to, because that determines whether you can join them.

Lane A — `→ auth.users(id)`, enforced FK (~25 tables)

commitment_audit_log · permission_audit_log (changed_by) · email_events · ai_feedback_events · ai_retrieval_feedback · ai_learning_promotions · ai_task_feedback · drawing_change_history · user_table_views · ai_skill_usage_events · team_chat_messages · team_chat_channels · task_comments · psr_comments · dev_annotations · dev_panel_comments · design_violations · agent_learnings

Lane B — `→ user_profiles(id)`, enforced FK (same uuid values as Lane A)

app_error_events · app_error_groups · conversations · requests · training_docs · training_doc_steps · training_doc_assets · training_doc_relations

All four training tables live here.

Lane C — `→ people(id)`, a different uuid space

user_email_notifications · user_schedule_notifications · project_directory_memberships · project_role_members · user_directory_permissions · distribution_group_members · permission_audit_log (person_id) · project_companies.primary_contact_id · subcontractor_sov_submissions Reachable from a logged-in user only via users_auth.

Lane D — bare uuid, no foreign key at all (~10 tables)

db_audit_log · projects_audit · budget_line_history · submittal_history · ai_tool_write_audits · acumatica_outbound_audit_logs · app_request_log · chat_history · chat_sessions · document_user_access Values look like auth ids by convention. The database enforces nothing, so these can hold orphans forever.

Lane E — no identity column at all

documents_access_audit · erp_sync_log · acumatica_sync_runs_log · bot_debug_log

Lane F — AI database (`fqcvmfqldlewvbsuxdvz`), separate project

outlook_email_skip_audit · source_sync_runs · source_processing_jobs · ingestion_jobs · ingestion_dead_letter · pipeline_model_usage · document_chunk_retrieval_telemetry · packet_refresh_jobs · rag_pipeline_state · source_sync_health_snapshots · system_alerts Why this grouping matters: Lanes A and B hold the same uuid values, so a query can bridge them — but they aren’t schema-compatible, so there’s no single join you can write across all audit tables. Lane C is a different value space entirely.

Training tables — exact FKs

Table	Identity FK	Other FKs
`training_docs`	`created_by`, `updated_by` → `user_profiles(id) SET NULL`	none
`training_doc_assets`	`created_by` → `user_profiles(id) SET NULL`	`training_doc_id → training_docs CASCADE`
`training_doc_steps`	`created_by` → `user_profiles(id) SET NULL`	`training_doc_id → training_docs CASCADE`, `screenshot_asset_id → training_doc_assets SET NULL`
`training_doc_relations`	`created_by` → `user_profiles(id)` — no `ON DELETE`	`source_doc_id`, `target_doc_id` → `training_docs CASCADE`

training_doc_relations.created_by is the odd one out: it defaults to NO ACTION while its three siblings are SET NULL. Deleting a user profile will block if it authored a relation.

What’s broken

Ranked by blast radius.

01 — The bridge: `users_auth` reportedly holds ~1 row

Flagged in docs/architecture/tables.yaml as a critical bug: most signups aren’t producing the bridge row, despite ~7 writer paths. Impact: every people-keyed RLS policy evaluates false for nearly all users. Lane C and all project-directory permissions are effectively dead.

02 — The admin gate: `user_profiles` reportedly empty, 123+ code paths read it

current_is_app_admin() does a live SELECT is_admin FROM user_profiles WHERE id = auth.uid(). All four training tables FK to this table. Impact: if it’s genuinely empty, training-doc inserts with a non-null created_by fail the FK, and every training RLS policy denies. tables.yaml says outright not to treat is_admin here as authoritative until this is resolved.

03 — Two admin functions that disagree by construction

is_admin() was migrated to read a JWT claim set at token issuance (20260517010000_rewrite_is_admin_to_read_jwt.sql). current_is_app_admin() never was — it still does a live table read. Impact: the two gates diverge for the lifetime of a JWT after any admin-flag change. user_profiles’ own policies use is_admin(); training, RAG, and budget tables use current_is_app_admin().

04 — Two competing auth→person resolvers

current_person_id() was redefined incompatibly between 20260409150000_commitment_sov_rls.sql (reads people.auth_user_id) and 20260427130000_secure_rag_documents_rls.sql (prefers users_auth, falls back to people.auth_user_id). Whichever migration ran last wins globally.

05 — A foreign key with mismatched types

timeline_events.actor_id is declared text REFERENCES auth.users(id) (20260401000001_change_workflow_tables.sql:145), but auth.users.id is uuid. Postgres has no cross-type equality operator for that constraint. Impact: either the constraint failed at migration time and the column is FK-less, or the live column isn’t actually text. Verify against the live DB before trusting this edge.

06 — An ACL table with zero referential integrity

document_user_access and document_group_access have no foreign keys at all — only composite primary keys. user_id is a dangling uuid; document_id is text while documents.id is uuid. Impact: orphaned access grants cannot be detected by the database.

What this means for the training layer

Decision	Where the graph points
Which identity should a `training_completions` row key to?	`user_profiles(id)` — matches the four existing training tables and stays in the id space that has rows behind it. Do not key to `people` unless the bridge is fixed first.
Can employees read `training_docs` today?	No. Admin-only RLS. The reader surface uses a server-side service client to bypass it.
Can that pattern extend to per-user progress?	No. A service-role bypass can’t express “read only your own row.” Progress tables need real user-keyed policies — `auth.uid() = user_id`, i.e. the `user_profiles` space.
Blocker to clear first	Finding 02. If `user_profiles` is empty in production, the training tables’ `created_by` FK and every training RLS policy are already failing. That’s upstream of any new feature.

Confidence

Structure is solid. Every table, column, and foreign key above was read from migration files and schema_dump.sql. Those are authoritative. Row counts are not. “users_auth has ~1 row” and “user_profiles is empty” come from hand-written notes in docs/architecture/tables.yaml, which may be stale. Findings 01 and 02 are the two that would change what you build. Both need one live count(*) before anyone commits to a plan.

Training Docs Architecture — what’s built in the training system
docs/architecture/tables.yaml — source of truth for table metadata
docs/architecture/TABLE-LIST.md — generated live table list

​Identity & activity: how the tables actually connect

​The identity core

​Where every activity table hangs

​Lane A — → auth.users(id), enforced FK (~25 tables)

​Lane B — → user_profiles(id), enforced FK (same uuid values as Lane A)

​Lane C — → people(id), a different uuid space

​Lane D — bare uuid, no foreign key at all (~10 tables)

​Lane E — no identity column at all

​Lane F — AI database (fqcvmfqldlewvbsuxdvz), separate project

​Training tables — exact FKs

​What’s broken

​01 — The bridge: users_auth reportedly holds ~1 row

​02 — The admin gate: user_profiles reportedly empty, 123+ code paths read it

​03 — Two admin functions that disagree by construction

​04 — Two competing auth→person resolvers

​05 — A foreign key with mismatched types

​06 — An ACL table with zero referential integrity

​What this means for the training layer

​Confidence

​Related