Skip to content

CoreAPI Consolidation Plan

Living document — update as phases complete. Last updated: 2026-09-09 · Current phase: 5 (database merge) · Gate: tests/smoke_test.go

Goal: collapse CoreAPI's two half-separate applications into one app, one database, aligned with widely-accepted Go practice, without disrupting auth/Zitadel. Companion to standardization-goals.md (the why); this file is the how and the running status.

Decisions taken

Question Decision Date
Split into two Fiber apps, or merge? Merge. One app, one DB. feature/ticketing-fiber-app-split is abandoned — it points the wrong way. 2026-09-08
domain/ under internal/? No. Changes every import path for a cosmetic gain. Leave domain/ at root. 2026-09-09
Chase a canonical Go layout? No. Stay close to current structure; fix identified pitfalls instead. There is no official Go layout — golang-standards/project-layout is not endorsed by the Go team and its pkg/ convention is widely criticised. 2026-09-09
Response envelope Unify on {success, data, error} — one move, then fix frontends. Sequenced after restructuring: Phase 1–5 are invisible to clients, so doing the visible break last avoids "was it the restructure or the envelope?" 2026-09-09
CDN clients Group as pkg/cdn/{streamer,cproxy,colorbar,elb} rather than scattering. Also fixes the non-idiomatic capitalised package names. 2026-09-09
Tests Light, HTTP-level smoke tests in tests/ at repo root — not scattered _test.go. ~15 tests. 2026-09-09
Merged DB layout Keep johorzoo as a schema inside the coreapi database. One connection, one transaction scope, zero renames, tag/tags stays unambiguous. 2026-09-08

Constraints discovered (these shape the work)

  • Zero table-name collisions between johorzoo (28) and coreapi (113). Only near-miss: johorzoo.tag ↔ coreapi.tags. The merge is mechanically safe.
  • DisableForeignKeyConstraintWhenMigrating: true is set in pkg/db/db.go:70 for both DB and ZooDB. Consequence for Phase 4: GORM will not recreate the 17 SQL-defined FKs. Either keep FKs in SQL, or add foreignKey/references tags and flip that flag — decide before starting.
  • The ticketing schema has never been GORM-managed. The legacy repo used Migrator().CreateTable() (not AutoMigrate), covering only 16 of 25 models, fresh-DB-only. General was never in that list. So Phase 4 is building a migration path, not restoring one.
  • 28 ticketing models declare TableName(); several carry MySQL-era tags (bigint unsigned AUTO_INCREMENT, datetime) that GORM renders imperfectly against Postgres.
  • Zero tests exist in CoreAPI today (0 _test.go files).
  • Config is scattered: 100 raw os.Getenv calls across 36 files; only 6 of 14 domains have the documented config.go.
  • johorzoo.schema_migrations exists but is empty — no migration history to preserve.

Phase 0 — Baseline · ✅ DONE (2 items carried forward)

  • Receive split schema/data files with FKs — ticketing-schema.sql + ticketing-data.sql from playtelly-iac
  • Reconcile the two divergent seed copies. Local had drifted 3 months behind staging (25 tables/57 general columns vs 28/65). Local now holds byte-identical copies under Quickstart/compose/infrastructure/postgres-init/ticketing/, loaded by 05-ticketing-init.sh. Old 05-tkt-migrate.sql deleted.
  • Verify DisableForeignKeyConstraintWhenMigrating vs the new FKs — already true; see Constraints above
  • Local rebuilt from scratch and verified end to end
  • CARRIED FORWARD: replace production PII with synthetic seed — ticketing-data.sql holds 2,367 real citizens (name, email, phone, NRIC) and 4,867 orders, now in a third git location. See Risks.
  • CARRIED FORWARD: confirm with Nucha whether ticket_group_integration_config exists in production (it seeds 3 rows here, which suggests yes)

Result after rebuild:

Before After
johorzoo tables 25 28
FK constraints 0 17
general columns 57 65
GET /api/ticketGroups/ticketProfile?ticketGroupId=5 500 200
GET /api/ticketGroups/ticketAvailability record not found 200, live POS slots
Manual SQL needed 2 tables by hand none

