kjh2064/KArtSell.Aegis
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity

fix: missing model_operations.models table + compliance schema breaking every fresh DB (live deploy failure) #29

Merged
kjh2064 merged 1 commits from feat/L-vs14-portfolio-reconciliation into main 2026-08-07 20:26:22 +09:00
Conversation 0 Commits 1 Files Changed 7
+111 -20
kjh2064 commented 2026-08-07 20:25:15 +09:00
Owner
Copy Link
Copy Source

Summary

PR #28 merged into main and its deploy immediately failed with relation "model_operations.models" does not exist at migration 0036 — the exact same failure already seen against a local test DB while verifying #28, confirming it's not environment drift but a real, deterministic bug: no migration ever created model_operations.models, only referenced it via FK (0036, 0038) and queried it directly (OpenDartDailyBatchJob.cs). Migration 0037 had the same class of bug for the compliance schema itself.

Root-cause fix

  • Add 0035_model_operations_models.sql (must sort before 0036). Scope is intentionally minimal — id/ticker/published_at/correlation_id/revision, i.e. only what's actually referenced today. The full Model Card/lifecycle schema is separate, larger work and isn't guessed at here.
  • Add CREATE SCHEMA IF NOT EXISTS compliance; to 0037, plus IF NOT EXISTS on its indexes for re-run idempotency (matching the rest of this migration set).
  • Verified: full chain 0000→0040 applies to a fresh DB ("Upgrade successful") and re-run is a clean no-op ("No new scripts need to be executed").

Bugs surfaced once the schema was actually complete enough to query

