mosaicstack/stack
1
0
Fork 0
Code Issues 48 Pull Requests 2 Actions Packages Projects Releases 15 Wiki Activity

KBN-100: implement unified Drizzle schema and N-1 PostgreSQL migration #769

New Issue
Open
opened 2026-07-14 21:58:07 +00:00 by jason.woltje · 0 comments
jason.woltje commented 2026-07-14 21:58:07 +00:00
Owner
Copy Link

Objective

Implement the frozen Native Kanban/SOT v1 unified Drizzle schema and concrete PostgreSQL N-1 migration after the completed KBN-010 gate.

Authority and prerequisites

  • KBN canon: PR #752 / main 49e8a54.
  • KBN-010: PR #765 squash-merged 2e228007; issue #753 closed; post-merge main pipeline #1813 terminal green.
  • Frozen contract version: 1.0.0-rc.4 under docs/native-kanban-sot/.
  • PostgreSQL is the sole writable project/task/orchestration SOT. Generated exports, files, Valkey, browser state, providers, and outage notes are never import/write authority.

Exclusive implementation scope

  • packages/db/src/schema.ts
  • packages/db/drizzle/** including journal/metadata and migration tests
  • narrowly required DB package test/config files explicitly enumerated before edits
  • KBN tracking/scratchpad documentation

No repositories, Gateway, MCP, CLI, Web, Coordinator behavior, fleet lifecycle, Discord/channel, connector fencing, deployment, or production migration execution.

Required delivery

  1. Add the complete frozen tenant/identity/planning/orchestration/governance relational model and exact enums/checks/unique indexes/composite FKs.
  2. Preserve N-1 legacy declarations through the compatibility/rollback window; no destructive drop/rename/narrow in expand.
  3. Implement ordered expand/backfill/validate/compatibility/switch/contract migration mechanics with bounded idempotent resume and quarantine of ambiguity—never guess.
  4. Implement rc.4 SI-001 exactly: create missions_workspace_id_uidx(workspace_id,id) before artifact/approval dependent FKs; retain global mission PK and project-congruent key.
  5. Enforce workspace and project congruence, exactly-one owners/principals/targets, active lease uniqueness, bigint monotonic fence storage, proposal event composites, immutable evidence/audit posture, and RESTRICT parent deletion.
  6. Preserve legacy status/tag/date/config/description/agent/backlog data with checksums and explicit field mapping.

Mandatory TDD / evidence

  • TDD RED/GREEN for every constraint/migration defect and critical mutation/data path.
  • Empty database and exact production-shape migration.
  • Crash/partial resume and rollback-before-switch.
  • N-1 startup/read/write compatibility; no premature new-only statuses.
  • Workspace/project/member/owner/principal/target negatives.
  • N100-01..50, including SI-001 duplicate feasibility, candidate-before-FKs, exact FK reconciliation, and separate foreign-workspace artifact/approval failures.
  • Proposal event missing/foreign/unrelated semantic fixtures.
  • Parent/DAG race obligations frozen for transaction owners where not schema-enforceable.
  • Real PostgreSQL DDL integration; no PGlite-only certification.
  • Reviewed generated SQL/journal, full package and repository gates, independent code/security review, Codex-Ultron verification, PR CI, squash merge, post-main terminal-green CI, and issue closure.

Holds

KBN-105 and all KBN consumers remain blocked until this card merges and closes. No runtime or production migration is authorized by this issue.

## Objective Implement the frozen Native Kanban/SOT v1 unified Drizzle schema and concrete PostgreSQL N-1 migration after the completed KBN-010 gate. ## Authority and prerequisites - KBN canon: PR #752 / main `49e8a54`. - KBN-010: PR #765 squash-merged `2e228007`; issue #753 closed; post-merge main pipeline #1813 terminal green. - Frozen contract version: `1.0.0-rc.4` under `docs/native-kanban-sot/`. - PostgreSQL is the sole writable project/task/orchestration SOT. Generated exports, files, Valkey, browser state, providers, and outage notes are never import/write authority. ## Exclusive implementation scope - `packages/db/src/schema.ts` - `packages/db/drizzle/**` including journal/metadata and migration tests - narrowly required DB package test/config files explicitly enumerated before edits - KBN tracking/scratchpad documentation No repositories, Gateway, MCP, CLI, Web, Coordinator behavior, fleet lifecycle, Discord/channel, connector fencing, deployment, or production migration execution. ## Required delivery 1. Add the complete frozen tenant/identity/planning/orchestration/governance relational model and exact enums/checks/unique indexes/composite FKs. 2. Preserve N-1 legacy declarations through the compatibility/rollback window; no destructive drop/rename/narrow in expand. 3. Implement ordered expand/backfill/validate/compatibility/switch/contract migration mechanics with bounded idempotent resume and quarantine of ambiguity—never guess. 4. Implement rc.4 SI-001 exactly: create `missions_workspace_id_uidx(workspace_id,id)` before artifact/approval dependent FKs; retain global mission PK and project-congruent key. 5. Enforce workspace and project congruence, exactly-one owners/principals/targets, active lease uniqueness, bigint monotonic fence storage, proposal event composites, immutable evidence/audit posture, and RESTRICT parent deletion. 6. Preserve legacy status/tag/date/config/description/agent/backlog data with checksums and explicit field mapping. ## Mandatory TDD / evidence - TDD RED/GREEN for every constraint/migration defect and critical mutation/data path. - Empty database and exact production-shape migration. - Crash/partial resume and rollback-before-switch. - N-1 startup/read/write compatibility; no premature new-only statuses. - Workspace/project/member/owner/principal/target negatives. - N100-01..50, including SI-001 duplicate feasibility, candidate-before-FKs, exact FK reconciliation, and separate foreign-workspace artifact/approval failures. - Proposal event missing/foreign/unrelated semantic fixtures. - Parent/DAG race obligations frozen for transaction owners where not schema-enforceable. - Real PostgreSQL DDL integration; no PGlite-only certification. - Reviewed generated SQL/journal, full package and repository gates, independent code/security review, Codex-Ultron verification, PR CI, squash merge, post-main terminal-green CI, and issue closure. ## Holds KBN-105 and all KBN consumers remain blocked until this card merges and closes. No runtime or production migration is authorized by this issue.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
fleet-enhancement
Fleet-wide reliability/speed/cost improvements surfaced by the nightly enhance pass
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
jason.woltje
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: mosaicstack/stack#769
Delete Branch "%!s()"

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?