> ## Documentation Index > Fetch the complete documentation index at: https://meganharrisonconsulting.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Identity & Activity Map > How users, profiles, people, training, and activity/audit tables interconnect. # 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. *** ## Related * [Training Docs Architecture](/developer-docs/architecture/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