Sphere audit log

Purpose

TBD by human

Identity & key fields

• Primary key: id (uuid, default gen_random_uuid()).
• sphereId (nullable uuid, FK → activities.spheres.id, on-delete set null) — null if the sphere was hard-deleted.
• sphereCode (text, NOT NULL) — preserved even after sphere deletion.
• actorUserId (uuid, NOT NULL) — raw sub claim from the super-admin Supabase project. INTENTIONAL: no .references() declared (per schema comment).
• action (enum sphere_audit_action: CREATE, UPDATE, DELETE).
• before, after (nullable jsonb).
• createdAt (timestamptz, NOT NULL).
• Indexes: sphere_audit_log_sphere_id_created_idx on (sphereId, createdAt DESC); sphere_audit_log_sphere_code_created_idx on (sphereCode, createdAt DESC).

business meaning: TBD by human

Invariants

• sphereCode, actorUserId, action, createdAt NOT NULL (enforced in tktspace-backend/libs/shared/data-access-db/src/lib/schema/activities.schema.ts).
• sphereId FK with ON DELETE SET NULL — log rows survive sphere deletion (enforced in tktspace-backend/libs/shared/data-access-db/src/lib/schema/activities.schema.ts).
• actorUserId has NO FK by design — super-admin auth uses a separate Supabase project from users.users (per schema comment in tktspace-backend/libs/shared/data-access-db/src/lib/schema/activities.schema.ts).

business invariants: TBD by human

Lifecycle

Append-only. No status column.

Relationships

• Sphere (ENT-037) — sphereId → activities.spheres.id, on-delete set null. N:1, nullable for deleted spheres.
• actorUserId — references a super-admin user in a separate Supabase project (no DB-level FK).

API surfaces

Surface	Exposed	Notes
client	no	—
business	no	—
super-admin	yes — /spheres/{id}/audit (SphereAuditEntryDto, SphereAuditAction)	Swagger UI

Known gotchas / open questions

• actorUserId is intentionally not FK-linked to users.users.id — super-admin auth is a separate Supabase project. Joins back to users will not work.
• before/after are free-form JSONB snapshots — schema TBD. Post-migration 0046 the defaultActivityType and allowedActivityTypes fields captured in these snapshots will carry enum string values (same wire format as before, but the DB-level column is now enum-typed).
• Two indexes — by id and by code — let queries survive the sphere being hard-deleted.
• 869dpxbj6 (migration 0049_worried_rattler.sql) DELETEd the CINEMA and SHOWS sphere rows after consolidating them into a new EVENTS row. Audit-log entries that previously referenced the CINEMA / SHOWS sphere ids have their sphere_id column NULLed by the ON DELETE SET NULL FK; sphere_code (text, NOT NULL) preserves the readable history ('CINEMA' / 'SHOWS' strings remain queryable). Joins through sphere_id return NULL for those rows; group-by-sphere_code queries continue to work. This is expected join-loss, not data-loss.