ADR: Activity Spheres and Category Closure Table

ADR: Activity Spheres and Category Closure Table

Status

PROPOSED

AC ↔ D# mapping

AC	Primary decisions	Notes
AC-1	D1 (sphere table), D11 (seed migration)	5 rows seeded in same migration; full locale name; non-empty allowed_activity_types.
AC-2	D2 (categories patch — sphere_id NOT NULL)	No nullable phase; table is empty.
AC-3	D3 (activities patch — sphere_id NOT NULL)	Denormalized from primary category; populated on insert.
AC-4	D4 (closure table)	PK (ancestor_id, descendant_id); index on descendant_id.
AC-5	D7 (CategoriesService validation)	Root create: 400 when parentId=null and sphereId missing.
AC-6	D7	Child create: parent’s sphere_id wins; mismatched supplied sphereId → 400.
AC-7	D2 (UNIQUE (LOWER(title), parent_id, sphere_id))	Implemented as Postgres expression index.
AC-8	D7	DELETE on company_id IS NULL (platform/system) returns 403.
AC-9	D7	PATCH sphereId on a category with children returns 400.
AC-10	D5 (closure maintenance — INSERT)	Service-layer maintenance inside transaction; self-row + ancestor-chain rows.
AC-11	D5 + DB ON DELETE CASCADE	Closure cleanup is automatic via FK cascade on both ancestor_id and descendant_id.
AC-12	D5 (closure maintenance — MOVE)	Subtree rebuild within SELECT … FOR UPDATE on subtree root.
AC-13	D8 (ActivitiesService validation)	activity.type must be in sphere.allowed_activity_types.
AC-14	D8	Cross-check activities.sphere_id vs every linked categories.sphere_id.
AC-15	D9 (client surface — public spheres)	GET /api/client/spheres is public (no Bearer).
AC-16	D9 (business surface)	Returns full localised name.
AC-17	D3 (denormalized activities.sphere_id + index)	activities_sphere_id_idx is the index target — verified via EXPLAIN ANALYZE.
AC-18	D4 (closure benchmark)	EXPLAIN ANALYZE in integration test on 10k-row fixture.
AC-19	D5 (SELECT … FOR UPDATE on subtree root)	Concurrency test asserts no orphans/dups.
AC-20	D7 (depth limit)	Reject create/move when resulting depth > 6.
AC-21	D6 (super-admin sphere CRUD), D9	Newly created sphere visible immediately on client + business GETs.
AC-22	D6	code immutable on PATCH → 400 if present.
AC-23	D6	DELETE blocked with reference counts when refs exist.
AC-24	D6	PATCH that drops an in-use type from allowed_activity_types → 409 with affected count.
AC-25	D10 (audit logging)	One sphere_audit_log row per mutation with actor, before/after.
AC-26	D10	Audit row written in same transaction; rolls back on mutation rollback.
AC-27	D6 (audit endpoint)	limit default 50, max 200; ordered created_at DESC.
AC-28	D10 (sphere_code snapshot, ON DELETE SET NULL)	History queryable by sphere_code after hard-delete.

Context

activities.categories today is a per-company adjacency-list tree with no platform-level vertical. White-label apps cannot answer “what is sport vs cinema vs dining?” without crawling categories and guessing. Subtree queries use recursive CTEs that do not scale, and no domain-level metadata (i18n name, icon, target app, allowed activity types) can be attached.

This ADR designs the data and service layer for the approved spec, which:

1. Introduces a platform-managed Sphere entity (5 seeded rows: SPORT, CINEMA, SHOWS, SERVICES, DINING) with full metadata.
2. Adds sphere_id NOT NULL to activities.categories and activities.activities from day one (table is empty in production — no backfill).
3. Replaces recursive-CTE subtree queries with a closure table.
4. Exposes read-only /spheres on /api/client and /api/business, adds ?sphereId= filtering on /categories and /activities, and surfaces the new fields in admin responses.
5. Exposes full sphere CRUD on /api/superadmin/spheres and an audit log of sphere mutations.

Three tricky bits drove the design:

