ADR: Sphere Column Type-Safety — text[] → activity_type[] Enum

ADR: Sphere Column Type-Safety — text[] → activity_type[] Enum

Status

PROPOSED

AC ↔ D# mapping

AC	Primary decisions	Notes
AC-1	D1 (schema definition update), D2 (ALTER COLUMN SET DATA TYPE USING)	Drizzle schema + drizzle-emitted migration body with hand-edited USING cast.
AC-2	D3 (pre-flight DO block)	RAISE on any non-enum value in either column; full rollback.
AC-3	D2 (PG enum DB constraint)	Native enum type rejects garbage at DB level (22P02).
AC-4	D4 (DTO decorator update — 3 surfaces)	enumName: ‘ActivityType’ on all three sphere DTOs.
AC-5	D5 (no behavior regression — wire format unchanged)	JSON unchanged; existing e2e tests must continue to pass unchanged.
AC-6	D4 + D6 (business client regen)	post-regen patch-spheres-swagger.js consistency check.
AC-7	D7 (4 e2e cases)	happy / pre-flight RAISE / 22P02 / rollback safety.

---

Decisions

D1 — Drizzle schema definitions update first

Decision: edit libs/shared/data-access-db/src/lib/schema/activities.schema.ts:

defaultActivityType: ActivityTypeEnum('default_activity_type').notNull(),
allowedActivityTypes: ActivityTypeEnum('allowed_activity_types').array().notNull(),

Then run pnpm db:generate to emit a migration scaffold. drizzle-kit will detect the type change and emit ALTER COLUMN ... SET DATA TYPE for both columns.

Rationale:

• Drizzle schema is the source of truth — TypeScript inference downstream picks up the enum union type automatically.
• pnpm db:generate produces journal entry + snapshot. Hand-editing the migration SQL body is necessary (see D2) but the file itself is drizzle-emitted (memory rule satisfied).

D2 — ALTER COLUMN SET DATA TYPE with explicit USING cast (hand-edit only if drizzle omits it)

Decision: run pnpm db:generate and inspect the emitted SQL before assuming a hand-edit is needed:

• Scalar text → enum (default_activity_type): repo evidence shows drizzle-kit DOES emit the USING cast for this case — migration 0021_lovely_shinko_yamashiro.sql:6 contains ALTER COLUMN "type" SET DATA TYPE "activities"."activity_type" USING "type"::"activities"."activity_type" (drizzle-generated). Expect the same emission here; no hand-edit.
• Array text[] → enum[] (allowed_activity_types): unverified in this repo (first array-typed enum conversion). If the emitted ALTER COLUMN allowed_activity_types SET DATA TYPE activities.activity_type[] lacks USING allowed_activity_types::activities.activity_type[], hand-add it.
• Pre-flight DO $$ block (D3): always hand-prepended — drizzle-kit never emits data guards.

Any hand-edit gets a prose block comment at the top of the file in the 0043_pg_trgm_search_indexes.sql style: a multi-line -- comment explaining WHAT was hand-edited, WHY drizzle-kit cannot emit it, and what happens on subsequent pnpm db:generate re-runs (must be a stable no-op because the schema TS already declares the enum-typed columns).

Rationale:

• Postgres requires explicit USING when changing column type between non-implicitly-castable types. Whether drizzle-kit 0.31.x emits it for the array case is the only open mechanical question — resolved by inspection at generate time, not by assumption.
• Existing precedent for hand-edited migration content in this repo: 0043_pg_trgm_search_indexes.sql (extension prepend, prose comment block) and 0021 (proves scalar USING emission). Same hand-edit discipline — document the deviation in the SQL itself.
• The cast text[] → activity_type[] is lossless when all values are in the enum domain. The pre-flight DO block (D3) guarantees this.

Alternative rejected: drizzle-kit generate --custom produces an empty file without updating the snapshot — the next db:generate would re-emit the ALTER and create drift. The primary path (schema update → generate → hand-edit) is the only snapshot-safe route.

D3 — Pre-flight DO $$ block raises on any garbage

