Skip to main content

Key tables (map)

A quick map of the ~30 tables, grouped. Full column detail lives in the migrations; this is the orientation layer. ⚠ = has its own gotcha note.

Identity & tenancy​

  • profiles — 1:1 with auth.users (name, avatar, notification prefs). Created by trigger handle_new_user().
  • companies — tenants (name, slug UNIQUE, branding colours, is_active, union_notification_email). ⚠ world-readable.
  • company_memberships — user↔company + role. Source of truth for tenancy & roles. ⚠ self-provision escalation.
  • user_encryption_keys — public/encrypted-private keypair. Unused (the E2E claim).
  • auth_audit_log — login/logout, server-written via log_auth_event.

Cases​

  • cases — the core. status (⚠ enum), flags (has_preliminary_inquiry, dismissal_intent, employee_rep_or_union_related), milestone dates, outcome, access_password (⚠), intervenientes jsonb, employee_*.
  • case_messages, case_attachments (is_from_employee), case_assignments, case_team_members (role_in_case), case_response_data (employee's defence, 1:1).

Workflow​

  • workflow_transitions — append-only status history.
  • workflow_rules — per-tenant FSM config, ⚠ ignored by the app.
  • case_gates, case_checklist_items (+ _attachments, checklist_audit_log), case_deadlines, inquiry_actions (⚠ JSONB step_data).
  • case_phase_deliverables — ⚠ never created in migrations.

Evidence, audit, dossier​

  • evidence_items (sha256_hash chain-of-custody, phase_captured).
  • case_audit_events — ⚠ client-authored audit.
  • case_dossier_exports — export jobs.

Portal & notifications​

  • process_portal_links, portal_items, portal_access_log — ⚠ portal flow.
  • notifications — realtime; user_id has no FK, no INSERT policy.

Email infra (service-role only)​

  • email_send_log (partial-unique dedupe idx), email_send_state (singleton config), suppressed_emails (append-only), email_unsubscribe_tokens. PGMQ queues auth_emails / transactional_emails (+ DLQs). ⚠ Lovable-coupled.