None of these were reachable before (the tables/schema didn't exist), so they were never exercised:

  • Dapper was never configured for snake_case↔PascalCase column mapping (Dapper.DefaultTypeMap.MatchNamesWithUnderscores), so every Sql class's result-set queries were silently returning null/default for every property instead of throwing. Fixed once, centrally, via a [ModuleInitializer] in KArtSell.BuildingBlocks/Data/DapperBootstrap.cs so it's set before the first query regardless of entry point (Host/DbMigrator/tests).
  • jsonb/inet columns written without an explicit cast (42804: column "x" is of type jsonb but expression is of type text) in AuditSql (details, ip_address), TradeSql (kis_response), SellDecisionSql (oos_performance) — fixed with ::jsonb/::inet casts. AuditSql's jsonb read-back into Dictionary<string,object> also needed a raw-DTO + JsonSerializer.Deserialize mapping.
  • AuditSql.RedactAuditEventDetailsAsync had a literal duplicate SET details = ... details = ... (invalid SQL) — nested the two jsonb_set calls into one assignment.

Test plan

  • dotnet build KArtSell.sln -c Release — 0 errors, 0 warnings
  • Architecture tests 13/13, unit tests 54/54 + 18/18 — all still pass
  • Local Postgres: full migration chain 0000→0040 fresh-install succeeds; re-run is a clean idempotent no-op
  • Host boots cleanly and registers all 34 endpoints against the now-complete schema

Follow-up (recorded in TECH_DEBT_REGISTER.md)

  • DEBT-022: jsonb/inet cast audit is partial — not yet checked across every Sql class, only what surfaced in this session's test runs.
  • DEBT-023: ApprovalSql.InsertProposalAsync still fails on a raw DateOnly Dapper parameter — same class of issue as the snake_case fix, needs the same kind of centralized fix.
  • DEBT-024: new TradeExecutionTests don't insert their FK parent rows (test-only gap, not a production defect); one pure-logic ranker test returns 1000 instead of 950 under the full suite (not yet root-caused, may be test-order state leakage); DbUpMigrationTests.* fail locally on a Postgres role permission gap unrelated to this fix.

🤖 Generated with Claude Code

## Summary PR #28 merged into `main` and its deploy immediately failed with `relation "model_operations.models" does not exist` at migration `0036` — the exact same failure already seen against a local test DB while verifying #28, confirming it's not environment drift but a real, deterministic bug: **no migration ever created `model_operations.models`**, only referenced it via FK (`0036`, `0038`) and queried it directly (`OpenDartDailyBatchJob.cs`). Migration `0037` had the same class of bug for the `compliance` schema itself. ## Root-cause fix - Add `0035_model_operations_models.sql` (must sort before `0036`). Scope is intentionally minimal — `id`/`ticker`/`published_at`/`correlation_id`/`revision`, i.e. only what's actually referenced today. The full Model Card/lifecycle schema is separate, larger work and isn't guessed at here. - Add `CREATE SCHEMA IF NOT EXISTS compliance;` to `0037`, plus `IF NOT EXISTS` on its indexes for re-run idempotency (matching the rest of this migration set). - **Verified**: full chain `0000`→`0040` applies to a fresh DB (`"Upgrade successful"`) and re-run is a clean no-op (`"No new scripts need to be executed"`). ## Bugs surfaced once the schema was actually complete enough to query None of these were reachable before (the tables/schema didn't exist), so they were never exercised: - **Dapper was never configured for snake_case↔PascalCase column mapping** (`Dapper.DefaultTypeMap.MatchNamesWithUnderscores`), so every Sql class's result-set queries were silently returning null/default for every property instead of throwing. Fixed once, centrally, via a `[ModuleInitializer]` in `KArtSell.BuildingBlocks/Data/DapperBootstrap.cs` so it's set before the first query regardless of entry point (Host/DbMigrator/tests). - **jsonb/inet columns written without an explicit cast** (`42804: column "x" is of type jsonb but expression is of type text`) in `AuditSql` (`details`, `ip_address`), `TradeSql` (`kis_response`), `SellDecisionSql` (`oos_performance`) — fixed with `::jsonb`/`::inet` casts. `AuditSql`'s jsonb read-back into `Dictionary<string,object>` also needed a raw-DTO + `JsonSerializer.Deserialize` mapping. - `AuditSql.RedactAuditEventDetailsAsync` had a literal duplicate `SET details = ... details = ...` (invalid SQL) — nested the two `jsonb_set` calls into one assignment. ## Test plan - [x] `dotnet build KArtSell.sln -c Release` — 0 errors, 0 warnings - [x] Architecture tests 13/13, unit tests 54/54 + 18/18 — all still pass - [x] Local Postgres: full migration chain `0000`→`0040` fresh-install succeeds; re-run is a clean idempotent no-op - [x] Host boots cleanly and registers all 34 endpoints against the now-complete schema ## Follow-up (recorded in `TECH_DEBT_REGISTER.md`) - DEBT-022: jsonb/inet cast audit is partial — not yet checked across every Sql class, only what surfaced in this session's test runs. - DEBT-023: `ApprovalSql.InsertProposalAsync` still fails on a raw `DateOnly` Dapper parameter — same class of issue as the snake_case fix, needs the same kind of centralized fix. - DEBT-024: new `TradeExecutionTests` don't insert their FK parent rows (test-only gap, not a production defect); one pure-logic ranker test returns 1000 instead of 950 under the full suite (not yet root-caused, may be test-order state leakage); `DbUpMigrationTests.*` fail locally on a Postgres role permission gap unrelated to this fix. 🤖 Generated with [Claude Code](https://claude.com/claude-code)
kjh2064 added 1 commit 2026-08-07 20:25:15 +09:00
fix: missing model_operations.models table + compliance schema breaking every fresh DB (live deploy failure) 4059828abf 
The SCP/DbMigrator deploy to the production target (178.104.200.7)
failed today with `relation "model_operations.models" does not
exist` at migration 0036 — the exact same failure I'd already hit
against a local test database, confirming this isn't environment
drift but a real, deterministic bug: no migration ever created
model_operations.models, only referenced it via FK (0036, 0038) and
queried it directly (OpenDartDailyBatchJob.cs). Migration 0037 had
the same class of bug for the `compliance` schema itself.

- Add 0035_model_operations_models.sql (must sort before 0036).
  Scope is intentionally minimal — id/ticker/published_at/
  correlation_id/revision, i.e. only what's actually referenced
  today. The full Model Card/lifecycle schema is separate, larger
  work and isn't guessed at here.
- Add `CREATE SCHEMA IF NOT EXISTS compliance;` to 0037, plus
  IF NOT EXISTS on its indexes for re-run idempotency (matching the
  rest of this migration set).
- Verified: full chain 0000->0040 applies to a fresh DB
  ("Upgrade successful") and re-run is a clean no-op
  ("No new scripts need to be executed").

Fixing the schema far enough to actually run queries against it
surfaced 3 more real, previously untested bugs in already-merged
code (none reachable before because the tables/schema didn't exist):

- Dapper was never configured for snake_case<->PascalCase column
  mapping (`Dapper.DefaultTypeMap.MatchNamesWithUnderscores`), so
  every Sql class's result-set queries were silently returning
  null/default for every property instead of throwing. Fixed once,
  centrally, via a `[ModuleInitializer]` in
  KArtSell.BuildingBlocks/Data/DapperBootstrap.cs so it's set before
  the first query regardless of entry point (Host/DbMigrator/tests).
- jsonb/inet columns written without an explicit cast
  (`42804: column "x" is of type jsonb but expression is of type
  text`) in AuditSql (details, ip_address), TradeSql (kis_response),
  SellDecisionSql (oos_performance) — fixed with `::jsonb`/`::inet`
  casts. AuditSql's jsonb read-back into Dictionary<string,object>
  also needed a raw-DTO + JsonSerializer.Deserialize mapping.
- AuditSql.RedactAuditEventDetailsAsync had a literal duplicate
  `SET details = ... details = ...` (invalid SQL) — nested the two
  jsonb_set calls into one assignment.

Verified: dotnet build 0/0; architecture 13/13; unit 54/54+18/18;
Host boots cleanly and registers all 34 endpoints against the
now-complete schema.

New tech debt recorded: DEBT-020 (this fix), DEBT-021 (Dapper
snake_case fix), DEBT-022 (jsonb/inet casts, partial — not yet
audited beyond what surfaced), DEBT-023 (ApprovalSql.
InsertProposalAsync still fails on a raw DateOnly parameter — same
class of issue as DEBT-021, not yet fixed), DEBT-024 (TradeExecution
tests don't insert FK parent rows; one pure-logic ranker test
returned 1000 instead of 950 under the full suite, not yet
root-caused; DbUpMigrationTests fail locally on a Postgres role
permission gap unrelated to this fix).

Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
kjh2064 merged commit 54b7922167 into main 2026-08-07 20:26:22 +09:00
Sign in to join this conversation.
Reviewers
No Reviewers
Labels
No items
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
kjh2064 (김재현)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: kjh2064/KArtSell.Aegis#29
Delete Branch "feat/L-vs14-portfolio-reconciliation"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?