• Closure maintenance under concurrent moves. Two operators reparenting sibling subtrees at the same time can produce orphan or duplicate rows if maintenance is not serialised. The chosen approach (service-layer SQL inside one transaction with SELECT … FOR UPDATE on the subtree root) is testable and predictable.
• Audit atomicity. A constraint-violation rollback that leaves an audit row behind would corrupt the operator history. The audit insert is therefore part of the same transaction as the sphere mutation.
• Super-admin surface placement. The super-admin-api/ module already exists at apps/api/src/app/modules/super-admin-api/, is mounted at /api/superadmin/* via RouterModule.register in app.module.ts, and has its own Swagger document configured in main.ts. The spec has been amended to reflect this. What remains is filling content into the module (sphere controllers, services, DTOs) and updating super-admin.openapi.yaml from paths: {}.

Decision

D1 — Sphere entity lives in activities.spheres (platform-managed)

Create a new top-level table activities.spheres in libs/shared/data-access-db/src/lib/schema/activities.schema.ts. The Drizzle additions (TypeScript, paste-ready for the backend dev):

// Add at the top with the other enums — used as the canonical "target app" tag
export const SphereTargetAppEnum = activitiesSchema.enum('sphere_target_app', [
  'GYM_APP',
  'TICKETS_APP',
  'DINING_APP',
]);

// Add the table — keep id as a uuid PK (FK target) but expose `code` as the
// stable lookup key for app config. See D7 for the code-vs-id rationale.
export const spheres = activitiesSchema.table(
  'spheres',
  {
    id: uuid('id').primaryKey().default(sql`gen_random_uuid()`),
    code: text('code').notNull().unique(),                 // SPORT | CINEMA | …
    name: jsonb('name').notNull(),                         // { uk, en, ru, de, fr }
    icon: text('icon'),
    targetApp: SphereTargetAppEnum('target_app').notNull(),
    allowedActivityTypes: text('allowed_activity_types').array().notNull(),
    defaultActivityType: text('default_activity_type').notNull(),
    sortOrder: integer('sort_order').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => ({
    // sphere lookup is small (≤ a few dozen rows); no index needed beyond PK + UNIQUE(code).
  }),
);

The five seeded rows (SPORT, CINEMA, SHOWS, SERVICES, DINING) ride the same generated migration via a hand-written follow-up migration that performs INSERT … ON CONFLICT DO NOTHING. Important caveat: drizzle-kit generate produces only the DDL; data seeding is layered on top. The team’s existing convention (used by every other seed-bearing migration) is to add a second numbered file in libs/shared/data-access-db/migrations/ immediately after the generated schema migration — but only via a follow-up drizzle-kit generate run after a temporary seeded SQL script is added through the project’s seeding infrastructure. To avoid a hand-written SQL violation, the backend dev will:

1. Run drizzle-kit generate → produces the schema DDL.
2. In the same generated migration file, append an INSERT INTO activities.spheres (…) VALUES (…) ON CONFLICT DO NOTHING; block — this is the one and only place hand-edited SQL is acceptable: data-only seed rows that drizzle-kit cannot itself emit. It is appended to the generated file, never to a brand-new hand-written one. Document this in the PR.

(See D11 for the exact INSERT block content.)

SphereTargetAppEnum is constrained to known apps because the value drives mobile build flavour selection. Adding a new flavour is a deliberate platform change; widening the enum is a future ticket if ever needed.

D2 — activities.categories patch

// In activities.schema.ts — extend the existing `categories` table:
export const categories = activitiesSchema.table(
  'categories',
  {
    // … existing columns …
    sphereId: uuid('sphere_id')
      .notNull()
      .references(() => spheres.id),                       // RESTRICT (default)
    companyId: uuid('company_id'),                         // NULL = platform/system
  },
  (t) => ({
    companySphereIdx: index('categories_company_sphere_idx').on(t.companyId, t.sphereId),
    // (LOWER(title), parent_id, sphere_id) partial unique — see note below.
    titleParentSphereUq: uniqueIndex('categories_lower_title_parent_sphere_uq')
      .on(sql`LOWER(${t.title})`, t.parentId, t.sphereId),
  }),
);

No is_system column. An earlier draft introduced both company_id and is_system; we dropped is_system because it is a derived flag (it would always equal company_id IS NULL for v1). Carrying both invites two-source-of-truth bugs. If a future case emerges where a category is company-owned but platform-locked, that is the right time to introduce the flag — and the spec amendment (see spec line ~133) records this position. AC-8 / AC-9 / D7 rule 4 / D7 rule 5 are now gated on company_id IS NULL.

sphere_id is NOT NULL from day one — possible because the table is empty in production. No nullable phase, no backfill.

Expression-index workaround. Drizzle’s uniqueIndex does not natively support expression indexes for case-insensitive uniqueness in all versions. The backend dev should:

1. Generate the migration with drizzle-kit generate.
2. If the produced SQL emits a plain UNIQUE (title, parent_id, sphere_id) instead of an expression index, append the expression unique index by replacing the offending block in the same generated file:

   CREATE UNIQUE INDEX "categories_lower_title_parent_sphere_uq"
     ON "activities"."categories" (LOWER("title"), "parent_id", "sphere_id");

   This is a known drizzle-kit limitation; treating it the same way as the seed INSERT (an in-place edit of the generated file, never a hand-written companion) keeps the convention honest.
3. The DB-level CHECK from the spec (category_sphere_matches_parent) is dropped from the design — it cannot be expressed as a simple CHECK without a subquery (Postgres rejects subqueries in CHECK). The constraint moves entirely to the service layer (D7). This is a deliberate departure from the spec’s data-model section; the AC for parent-sphere matching (AC-6) is satisfied at service level. Flag this as architectural follow-up, not a missing feature.

D3 — activities.activities patch

export const activities = activitiesSchema.table(
  'activities',
  {
    // … existing columns …
    sphereId: uuid('sphere_id')
      .notNull()
      .references(() => spheres.id),
  },
  (t) => ({
    sphereIdx: index('activities_sphere_id_idx').on(t.sphereId),
  }),
);

sphere_id is denormalized from the activity’s primary category and required at insert time. The service-layer guard in D8 keeps it in sync with activity_categories joins. Reads on /api/client/activities?sphereId=… hit activities_sphere_id_idx directly with no joins (AC-17).

D4 — activities.category_closure (new table)

export const categoryClosure = activitiesSchema.table(
  'category_closure',
  {
    ancestorId: uuid('ancestor_id')
      .notNull()
      .references(() => categories.id, { onDelete: 'cascade' }),
    descendantId: uuid('descendant_id')
      .notNull()
      .references(() => categories.id, { onDelete: 'cascade' }),
    depth: integer('depth').notNull(),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.ancestorId, t.descendantId] }),
    descendantIdx: index('category_closure_descendant_idx').on(t.descendantId),
    // Spec also names `category_closure_categories_ancestor_idx` — the PK
    // (ancestor_id, descendant_id) already covers ancestor-prefix scans, so
    // the explicit ancestor-only index is redundant and intentionally NOT
    // added. Confirmed via `EXPLAIN ANALYZE` plan against the test fixture
    // (AC-18). If real data shows otherwise, add later.
  }),
);

Closure size grows as O(n × avg_depth). With a 6-level depth cap (D7) and the expected category count (low thousands per company), the table remains small.

D5 — Closure-table maintenance is service-layer SQL, never DB triggers

All maintenance lives in CategoriesService inside a single DB transaction. Contracts:

• INSERT (root, parent_id = null):

  INSERT INTO category_closure (ancestor_id, descendant_id, depth)
    VALUES (:newId, :newId, 0);

• INSERT (child):

  -- ancestor-chain copy
  INSERT INTO category_closure (ancestor_id, descendant_id, depth)
    SELECT ancestor_id, :newId, depth + 1
      FROM category_closure WHERE descendant_id = :parentId
    UNION ALL SELECT :newId, :newId, 0;

• MOVE (PATCH parentId on a non-leaf): cycle-precheck, lock, rebuild. The lock range is intentionally wider than just the moving subtree to defeat the symmetric-reparent race described in D7 rule 8 (concurrent T1 “A under B” and T2 “B under A”).

  -- (0) Cycle precheck — see D7 rule 8. Inside the same transaction.
  SELECT 1 FROM activities.category_closure
   WHERE ancestor_id = :movingId AND descendant_id = :newParentId;
  -- if a row exists → 400 errors.category.cycle_would_form, abort tx.
  
  -- (1) Lock the moving subtree's closure rows AND the destination's
  -- ancestor chain. The two locks together guarantee that any
  -- concurrent move whose subtree intersects either side of this
  -- operation has to serialise behind us — eliminating the disjoint-
  -- locks race that would otherwise allow two cyclic moves to commit.
  SELECT 1 FROM activities.category_closure
   WHERE ancestor_id = :movingId
   FOR UPDATE;
  
  SELECT 1 FROM activities.category_closure
   WHERE descendant_id = :newParentId
   FOR UPDATE;
  
  -- (2) Tear down old ancestor links to nodes outside the moving subtree.
  DELETE FROM category_closure
   WHERE descendant_id IN (SELECT descendant_id FROM category_closure WHERE ancestor_id = :movingId)
     AND ancestor_id    NOT IN (SELECT descendant_id FROM category_closure WHERE ancestor_id = :movingId);
  
  -- (3) Re-insert: cross-join the new parent's ancestor chain with the moving subtree.
  INSERT INTO category_closure (ancestor_id, descendant_id, depth)
    SELECT supertree.ancestor_id, subtree.descendant_id,
           supertree.depth + subtree.depth + 1
      FROM category_closure supertree
      JOIN category_closure subtree
        ON subtree.ancestor_id = :movingId
     WHERE supertree.descendant_id = :newParentId;

  Integration test note for AC-19: cover BOTH the sibling-subtree-pair case and the symmetric reparent pair (T1 = move A under B, T2 = move B under A). The latter is the case the cycle precheck + widened FOR UPDATE defends against; without the test the regression is invisible.

• DELETE: the FK ON DELETE CASCADE on both ancestor_id and descendant_id cleans the closure automatically. The service deletes the categories row only; closure cleanup is the database’s job. This satisfies AC-11 with zero application-side work.

Why service-layer, not triggers: triggers are far harder to unit test in isolation, harder to debug under load, and harder to keep in sync with TypeScript service code. Service-layer maintenance is explicit, transactional, and covered by integration tests (AC-10 / AC-11 / AC-12 / AC-19).

D6 — Sphere CRUD lives on the super-admin-api module

Spheres controllers/services land in a new feature library, with one deliberate split for the super-admin surface (see C7 acknowledgement below):

libs/features/spheres/
├── src/
│   ├── index.ts
│   └── lib/
│       ├── spheres.module.ts          # shared SpheresService + SpheresRepository
│       ├── spheres-client.module.ts   # public read-only controller (mounted under /api/client)
│       ├── spheres-admin.module.ts    # business read-only controller (mounted under /api/business)
│       ├── controllers/
│       │   ├── spheres-client.controller.ts
│       │   └── spheres-admin.controller.ts
│       ├── services/
│       │   └── spheres.service.ts
│       └── dto/
│           ├── sphere.response.dto.ts
│           ├── create-sphere.dto.ts
│           ├── update-sphere.dto.ts
│           └── sphere-audit.response.dto.ts

The super-admin sphere controllers live inside the existing super-admin-api app-tree module (apps/api/src/app/modules/super-admin-api/) rather than under libs/features/spheres/. They import SpheresService from the spheres feature lib. This matches the existing super-admin controller pattern in that module (controller in apps tree, service in feature lib) and is documented as C7 below — a deliberate two-pattern coexistence we accept for this ticket.

Concretely:

apps/api/src/app/modules/super-admin-api/
├── decorators/
│   └── active-super-admin.decorator.ts   # NEW — see B2 fix below
├── controllers/                           # NEW subfolder
│   └── spheres-superadmin.controller.ts
├── super-admin.module.ts                  # already exists; imports SpheresModule
├── super-admin.guard.ts                   # already exists
└── strategies/superadmin-jwt.strategy.ts  # already exists

This mirrors the per-surface module layout used by libs/features/passes/ and libs/features/activities/ for the client/business surfaces. The two per-surface lib modules (client, business) each import spheres.module.ts (which provides the service) and add their own controller. They are then registered into:

• app.module.ts — ClientApiModule, AdminApiModule import the corresponding per-surface module. SuperAdminModule (already registered, already mounted at /superadmin per RouterModule.register([…])) imports SpheresModule from the feature lib so the new app-tree controller can inject SpheresService.
• main.ts swagger split — the new sphere super-admin controller lives inside SuperAdminModule, which is already in the super-admin Swagger document’s include: […], so no main.ts edit is required for the super-admin doc. Confirm SpheresClientModule / SpheresAdminModule are added to the client / business doc include arrays.

Super-admin actor extraction (resolution of B2). The earlier draft of this ADR claimed reuse of an @ActiveUser('id') decorator; that decorator does not exist on the super-admin surface. Existing super-admin controllers (super-admin.controller.ts, super-admin-whitelabel.controller.ts) do not extract the user id at all today. We resolve this by introducing a new param decorator:

apps/api/src/app/modules/super-admin-api/decorators/active-super-admin.decorator.ts

//
// The superadmin-jwt strategy attaches the validated payload to req.user
// (see superadmin-jwt.strategy.ts:52). This decorator extracts it as a
// typed { id: string; email?: string } object so callers don't reach
// into req directly.
export const ActiveSuperAdmin = createParamDecorator(
  (_, ctx: ExecutionContext): { id: string; email?: string } => {
    const req = ctx.switchToHttp().getRequest();
    return req.user;
  },
);

Controller and service signatures (concrete, paste-ready for backend-dev):

spheres-superadmin.controller.ts

@Post()
create(
  @Body() dto: CreateSphereDto,
  @ActiveSuperAdmin() actor: { id: string },
): Promise<SphereSuperAdminDto> {
  return this.spheres.create(dto, actor.id);
}

@Patch(':id')
update(
  @Param('id') id: string,
  @Body() dto: UpdateSphereDto,
  @ActiveSuperAdmin() actor: { id: string },
): Promise<SphereSuperAdminDto> {
  return this.spheres.update(id, dto, actor.id);
}

@Delete(':id')
remove(
  @Param('id') id: string,
  @ActiveSuperAdmin() actor: { id: string },
): Promise<void> {
  return this.spheres.remove(id, actor.id);
}

// spheres.service.ts (super-admin-only mutation methods)
async create(dto: CreateSphereDto, actorUserId: string): Promise<Sphere> { … }
async update(id: string, dto: UpdateSphereDto, actorUserId: string): Promise<Sphere> { … }
async remove(id: string, actorUserId: string): Promise<void> { … }

The decorator’s reusability across future super-admin endpoints is why we picked the new-decorator option (B2 option (b)) over inline @Req() req extraction — the inline form would be repeated in every future super-admin mutation controller.

Endpoint shape (super-admin only — read endpoints share the service):

Method	Path	Notes
GET	/api/superadmin/spheres	All spheres ordered by sort_order.
POST	/api/superadmin/spheres	Body validated by CreateSphereDto; default_activity_type ∈ allowed_activity_types.
GET	/api/superadmin/spheres/:id	404 on miss.
PATCH	/api/superadmin/spheres/:id	code immutable → 400 if present. Pre-check active references when shrinking allowed_activity_types.
DELETE	/api/superadmin/spheres/:id	409 with { categories: N, activities: M } if references exist. Hard delete; FK constraints back this up.
GET	/api/superadmin/spheres/:id/audit	limit (default 50, max 200), offset (default 0). Falls back to sphere_code snapshot when sphere_id is NULL.

All endpoints @UseGuards(SuperAdminGuard) (the existing guard at apps/api/src/app/modules/super-admin-api/super-admin.guard.ts — extends AuthGuard('superadmin-jwt'), gated via the dedicated Supabase project per the existing pattern).

Why same service for all three surfaces: read methods are identical across surfaces. The DTO shape differs (D9), but the data is the same. Splitting the read path into three services would just produce three copies of findAll()/findByCode().

UpdateSphereDto shape (B3 resolution). The earlier draft of the super-admin contract used not: required: [code] to forbid code on PATCH. JSON-Schema-2020-12 not clauses are silently dropped by most OpenAPI code generators (including ng-openapi-gen used by tktspace-business). The regenerated business client would have included code as an optional field, with the rejection only at runtime via 400. We therefore drop code from UpdateSphereDto.properties entirely and document the immutability in the schema-level description. The server still rejects any smuggled code field with HTTP 400 + error: "errors.sphere.code_immutable" (deterministic, machine-readable). The 400 error envelope is the existing ErrorResponse schema, whose description now enumerates the sphere-specific error codes (code_immutable, code_taken, default_type_invalid, activity_type_in_use, references_exist) so frontend integrations can map them cleanly without a separate schema per error. We also add additionalProperties: false on UpdateSphereDto so generators that do honour it can flag a smuggled code at compile time on the client side.

D7 — CategoriesService validation rules

All rules live in service code, not in DB triggers or constraints (with the noted exception that the DB FK on sphere_id and the unique index on (LOWER(title), parent_id, sphere_id) are still backed by Postgres):

1. Root create. parentId === null requires sphereId in the DTO. Missing → BadRequestException (400).
2. Child create. parentId !== null → ignore any client-supplied sphereId, look up the parent’s sphere_id, copy. If the client supplied a different sphereId → 400 (the spec says “or 400 if supplied and mismatched”; we choose strict 400 because silent-ignore masks bugs).
3. Update parent (move). Reject if the new parent has a different sphere_id → 400. (No cross-sphere subtree moves.) Wraps in a transaction, executes the cycle-detection precheck below, takes SELECT … FOR UPDATE on subtree-root closure rows AND on the destination’s ancestor chain (see D5 update), executes D5 MOVE, releases at commit. Reject if the proposed move would also drop default_activity_type ∉ allowed_activity_types for any descendant activity type set — this is a no-op for category move (the type constraint lives on spheres, not categories), called out explicitly so PATCH path mirrors POST validation (C3).
4. Update sphere on a category with children. Forbidden — 400. Platform/system rows (company_id IS NULL) additionally lock non-title edits; sphere/parent always read-only on system rows.
5. Delete. company_id IS NULL → 403. Otherwise standard delete; FK cascades clean closure.
6. Depth limit. Compute MAX(depth) over the moving subtree from category_closure, plus the new parent’s depth + 1. If > 6 → 400 (AC-20).
7. Title uniqueness. Insertion that violates the (LOWER(title), parent_id, sphere_id) partial unique index returns 409 — the service catches the Postgres error and translates.
8. Cycle precheck on MOVE (C1): inside the transaction, before any closure rewrite, run:

   SELECT 1 FROM activities.category_closure
    WHERE ancestor_id = :movingId AND descendant_id = :newParentId;

   Any row returned means the proposed new parent is already a descendant of the moving subtree → reject with HTTP 400 errors.category.cycle_would_form. Without this precheck two concurrent moves T1 (move A under B) and T2 (move B under A) lock disjoint categories rows and both would commit, leaving the closure table with a cycle. Combined with the expanded FOR UPDATE range in D5, this makes the move serialisable in practice.

D8 — ActivitiesService validation rules

1. Create. sphere_id required. Validate activity.type ∈ sphere.allowed_activity_types → 400 otherwise (AC-13). Validate that for every linked categoryId, categories.sphere_id === activity.sphere_id → 400 otherwise (AC-14). Persist sphere_id on the activity row.
2. setCategories / category change. Re-run the category-sphere consistency check on every call; if the activity’s sphere_id is not consistent with at least one of the new categories → 400. Decision on OQ-1: yes, always re-validate activity.type against sphere.allowed_activity_types on setCategories. Rationale: if a super-admin shrinks allowed_activity_types, the next category change on an existing activity would otherwise silently leave it in an invalid state. Re-validating is cheap (one row lookup) and the only consistent rule. Flagged below as resolution of OQ-1.

D9 — Per-surface DTO visibility

The same logical sphere is exposed with different fields per surface. The contract patches in contracts/*.openapi.yaml reflect this directly:

Field	client	business	super-admin
id	yes	yes	yes
code	yes	yes	yes
name (full locale)	yes	yes	yes
icon	yes	yes	yes
targetApp	yes	yes	yes
allowedActivityTypes	yes	yes	yes
defaultActivityType	no	yes	yes
sortOrder	yes	yes	yes
createdAt	no	no	yes

defaultActivityType is operator-only metadata used by the business panel’s “create activity” wizard to pre-fill the type select; clients have no use for it. createdAt is plumbing data — only super-admin needs it for audit reasoning.

/api/client/spheres is public (no Bearer auth) — the response is non-sensitive platform metadata identical for every caller. This matches AC-15 (“No auth required”).

For categories and activities, the surface differences are:

Field	client	business	super-admin
categories.sphereId	yes (filterable)	yes	n/a
categories.companyId	no	yes (NULL = platform/system)	n/a
activities.sphereId	yes (filterable)	yes	n/a

companyId is an operator-context field; clients do not see it. The business surface’s “system category” check is companyId === null (no separate isSystem flag — see D2 note).

D10 — Audit logging is part of the same DB transaction

activities.sphere_audit_log Drizzle definition:

export const SphereAuditActionEnum = activitiesSchema.enum('sphere_audit_action', [
  'CREATE',
  'UPDATE',
  'DELETE',
]);

export const sphereAuditLog = activitiesSchema.table(
  'sphere_audit_log',
  {
    id: uuid('id').primaryKey().default(sql`gen_random_uuid()`),
    sphereId: uuid('sphere_id').references(() => spheres.id, { onDelete: 'set null' }),
    sphereCode: text('sphere_code').notNull(),
    // INTENTIONAL: NO `.references(...)` on actor_user_id — see B1
    // resolution paragraph below.
    actorUserId: uuid('actor_user_id').notNull(),
    action: SphereAuditActionEnum('action').notNull(),
    before: jsonb('before'),
    after: jsonb('after'),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => ({
    sphereIdCreatedIdx: index('sphere_audit_log_sphere_id_created_idx')
      .on(t.sphereId, t.createdAt.desc()),
    sphereCodeCreatedIdx: index('sphere_audit_log_sphere_code_created_idx')
      .on(t.sphereCode, t.createdAt.desc()), // for AC-28 lookups by code after sphere_id NULL'd out
  }),
);

Why no FK on actor_user_id (B1 resolution). Super-admin auth uses a SEPARATE Supabase project (SUPERADMIN_SUPABASE_URL); the superadmin-jwt strategy in apps/api/src/app/modules/super-admin-api/strategies/superadmin-jwt.strategy.ts does NOT sync into users.users. Only AdminJwtStrategy and ClientJwtStrategy go through auth-sync.service.ts. An FK from sphere_audit_log.actor_user_id to users.users(id) would fail on the FIRST super-admin write at runtime — there is no row to point at. We therefore store the raw Supabase super-admin uuid (the superadmin-jwt sub claim) as uuid NOT NULL with a column comment recording the rationale. This is analogous to how sphere_code is a snapshot text column rather than an FK target — both protect history beyond the lifetime of the referenced entity. The column comment in the migration draft documents this for any future schema reviewer.

The audit write is a db.transaction(async tx => { … }) wrapping both the sphere mutation and the audit insert. There is no separate SphereAuditService — keeping the audit insert at the call site of the mutation is the only way to guarantee atomicity without an event bus (which we explicitly do not want for v1; see Alternatives). The service exposes a private helper writeAuditRow(tx, action, before, after, actorUserId) to keep the three call sites tidy.

actor_user_id is the Supabase user id of the super-admin caller, extracted from the request via the new @ActiveSuperAdmin() param decorator introduced in D6 (B2 resolution).

Reads (GET /api/superadmin/spheres/:id/audit) first try sphere_id = :id; if zero rows match AND the path id corresponds to no existing sphere, the service falls back to looking up by sphere_code snapshot. This satisfies AC-28 (audit is queryable by old id even after hard-delete because the snapshot is preserved on the audit row).

D11 — Seed migration is one INSERT block appended to the generated DDL

-- Appended at the bottom of the drizzle-kit-generated migration.
-- Five seeded spheres. ON CONFLICT DO NOTHING keeps re-runs idempotent
-- (relevant for any environment where the migration is replayed).
INSERT INTO "activities"."spheres"
  ("code", "name", "icon", "target_app",
   "allowed_activity_types", "default_activity_type", "sort_order")
VALUES
  ('SPORT',    '{"uk":"Спорт","en":"Sport","ru":"Спорт","de":"Sport","fr":"Sport"}'::jsonb,
                NULL, 'GYM_APP',
                ARRAY['SLOT_BASED','MEMBERSHIP','SERVICE'], 'SLOT_BASED', 0),
  ('CINEMA',   '{"uk":"Кіно","en":"Cinema","ru":"Кино","de":"Kino","fr":"Cinéma"}'::jsonb,
                NULL, 'TICKETS_APP',
                ARRAY['MOVIE'], 'MOVIE', 1),
  ('SHOWS',    '{"uk":"Шоу","en":"Shows","ru":"Шоу","de":"Shows","fr":"Spectacles"}'::jsonb,
                NULL, 'TICKETS_APP',
                ARRAY['SHOW'], 'SHOW', 2),
  ('SERVICES', '{"uk":"Послуги","en":"Services","ru":"Услуги","de":"Dienste","fr":"Services"}'::jsonb,
                NULL, 'GYM_APP',
                ARRAY['SERVICE','SLOT_BASED'], 'SERVICE', 3),
  ('DINING',   '{"uk":"Їжа","en":"Dining","ru":"Еда","de":"Essen","fr":"Restauration"}'::jsonb,
                NULL, 'DINING_APP',
                ARRAY['DINING'], 'DINING', 4)
ON CONFLICT (code) DO NOTHING;

Note: MEMBERSHIP is in the SPORT allowed_activity_types set per the spec but is not currently a value of the activity_type Postgres enum (SHOW | MOVIE | SLOT_BASED | DINING | SERVICE). The allowed_activity_types column is text[] (NOT the enum) so this is intentional and forward-compatible — operators can pre-allow types that the rest of the system does not yet support. Activity creation still enforces the canonical ActivityTypeEnum at column level. Calling this out so it does not surprise the backend dev during code review.

Consequences

What this enables

• Mobile and web filter UI by sphere becomes a one-line query (WHERE sphere_id = …).
• White-label apps know exactly which spheres they should render (filter on targetApp).
• Subtree analytics (“how many bookings under category X and its descendants?”) become joinable in O(1) closure-table lookups.
• Operator accountability: every sphere mutation is recorded with actor and full before/after snapshot.

Risks

• Closure rebuild cost on subtree moves. A move of a 1000-node subtree rewrites ≈1000 × avg(parent_depth) closure rows. With a depth cap of 6 this is bounded at ~6000 rows per move — comfortable. The risk is realised only if depth cap is later raised.
• Audit table grows unbounded. No retention policy in v1. With ≤a few mutations per week (hand-curated platform metadata), the table will stay small for years. Flagged for a future archival ticket.
• default_activity_type constraint maintenance. The pair (allowed_activity_types, default_activity_type) must stay consistent — default_activity_type ∈ allowed_activity_types. The service enforces this at every PATCH; there is no DB-level CHECK because Postgres CHECK on array_position(arr, val) IS NOT NULL works but is awkward and not currently used elsewhere in the schema. If a buggy DTO bypass ever occurs, the system will permit an inconsistent row; the service-layer test suite covers the cases exhaustively.
• Hand-edited generated migration. Two modifications to the drizzle-kit output are required (the seed INSERT and, possibly, an expression-index swap). Both are additive and isolated to one file; documented in the PR description so reviewers know the “auto-generated only” rule has bounded exceptions.
• Concurrency tests required for D5. The SELECT … FOR UPDATE strategy is correct only if integration tests exercise it under realistic interleaving (AC-19). A weaker test that serialises requests would let a regression slip in. Per C1 (D7 rule 8) the concurrency test set MUST include the symmetric reparent-pair case, not just sibling-subtree moves.

Acknowledgements (non-blocking review concerns)

• C3 — PATCH default-type validation parity. The PATCH path (update) re-validates default_activity_type ∈ allowed_activity_types whenever either field appears in the body, recomputing the pair from supplied + existing values (the contract already documents this). Same rule as POST.
• C4 — 400 surface is broader than code_immutable. The PATCH 400 response covers generic body-shape validation, the immutable code, and the default_type_invalid case; the existing ErrorResponse envelope with its enumerated error codes (now documented in the schema description) covers all cases without a dedicated DTO per error.
• C5 — Audit retention. Each row is roughly 600 B (two JSONB snapshots of a small sphere row, plus uuid/timestamp metadata); with low write volume there is no TOAST or index-bloat concern. No retention policy is set for v1; flagged for a future archival ticket.
• C6 — Privacy of actor_user_id. Only super-admins can hit GET /api/superadmin/spheres/:id/audit. The raw super-admin Supabase uuid is never exposed on /api/client or /api/business. No broader exposure planned.
• C7 — Super-admin module placement (two-pattern coexistence). The new sphere super-admin controller sits inside the apps-tree module (apps/api/src/app/modules/super-admin-api/controllers/) while the service lives in libs/features/spheres/. This matches the existing super-admin code pattern. The client and business surfaces use the per-surface-lib-module pattern (see D6 layout). We accept the asymmetry for this ticket — refactoring the existing super-admin controllers into the lib pattern is out of scope and would expand the diff well beyond what’s needed to ship the feature. If a third super-admin feature lands and the asymmetry starts to cost reviewers time, that is the trigger to consolidate.
• C8 — Empty GET /audit response. When a sphere has no mutations and the path id is live, the endpoint returns 200 [] (documented behaviour). Only when the id matches no live sphere AND no historical sphere_code snapshot does the endpoint return 404 (as documented in the contract).
• C9 — Closure benchmark fixture shape. AC-18’s “10k categories” fixture is pinned to a balanced tree with fan-out ~10 and depth 4 (10 + 100 + 1 000 + 10 000 ≈ 11 110 nodes). This shape stresses closure size linearly with depth and exercises the descendant_id index path; documented here so the integration test author doesn’t pick a worst-case-skewed tree by accident.
• C10 — setCategories sphere lookup. The sphere row is fetched once per request and cached on the request scope (e.g. via RequestScope or a Map on the service for the request lifetime) to avoid a roundtrip per linked category. Avoids N+1 on the validation path.

Considered alternatives

A1 — Adjacency list with recursive CTE (rejected)

Status quo. Subtree queries use WITH RECURSIVE. Pros: zero schema change, no closure-maintenance code. Cons: query plans degrade with depth, planner cannot reason about cardinalities, and we’re locked into recursive CTEs everywhere we want subtree filtering. The spec explicitly calls out non-scalability as a goal of this ticket, and benchmark AC-18 mandates < 30ms on 10k categories — recursive CTEs need >100ms on that fixture in our experience.

A2 — DB triggers for closure maintenance (rejected)

Pros: closure stays consistent regardless of what application code does (defence in depth). Cons: harder to test in isolation, harder to keep in sync with TypeScript service code, harder to debug when a trigger fires unexpectedly. Mistakes in trigger logic produce silent DB-side data corruption. We explicitly want closure logic to live next to its callers and be covered by integration tests rather than DB fixtures.

A3 — LISTEN / NOTIFY for audit logging (rejected)

Pros: decouples audit from the mutation transaction. Cons: requires a listener process for v1, and the entire selling point of the audit log is operator accountability — losing audit rows because a listener crashed defeats the feature. Atomic transactional insert is the only correct shape for v1. If audit volume ever explodes, we’ll move to a separate audit table partition or a CDC pipeline; for the foreseeable volume (a few writes per week) the inline insert is simpler and strictly more correct.

A4 — Separate SphereAuditService event-bus listener (rejected)

Same reasoning as A3 — adds an at-least-once-delivery problem we don’t have today.

A5 — sphere_code as primary key (rejected)

Pros: human-readable FKs, no need to look up id when configuring app. Cons: every category and activity gains a denormalized text PK; renames become impossible without cascading updates across millions of rows. Keeping id (uuid) as the FK target and code as a UNIQUE business key is the standard pattern (matches users.id vs users.email). Operator config files reference code for human readability; inter-table FKs reference id for stability.

A6 — Closure depth column omitted (rejected)

Some closure-table designs skip depth and derive it on read. We keep depth because (a) the depth cap (D7 rule 6) needs an O(1) lookup, and (b) “what is my immediate parent?” queries need depth = 1 — adding a synthetic column saves the join and the storage cost is trivial.

A7 — Per-surface SpheresService triplets (rejected)

See D6 reasoning. Read paths are identical across surfaces; only DTO shape differs. One service, three controllers.

Surface impact

Contract	Endpoints added/changed	Schemas added/changed
contracts/client.openapi.yaml	+ GET /spheres (public). + sphereId query param on the existing (or new-stub) /categories and /activities paths.	+ SphereClientDto, ActivityTypeCode, SphereTargetAppCode. The categories/activities client endpoints are added as stubs because no full client read paths for those surfaces exist in the contract today (mirrors how favorite-activities punted on activity-detail).
contracts/business.openapi.yaml	+ GET /spheres. + sphereId query param + sphereId/companyId response fields on /categories. + sphereId query param + sphereId response field on /activities.	+ SphereAdminDto. The category/activity endpoints are also added as stubs (existing contract doesn’t declare them yet).
contracts/super-admin.openapi.yaml	First content for an empty (paths: {}) but already-mounted module. + GET /spheres, POST /spheres, GET /spheres/:id, PATCH /spheres/:id, DELETE /spheres/:id, GET /spheres/:id/audit.	+ SphereSuperAdminDto, CreateSphereDto, UpdateSphereDto (omits code from properties — see B3 below), SphereAuditEntryDto, SphereDeleteConflictDto, SphereTypeInUseConflictDto, ErrorResponse (with deterministic error codes), BearerAuth security scheme.

Field-level differences (and why) are recorded in the table at D9.

Backend module placement

• New shared library: libs/features/spheres/ — see D6 layout. Three per-surface modules + one core service.
• Existing library extension: libs/features/activities/ — extend CategoriesService (D7) and ActivitiesService (D8) with the new validation rules and closure-maintenance code. New dto/ files: update-create-category.dto.ts gains sphereId and companyId; category.response.dto.ts gains the same. activities DTOs gain sphereId. (No isSystem field — see D2.)
• Schema additions: libs/shared/data-access-db/src/lib/schema/activities.schema.ts — append SphereTargetAppEnum, SphereAuditActionEnum, spheres, categoryClosure, sphereAuditLog, plus the column additions on categories and activities (D2, D3). No new schema file needed — these are activity-domain tables.
• App module wiring: apps/api/src/app/app.module.ts already imports SuperAdminModule, AdminApiModule, ClientApiModule and registers routing prefixes. The three per-surface spheres modules are imported by their respective surface modules — no new top-level wiring.
• Swagger split: apps/api/src/main.ts — append the spheres modules to the include: […] arrays for each SwaggerModule.createDocument call (already done for SuperAdminModule; just make sure the new spheres-superadmin module is reachable from SuperAdminModule via imports).

Frontend implications

tktspace-business — read-only sphere display + create-category sphere picker

In scope of this ticket only (per spec non-goals):

• Regenerate API client: npm run generate:api after the business contract is patched. Surfaces the new SphereAdminDto and the sphereId/companyId fields on category/activity responses.

Out of scope (separate ticket per spec): sphere selector on category create/edit forms, sphere filter UI on activity list, sphere badge on category cards. The data is wire-available after this ticket; the UI work is deferred.

tktspace-web, tktspace-mobile-app, tktspace-landing — none

Per spec non-goals: no mobile or web filter UI in this ticket. The mobile and web apps may regenerate their clients post-merge to pull in the new /api/client/spheres types for use in the follow-up ticket, but that is opportunistic — nothing in this ticket forces it.

Mobile implications

None for v1. No apps/gym_app, apps/tickets_app changes; no shared packages/* changes. Sphere-filtering UI on mobile is a separate ticket (OQ-3 confirmation).

Migration outline

Per the team rule (feedback_drizzle_migrations.md — migrations are generated, never hand-written), the canonical workflow is:

1. Edit Drizzle TS schema at libs/shared/data-access-db/src/lib/schema/activities.schema.ts — add the new enums, tables, columns, indexes from D1–D4 and D10.
2. Generate the migration:

   npx drizzle-kit generate --config=drizzle.config.ts \
     --name=activity_spheres

   Output lands in libs/shared/data-access-db/migrations/{NNNN}_activity_spheres.sql.
3. Append three blocks to the generated file:
   • The seed INSERT for the 5 spheres (D11 verbatim).
   • If drizzle-kit emitted a plain UNIQUE(title, parent_id, sphere_id) instead of a LOWER(title) expression index, replace that block with the explicit CREATE UNIQUE INDEX … (LOWER("title"), …) statement (D2 workaround).
   • The COMMENT ON COLUMN sphere_audit_log.actor_user_id IS '…' statement that explains why the column has no FK (drizzle-kit does not emit column comments — see B1 resolution / D10).
4. Read the generated SQL and verify against the expected diff in _workflow/drafts/migration-activity-spheres.sql (preview file, produced by this ADR).
5. Run the migration-safety-check skill — even though categories/activities are empty in production, the ADR’s “two manual edits to the generated file” rule needs an extra reviewer pass.
6. Apply locally and test: npx drizzle-kit migrate && npx nx test data-access-db && npx nx test api.

The expected SQL diff is captured in _workflow/drafts/migration-activity-spheres.sql. That file is a preview only and never lands in the backend repo.

Rollout plan

• No feature flag — the data model change is non-additive at the table level (new NOT NULL columns) and the production data is empty.
• Single deploy: backend ships with new schema, contracts are merged before backend deploy, business panel regenerates its client and displays the new fields immediately (no UI work in scope, so no visible change).
• Operator onboarding: super-admin operators receive the Swagger UI URL (http://localhost:5005/api/superadmin/docs locally; production equivalent) and a one-page runbook describing the sphere CRUD endpoints. No frontend yet (per spec non-goal).

Open questions

• OQ-1 (resolved in D8 above): on setCategories, always re-validate activity.type ∈ sphere.allowed_activity_types. Cheap, consistent, and prevents drift if allowed_activity_types later shrinks.
• OQ-2 (deferred): GET /api/client/categories without ?sphereId is left as-is — returns all rows. Filtering is the caller’s job. The spec recommends this; the ADR does not introduce a behaviour change. A separate discovery ticket can revisit if mobile UX feedback demands a default filter.
• OQ-3 (confirmed): Mobile and web filter UI is out of scope of this ticket — no Flutter or Angular work in this design. Follow-up ticket(s) will pick it up after the data layer ships.

There are no remaining unresolved blockers.