Storefront product page renders fully: service types (Bot Tour RM18, River Cruise RM22) and live POS departure times.

Upstream bugs found (report to Chayanon — staging init would fail from scratch too)

  1. ticketing-schema.sql:2247 — CREATE TABLE IF NOT EXISTS ticket_group_integration_config is the only 1 of 28 not johorzoo.-qualified. With the pg_dump preamble at :17 blanking search_path, it fails with "no schema has been selected to create in". Its backfill block too. Worked around in 05-ticketing-init.sh via sed re-establishing search_path — no-op once fixed upstream.
  2. ~~Data file COPY ordering~~ — FIXED upstream by f73cb84 "Fix seed data ordering", which moved ticket_group ahead of its dependents. The session_replication_role deferral is no longer needed and was removed. Note: down -v alone does not fix this class of problem — ordering does.
  3. ticketing-schema.sql:2345 comment says ticket_operating_exception "does not exist in dev" and skips its FK (12 of 13). That's now stale — the table exists locally, so the 13th FK could be enabled.

Phase 1 — Structural tidy · ✅ DONE (1 item deferred)

Zero behaviour change. Gate passed: go build ./... clean, gofmt clean on all touched files, container healthy, every endpoint class verified.

  • Split main.go (415 lines → 40) into: models.go (162, registerModels()), infra.go (141, initInfra()), routes.go (128, buildApp() *fiber.App). Kept as root-level files in package main rather than an app/ subpackage — same readability win, zero import churn, no exported-symbol surface to design. defer natsclient.Close() was hoisted into main() so it still fires at process exit, not when initInfra() returns.
  • setup.go → ticketing.go (setupTicketingService, body unchanged)
    • schedulers.go (the 5 run*Scheduler helpers, with the multi-replica warning documented at the top)
  • bootstrap/ → pkg/seed/platform.go; bootstrap.Run → seed.Platform (renamed to avoid colliding with the existing seed.Run). No new dependency direction: pkg/seed already imports domain/* in 20 of 25 files.
  • internal/routes.go folded into routes.go (12-line function inlined)
  • scratch/ deleted and unwired
  • CDN clients grouped and de-capitalised: pkg/{CProxyClient,ColorbarClient,ELBClient,StreamerClient} → pkg/cdn/{cproxy,colorbar,elb,streamer}, packages renamed to match their directories, all call sites updated.
  • DEFERRED to Phase 2: delete the duplicate General in internal/db/models/general.go. It is not dead — imported by domain/tenancy/routes/wire.go and pkg/email/email.go:150 (NewEmailService(*models.General)). The duplicate exists specifically so pkg/email doesn't import domain/tenancy, which is a legitimate dependency-direction reason. The correct fix is for pkg/email to declare its own narrow config struct and have the caller map into it — an API change, not a mechanical move, so it belongs with config work.

pkg/ count: 33 → 30.

Verified after Phase 1

Endpoint Result
/health 200
/api/v1/identity/users/me 200
/api/v1/tenancy/organizations 200
/api/v1/media 200
/api/ticketGroups (ticketing) 200
/api/settings/faq (ticketing) 201
/api/v1/internal/... (PSK-gated) 401 (correct)
:13000 storefront 200
:4000/biz-console/ 200
POS ticketAvailability 200, live slots
Ticketing schedulers started (8 log lines)

Phase 2 — Config standardisation · 🔄 2a DONE, 2b pending

2a — Config honesty · ✅ DONE (ab4222a)

  • pkg/email declares its own SMTPConfig. It took *internal/db/models.General — a 57-field struct — to read 9 fields. That forced a duplicate of domain/tenancy.General to exist under internal/ (so pkg wouldn't import domain) plus a ~60-line field-by-field mapper in wire.go to feed the call. The duplicate had also already drifted: 57 fields vs tenancy's 63. Now consumer-defined: 9-field config, 9-field mapper, pkg/email imports neither domain nor internal. wire.go 209 → 161 lines.
  • internal/ deleted outright — general.go was its last file after Phase 1 inlined internal/routes.go.
  • Removed the dead AutoMigrate / CreateConstraints flags. Read from env into pkg/config, then used in exactly two places, both log.Println, under a comment claiming they "control schema migration on the ticketing DB". Nothing in CoreAPI ever passed ticketing models to a migrator. Deleted rather than left logging "Auto-migration is enabled" while doing nothing. Phase 4 adds a real one. Bonus finding: staging's coreapi configmap sets TICKETING_AUTO_MIGRATE while the code read AUTO_MIGRATE — misnamed and inert.

Net −116 lines. Smoke suite 10/10 on a force-recreated container.

2b — env defaults + inventory · ✅ DONE (5b6b873)

82 distinct vars, 107 read sites, 38 files. Three had inconsistent fallbacks depending on which file read them — so unset meant components silently disagreed:

var was now
AUTHAPI_BASE_URL pkg/config → http://authapi:3002; pkg/jwt/rs256.go + pkg/authapi/client.go → "" aligned
MINIO_BUCKET domain/media → playtelly; pkg/storage + pkg/seed/media → "" aligned
CORE_API_PORT main.go → none (addr became ":"); pkg/config → 8080, used by nothing both 3000
  • config.WarnInsecureDefaults (called from initInfra) logs a SECURITY: line when either committed signing-secret default is in effect — TICKETING_JWT_SECRET_KEY / CONCIERGE_GUEST_JWT_SECRET. Warning not fatal on purpose; see Risks.
  • Generated coreapi-env-vars.md — all 82 vars, defaults, read sites, grouped by prefix
  • Deferred: collapse remaining multi-site reads into domain config structs (MEDIA_PROXY_BASE_URL ×7, BANNER_STORAGE_PATH ×6, storage paths). Mechanical; better reviewed alone.

Phase 4 — Ticketing schema → GORM · 🔄 4a DONE, 4b needs a decision

4a — make the models migratable · ✅ DONE (af999a2)

19 of 26 models could not migrate to Postgres at all. Two MySQL-only constructs inherited from when this was a MySQL app:

type:bigint unsigned AUTO_INCREMENT  ->  syntax error at "unsigned"
type:datetime                        ->  type "datetime" does not exist

96 tags fixed across 10 files. type:bigserial does not work as a replacement — it is a CREATE TABLE pseudo-type and fails in ALTER COLUMN TYPE; dropping the tag and letting GORM infer from uint+primaryKey is correct. Also fixed a malformed tag (bigint unsigned:int, parsed as an unknown key and silently ignored).

These tags only affect migration — GORM never consults them when querying — so runtime is unchanged. Result: 26/26 migrate cleanly.

Adds cmd/schema-diff, the harness that established this. Phase 5 needs the same check after the merge.

Five tables have duplicate model definitions. Only one genuinely differs: identity.OrderTicketGroup lacks program_cd; commerce's matches the DB. The rest are equivalent. config and schema_migrations have no model at all.

4b — reconcile discrepancies · ✅ DONE (08a349d)

Database treated as source of truth (it is a production snapshot; the structs are what the code believes).

  • nullability (20) — 16 dropped a spurious not null, 4 gained a missing one
  • int width (4) — advertising_ticket.placement, report.schedule_* pinned to type:integer (uint had rendered bigint)
  • varchar length (1) — report_attachment.attachment_path 510 → 255
  • missing fields (2) — general.jp_api_key_2 / jp_ag_token_2 added; CoreAPI previously could not read the second JohorPay key pair at all

Result: 0 missing, 0 extra, 0 type/nullability differences.

The 6 timestamps · ✅ APPLIED locally (ecd897a)

migration/2026-09-09_ticketing_timestamptz.sql — applied to local dev. Not yet run on staging or prod.

Judgment on intent: there was none. pg_dump never emits bare TIMESTAMP, so every dump-derived table is timestamptz; the 3 outliers are exactly the hand-written blocks, and Postgres silently defaults bare TIMESTAMP to without time zone. The intent is written in the other 25 tables.

Corroborated empirically before applying: all 8 rows in ticket_variant_override were created within 11 minutes of each other, at 03:37–03:48. Under the UTC reading that is 11:37–11:48 MYT — an admin session in normal working hours. Under a local-time reading it would be ~3:40 AM.

Result: 6 columns converted, instants preserved exactly (03:37:40.962023 → 03:37:40.962023+00, rendering as 11:37+08), 8 rows intact, 0 remaining without time zone columns.

25 of 28 tables use timestamptz; three do not, and those three are exactly the hand-written DDL blocks in the seed (pg_dump emits with time zone; a human typing TIMESTAMP gets without). The seed is internally inconsistent, so matching it would cement an accident.

PHASE 4 GATE MET — GORM's output now diffs to 0 differences, 0 missing columns, 0 extra columns against the live schema. Smoke suite 10/10.

The explicit AT TIME ZONE 'UTC' is load-bearing — without it, run from a +08 workstation, every value shifts 8 hours. Migration is idempotent.

⚠️ Fixes an existing database only; down -v reintroduces it. Durable fix is upstream.

Phase 5 — Merge the databases · 🔄 data done locally, code pending

5a — the gate · ✅ DONE (d16e781)

tests/dbstate_test.go. The HTTP smoke suite proves routes respond; it cannot prove data survived a migration, because a partially-restored dump still serves 200s from the rows that made it. Four assertion families:

Assertion Catches
Row counts per table, vs a captured baseline rows that never arrived
md5 content checksums, PK-ordered a shifted timestamp, a truncated varchar, a NULLed field — all invisible to a count
FK orphan sweep, discovered from pg_constraint (17 FKs) restoring tables in the wrong order with constraints deferred
No timestamp without time zone anywhere in the schema a down -v reintroducing the Phase 4 tz bug

Why the same tests work on both sides of the merge: every query is schema-qualified johorzoo.*. Today that resolves inside the separate johorzoo database; after the merge it resolves as a schema inside coreapi. Verifying the merge is a DSN change and nothing else.

Two details that would otherwise cause false failures: the session is pinned to UTC before checksumming (row-to-text rendering of timestamptz is session-dependent, so a +08 capture would never match a UTC verify), and tables with no primary key are counted but not checksummed since they have no stable row order.

Baseline: 28 tables / 47,799 rows, captured after the timestamptz migration. Capture is gated on CAPTURE_BASELINE=1 so a stray go test ./... cannot overwrite the record it exists to verify against.

Verified the assertions fail when they should, not merely that they pass: mutating one tag_name was caught by the checksum while the row count held; an orphan row and a bare-timestamp column were both caught in a throwaway database.

# before — record the truth
SMOKE_DB_DSN='postgres://…/johorzoo?sslmode=disable' CAPTURE_BASELINE=1 \
  go test ./tests/... -run TestCaptureDBBaseline -v -count=1

# after — same tests, new DSN
SMOKE_DB_DSN='postgres://…/coreapi?sslmode=disable' \
  go test ./tests/... -run 'TestRowCounts|TestContentChecksums|TestNoOrphaned|TestTimestamp' -count=1

5b — data migration · ✅ APPLIED locally (55bbf3e)

migration/2026-09-09_merge_johorzoo_into_coreapi.sh — preflight / run / verify / rollback.

It is a copy, not a move. The source database is never written to and never dropped, which is what makes it safe to re-run and trivial to undo: rollback is DROP SCHEMA on the target while the source sits there intact.

  • --schema=johorzoo scopes it — coreapi.public is never touched
  • --single-transaction --exit-on-error — a failure halfway cannot leave a half-populated schema for the next run to trip over
  • verify compares exact counts plus three things a count misses: sequence positions (restore the rows, leave last_value at 1, next insert collides on the PK), the 6 functions and 6 triggers (the MySQL ON UPDATE CURRENT_TIMESTAMP emulation for general, rail_menu, rail_menu_ticket_group, report, report_attachment, ticket_variant — lose them and updated_at quietly stops advancing), and the FK count

Local run: 28 tables / 47,799 rows, counts identical, sequences in step, functions and triggers matched. The baseline captured from the johorzoo database then passed unchanged against coreapi.johorzoo — 77 assertions, DSN the only difference. Zero table-name collisions with coreapi.public.

Two defects found by testing the destructive path rather than trusting it: docker exec -i drained the script's own stdin, so the count queries ate rollback's piped confirmation, read hit EOF and set -e killed it mutely — the schema survived by accident, not design. And the confirmation now reads from /dev/tty, so echo yes | … rollback can never approve a DROP.

5c — point the app at it · ✅ DONE except AutoMigrate (f1c557d, c529bcd, 3493507)

  • Schema-qualified all 26 ticketing TableName() → johorzoo.<table>. Verified GORM handles this before relying on it: it emits SELECT * FROM "johorzoo"."tag" — correctly split and quoted — and Migrator().HasTable() recognises the table.
  • Three raw-SQL sites needed their own prefix, and two were latent MySQL-era bugs: JOIN Report ON Report.report_id = … and JOIN Ticket_Tag ON …. Unquoted identifiers fold to lower case on Postgres, so these resolved only because the ticketing connection carried search_path=johorzoo. My first sweep for them was case-sensitive and missed both.
  • One connection. ZooDB, GetZooDB() and the second DSN deleted. search_path left at the default rather than widened to public,johorzoo — with both schemas reachable, an unqualified name would resolve by search order, so a future native table named tag or report would silently capture ticketing's queries.
  • pg_advisory_lock on the 5 schedulers — the live duplicate-email bug. Per-job pg_try_advisory_lock, own *sql.Conn (a session lock belongs to one connection; released on a different pooled connection the unlock is a no-op and the lock leaks).
  • TICKETING_DB_NAME deliberately kept: zitadel_user_migration.go is a one-off tool that reads the original separate database, and Quickstart's legacy TicketAPI.yml still uses it.
  • ⛔ Adding the 26 ticketing models to registerModels() — REVERTED, do not retry as-is. See below.

5d — ticketing is GORM-owned · ✅ DONE (28d4144, 8d291d6, 24bb762)

Hazard 2 in CLAUDE.md is closed, and Phase 4 — Ticketing schema → GORM is done. The 26 ticketing models register through RegisterModels alongside media and distribution. One mechanism owns the whole schema — no second registry, no separate tool.

A create-only registry (c725e31) was the waystation while the structs caught up to a schema they did not author. It is now removed: AutoMigrate creates missing tables and alters existing ones, so once the structs were faithful, create-only had no job, and two policies is the thing it existed to avoid.

The gate had to be rebuilt twice before it was honest

This is the important lesson. Two versions of cmd/schema-diff reported a clean schema while a real boot crashed. Both built GORM's schema into an empty database and compared it to live column by column — and a schema built from nothing cannot show what AutoMigrate does to an existing table.

Version What it compared What it missed
v1 AutoMigrate exit code only (migrated 26/26, 0 failed) everything — it compared nothing
v2 types, nullability, defaults, uniqueness via pg_index that a unique constraint and a unique index are different to AutoMigrate. pg_index sees both, so v2 normalised away exactly the difference that caused the crash
v3 runs AutoMigrate against a copy of the live schema and diffs the DDL — gate passes and the boot agrees

⚠️ The 0 differences figure recorded under Phase 4 came from an ad-hoc SQL query never committed to the repo. It was both narrower than stated and unreproducible. That, more than the missing dimensions, is why the failure was a surprise.

migration/schema-diff.sh is now the gate:

./migration/schema-diff.sh          # what would AutoMigrate do to live?
./migration/schema-diff.sh scratch  # can the models rebuild it from nothing?

It also does the half a structural diff cannot: for each unique index the diff says GORM would create, it asks the live database whether its data permits one. The scratch copy has no rows, so a unique index over existing duplicates succeeds there and log.Fatals at boot against real data.

What had to change, on both sides

Truth was on both sides, so both moved.

Models — 54 defaults + 1 uniqueness kind: - 38 × default:'' on NOT NULL strings (GORM was stripping these → insert becomes a NOT NULL violation) - 12 × timestamps, six spelled now() and six CURRENT_TIMESTAMP to match each table — semantically identical, but Postgres stores the expression as written so one spelling cannot match both - 3 literals; report.reporting_period_type 'previous_day' → 'last_30_days', a real model/DB disagreement, not a missing tag. The DB won because that is the behaviour users have — if previous_day was the intent it needs a product decision - TicketGroupIntegrationConfig.TicketGroupId: uniqueIndex → unique

Database — 3 migrations, applied locally: | Migration | What | |---|---| | admin_is_disabled_default | admin.is_disabled had no default while customer.is_disabled has DEFAULT false — same column, same schema, both structs declare it. The DB was inconsistent, not the models. | | ticketing_gorm_alignment | drops the no-op DEFAULT NULL from 36 nullable columns; renames the unique constraint to GORM's name. Guarded to abort if any NOT NULL column carried a DEFAULT NULL | | ticketing_gorm_indexes | creates the 7 indexes the models declare. ⚠️ Five are a behavioural tightening — admin.username, customer.email, tag.tag_name, token.access_token, token.refresh_token had no uniqueness enforcement. A duplicate customer email will now be rejected. CREATE UNIQUE INDEX fails outright on existing duplicates, and staging/prod hold different data — run the checks in the file first |

Verified

  • johorzoo DDL byte-identical after a full AutoMigrate pass over 47,799 rows including 4,867 real orders; container confirmed recreated, zero restarts
  • 96 assertions green; schema-diff.sh passes; scratch mode builds 26/28 tables from nothing (config and schema_migrations have no Go model)
  • all three migrations idempotent, re-run to confirm, row counts unchanged

⚠️ Accepted risk, recorded deliberately

standardization-goals.md Goal 4 flags AutoMigrate-in-production as a hazard: additive-only, and RunMigrationMain() log.Fatals the process on any error — named there as the likely cause of past staging corruption worked around with down -v. Ticketing is now under that mechanism. The decision is to standardise on GORM first and move to a tracked migrator once proven, with schema-diff.sh as the guard in the meantime.

Verified state after 5c

  • Suite green: HTTP + dbstate, 0 relation-not-found errors
  • pg_stat_activity: connections to coreapi only, none to johorzoo
  • Advisory locks verified with two replicas on one database. The first test proved nothing — they booted ~18s apart and each job finishes in milliseconds, so ticks never overlapped and both ran everything. Holding lock 8420001 externally and restarting a replica is the real test: ticket email retry skipped this tick while the other four ran. Afterwards pg_locks showed nothing held and no release errors.

Unrelated pre-existing bug found in the logs (not fixed here)

domain/catalogue/handler_schedule.go compares time_blocks.start_time (timestamptz) against $3::time, which Postgres rejects outright (operator does not exist). Native TellyboardAdmin live-control code, on the native schema, in a file this work never touched — the scheduler has been failing every ~15s independently. Spawned separately.

Phase 6 — Envelope unification (frontend-coordinated)

  • Land {success, data, error} across ticketing
  • Update ~96 call sites in SpatioViewAdmin + ticketcms
  • Retire pkg/models, pkg/errors
  • Delete branch feature/ticketing-fiber-app-split

Note: frontends only ever branch on 3 respCode values — 2000, 5003 (redundant with HTTP 404) and 4009 (doesn't exist in the backend, dead branch). The numeric registry isn't earning its keep.

Standing risks (not phase-gated)

  1. Ticketing schedulers run on every replica with no locking. 5 goroutines, opt-out (DISABLE_*_SCHEDULER != "true"), no lock/leader election. Prod HPA is minReplicas: 2, maxReplicas: 5 and no disable var is set anywhere in the IaC. The email job does select-then-mark, so 2–5 pods send the same mail. Likely causing duplicate ticket/report emails in production today. Fixed in Phase 5.
  2. Production PII in git, now in three places — 2,367 citizens incl. NRIC. Malaysian PDPA applies. Separate decisions needed on history purging and whether this is reportable; both above a dev session's pay grade.
  3. Secrets in a plain k8s ConfigMap (coreapi-configmap, prod + staging): DB_PASS, COREAPI_EMAIL_PASSWORD, MINIO_SECRET_KEY, COREAPI_AUTHAPI_PSK.
  4. Live credentials committed in Quickstart/.env and .env.shared-dev (Gmail app password, Google OAuth secret + refresh token, Zoho secret).
  5. TLS verification disabled on POS and payment clients (InsecureSkipVerify: true).
  6. Per-product email fields have no Go consumer — TicketGroupIntegrationConfig's Email* columns are editable in SpatioViewAdmin and change nothing. POS and JohorPay halves are wired; email half is not.