Decision: the migration starts with a DO $$ block that counts rows where default_activity_type is not in the enum domain AND rows where any element of allowed_activity_types is not in the enum domain. If either count > 0, aggregate the offending values into a string and RAISE EXCEPTION with a clear message instructing the operator to run cleanup first.

Rationale:

• Without the pre-flight, the ALTER COLUMN … USING cast would RAISE mid-conversion with a less-informative Postgres error (“invalid input value for enum”). Our DO block surfaces the count and the specific offending values, making operator triage cheaper.
• Fail-loud is cheaper than silent data corruption.
• Pre-flight matches the pattern from P0 #1 (default_activity_type=‘MEMBERSHIP’ check) and P3 #8 (SERVICES sphere row existence check). Operators learn one convention.

Operator pre-deploy check: before merging, run on prod:

SELECT count(*) FROM activities.spheres
WHERE default_activity_type NOT IN ('SHOW','MOVIE','SLOT_BASED','DINING','SERVICE')
   OR EXISTS (SELECT 1 FROM unnest(allowed_activity_types) v
              WHERE v NOT IN ('SHOW','MOVIE','SLOT_BASED','DINING','SERVICE'));

Result must be 0. If non-zero, escalate.

D4 — DTO @ApiProperty decorators: exactly TWO declaration sites

Decision: the response-DTO hierarchy uses inheritance (SphereSuperAdminDto extends SphereAdminDto extends SphereClientDto — libs/features/spheres/src/lib/dto/sphere.dto.ts), so all three surfaces are covered by updating two declaration sites:

1. SphereClientDto.allowedActivityTypes (sphere.dto.ts:22, currently @ApiProperty({ type: [String] })) → @ApiProperty({ enum: ActivityTypeEnum.enumValues, enumName: 'ActivityType', isArray: true, example: ['DINING', 'SERVICE'] }) — inherited by Admin and SuperAdmin.
2. SphereAdminDto.defaultActivityType (sphere.dto.ts:28, currently bare @ApiProperty()) → same decorator without isArray — inherited by SuperAdmin. Client omits the field per D9 of activity-spheres ADR (verified: SphereClientDto has no defaultActivityType).

Mutation DTOs (CreateSphereDto, UpdateSphereDto) need NO changes — P0 #1 already landed enumName: 'ActivityType' + @IsIn(ActivityTypeEnum.enumValues) on both (sphere.dto.ts:58-77 and 107-127). Note on ActivityType schema pre-existence (attribution corrected by Phase A critic): components.schemas.ActivityType is present in all three contract YAMLs primarily because the activities-feature DTOs (libs/features/activities/src/lib/dto/find-activities-client.dto.ts:79, activity.response.dto.ts:97) already carry enumName: 'ActivityType' on activities’ own type fields — those DTOs are exposed on the client + business surfaces. (The super-admin-only mutation DTOs contribute on the superadmin surface.) The pre-existence of the named schema on all three surfaces is what makes this ticket’s contract diff $ref wiring only.

Rationale:

• enumName: 'ActivityType' instructs @nestjs/swagger to emit a top-level components.schemas.ActivityType enum and $ref it. Without it, the enum inlines at every reference site, breaking named-enum codegen in clients.
• CLAUDE.md OpenAPI authoring checklist explicitly requires enumName for every named enum — this fixes the remaining gap on the sphere response DTOs (mutation DTOs were fixed in P0 #1).

D5 — No wire-format behavior regression

Decision: the JSON wire format is unchanged. Existing clients send enum values as strings; this remains true after migration. Existing e2e tests for spheres run unchanged; verify GREEN with no test rewrites needed.

Rationale:

• The enum is a type-level tightening on both sides (TypeScript union, Dart enum) — no behavior change at the protocol layer.
• Backward compatibility: old client builds in the wild (web/mobile/business) continue to work. New regenerations get a stricter named type.

D6 — Business client regen + patch-script consistency check

Decision: after backend migration + sync:contracts, run npm run generate:api in tktspace-business. Then verify:

1. grep -rn MEMBERSHIP client/src/app/core/api/models/ tools/gen/ returns 0 (smoke from P0 #1 work).
2. tools/gen/patch-spheres-swagger.js:55 hardcoded enum (post-P0 #1: ['SHOW','MOVIE','SLOT_BASED','DINING','SERVICE']) matches the regenerated contract — if anything has drifted, update both the patch script and tools/gen/swagger-api.json.

Rationale:

• The patch-spheres-swagger.js workaround from P0 #1 still exists. Until it’s retired in a future cleanup ticket, every contract regen must verify it’s in sync with the live contract.
• The named ActivityType schema will now appear in the regenerated swagger-api.json automatically. If the patch script’s hardcoded ActivityTypeCode enum is still present and consistent, the codegen output is unaffected.

D7 — E2E test suite: 4 cases

Decision: new file apps/api-e2e/src/spheres/sphere-type-safety-0046.spec.ts (migration head verified at 0045; this ticket lands 0046_sphere_columns_to_activity_type_enum.sql). Covers:

1. Happy path — apply migration on clean data, assert information_schema.columns.udt_name = 'activity_type' for both columns.
2. Pre-flight RAISE — inject default_activity_type='BANANA' row, run migration, assert RAISE matching /% bad default_activity_type.*Offending values: BANANA/, assert rollback (columns still text).
3. 22P02 rejection — post-migration, raw INSERT with 'BANANA' → assert Postgres error code 22P02.
4. Rollback safety — down-migration (text-cast) preserves data exactly.

Rationale: mirrors the e2e patterns from sphere-migration-0044.spec.ts (P0 #1) and sphere-rebucket-0045.spec.ts (P3 #8). Direct pg client + apply-migration helper, no test rewrites needed.

---

Out of scope (explicitly deferred)

• allowedPaymentMethods text[] on activities — same shape problem, different domain (payment method enum, not activity type). Separate ticket.
• Adding new activity_type enum values — product decision, separate per-feature ADR.
• activity_categories / categories.sphere_id consistency — P3 #20.
• Retiring tools/gen/patch-spheres-swagger.js — workaround from earlier sphere endpoint rollout; can retire once business.openapi.yaml carries proper sphere schemas from a clean codegen. Out of scope here.
• CI seed-lint guard — DB enum constraint replaces it.

---

Risks (post-mitigation)

• Hand-edited migration drifts on next pnpm db:generate — only relevant if the array USING cast needs hand-adding (D2). Mitigation: prose comment block in the 0043 style + the committed snapshot reflects the post-migration schema state, so re-runs are a no-op. Future migrations of these columns are rare (enum value addition only).
• Pre-flight false-positive blocks legitimate deploys — only fires if garbage exists. Operator runs the manual SELECT pre-deploy (D3). Cleanup follows P0 #1 / P3 #8 patterns.
• Backward-compat regression — none expected. Phase B verifies all existing sphere e2e tests still GREEN.

---

Verification

1. Edit activities.schema.ts per D1 (current state verified: lines 84-85, both text(...); ActivityTypeEnum pgEnum at lines 39-45, 5 values).
2. Run pnpm db:generate. Inspect emitted SQL per D2 — scalar USING expected present (0021 precedent); array USING may be missing.
3. Hand-edit the migration: prepend the pre-flight DO block; add the array USING clause only if absent; prose comment block in 0043 style.
4. Run e2e: pnpm exec jest --testPathPattern=sphere-type-safety — all 4 cases pass.
5. Run existing sphere e2e: pnpm exec jest --testPathPattern=spheres/ — no regression.
6. pnpm run docker:up && pnpm db:migrate → smoke: \d+ activities.spheres shows enum types.
7. pnpm run sync:contracts → all three YAMLs emit ActivityType named schema.
8. In tktspace-business: npm run generate:api, verify grep MEMBERSHIP returns 0.
9. Manual 22P02 verification: raw SQL INSERT with bad value → Postgres error.

STATUS: READY_FOR_REVIEW