feat(db): federation schema — grants/peers/audit_log [FED-M2-01] #486

Merged

jason.woltje merged 2 commits from feat/federation-m2-schema into main 2026-04-22 02:02:22 +00:00

Conversation 1 Commits 2 Files Changed 7 +4095 -1

jason.woltje commented 2026-04-22 01:40:13 +00:00

Owner

Copy Link

Summary

• Adds federation_peers, federation_grants, and federation_audit_log tables with full FK constraints and indexes
• Adds peer_state and grant_status PostgreSQL enums (declared before tables that reference them)
• Generates migration 0008_careless_lake.sql via pnpm --filter @mosaicstack/db db:generate
• Adds integration tests (6/6 pass with FEDERATED_INTEGRATION=1) verifying FK cascades, set-null behavior, enum constraints, and unique constraints

Closes part of #461 (FED-M2-01 task).

New files

File	Purpose
packages/db/src/federation.ts	Re-export barrel for federation symbols (application code import point)
packages/db/src/federation.integration.test.ts	Integration tests (6 tests, all pass)
packages/db/drizzle/0008_careless_lake.sql	Forward-only migration
packages/db/drizzle/meta/0008_snapshot.json	Drizzle-kit snapshot

Modified files

File	Change
packages/db/src/schema.ts	Added federation enums + tables at the bottom (drizzle-kit CJS/ESM compatibility)
packages/db/src/index.ts	Re-export * from './federation.js'
packages/db/drizzle/meta/_journal.json	Updated with new migration entry

Tables

federation_peers — registered peer gateway (cert CN identity from Step-CA)

• Unique constraints on common_name and cert_serial
• Indexes on cert_serial (CRL lookups) and state (routing)

federation_grants — per-user data access grant to a specific peer

• FK → users.id ON DELETE CASCADE
• FK → federation_peers.id ON DELETE CASCADE
• Compound indexes on (subject_user_id, status) and (peer_id, status)

federation_audit_log — append-only request log (M4 writes; M2 creates only)

• FKs to all three: peers, users, grants — all SET NULL (audit rows survive deletions)
• Indexes on (peer_id, created_at), (subject_user_id, created_at), (created_at)

Tests

FEDERATED_INTEGRATION=1 pnpm --filter @mosaicstack/db test src/federation.integration.test.ts
✓ 6 passed (168ms)

Tests cover:

1. Insert sample user + peer + grant + audit row — round-trip verified
2. FK cascade: user delete cascades to federation_grants
3. FK set-null: peer delete sets federation_audit_log.peer_id = NULL
4. Enum constraint: invalid grant_status rejected by DB
5. Enum constraint: invalid peer_state rejected by DB
6. Unique constraint: duplicate cert_serial rejected

Quality gates

• typecheck: ✅ 38/38 tasks pass
• lint: ✅ 21/21 tasks pass
• format:check: ✅ all files pass

🤖 Generated with Claude Code

## Summary - Adds `federation_peers`, `federation_grants`, and `federation_audit_log` tables with full FK constraints and indexes - Adds `peer_state` and `grant_status` PostgreSQL enums (declared before tables that reference them) - Generates migration `0008_careless_lake.sql` via `pnpm --filter @mosaicstack/db db:generate` - Adds integration tests (6/6 pass with `FEDERATED_INTEGRATION=1`) verifying FK cascades, set-null behavior, enum constraints, and unique constraints Closes part of #461 (FED-M2-01 task). ## New files | File | Purpose | |---|---| | `packages/db/src/federation.ts` | Re-export barrel for federation symbols (application code import point) | | `packages/db/src/federation.integration.test.ts` | Integration tests (6 tests, all pass) | | `packages/db/drizzle/0008_careless_lake.sql` | Forward-only migration | | `packages/db/drizzle/meta/0008_snapshot.json` | Drizzle-kit snapshot | ## Modified files | File | Change | |---|---| | `packages/db/src/schema.ts` | Added federation enums + tables at the bottom (drizzle-kit CJS/ESM compatibility) | | `packages/db/src/index.ts` | Re-export `* from './federation.js'` | | `packages/db/drizzle/meta/_journal.json` | Updated with new migration entry | ## Tables **`federation_peers`** — registered peer gateway (cert CN identity from Step-CA) - Unique constraints on `common_name` and `cert_serial` - Indexes on `cert_serial` (CRL lookups) and `state` (routing) **`federation_grants`** — per-user data access grant to a specific peer - FK → `users.id` ON DELETE CASCADE - FK → `federation_peers.id` ON DELETE CASCADE - Compound indexes on `(subject_user_id, status)` and `(peer_id, status)` **`federation_audit_log`** — append-only request log (M4 writes; M2 creates only) - FKs to all three: peers, users, grants — all SET NULL (audit rows survive deletions) - Indexes on `(peer_id, created_at)`, `(subject_user_id, created_at)`, `(created_at)` ## Tests ``` FEDERATED_INTEGRATION=1 pnpm --filter @mosaicstack/db test src/federation.integration.test.ts ✓ 6 passed (168ms) ``` Tests cover: 1. Insert sample user + peer + grant + audit row — round-trip verified 2. FK cascade: user delete cascades to `federation_grants` 3. FK set-null: peer delete sets `federation_audit_log.peer_id = NULL` 4. Enum constraint: invalid `grant_status` rejected by DB 5. Enum constraint: invalid `peer_state` rejected by DB 6. Unique constraint: duplicate `cert_serial` rejected ## Quality gates - typecheck: ✅ 38/38 tasks pass - lint: ✅ 21/21 tasks pass - format:check: ✅ all files pass 🤖 Generated with [Claude Code](https://claude.com/claude-code)

jason.woltje added 1 commit 2026-04-22 01:40:14 +00:00

feat(db): federation schema (grants/peers/audit_log) [FED-M2-01] ... a1ab4386fe

Adds Drizzle ORM schema and migration for federation v1 milestone 2:

- New enums: peer_state ('pending'|'active'|'suspended'|'revoked'),
  grant_status ('active'|'revoked'|'expired')
- New tables: federation_peers, federation_grants, federation_audit_log
- FK cascades: user delete cascades grants; peer delete set-nulls audit_log
- Migration: 0008_careless_lake.sql
- Integration tests (FEDERATED_INTEGRATION=1): 6/6 pass

No business logic, no services, no DTOs — schema only.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

jason.woltje referenced this issue from a commit 2026-04-22 01:52:57 +00:00

docs(federation): S21 tracking — DEPLOY-01/02 done, IMG-FIX in flight, M2-01 in remediation

jason.woltje referenced this pull request 2026-04-22 01:53:12 +00:00

docs(federation): S21 tracking — DEPLOY-01/02 done, IMG-FIX in flight, M2-01 in remediation #487

jason.woltje added 1 commit 2026-04-22 01:53:14 +00:00

fixup: federation_audit_log DESC indexes + reserved M4 columns ... 0e0ad9defe

- Add .desc() to all three federation_audit_log created_at indexes for
  reverse-chronological scans (PRD section 7.3)
- Add reserved nullable columns query_hash, outcome, bytes_out per
  TASKS.md M2-01 spec (written by M4, columns reserved now to avoid
  retroactive migration)
- Regenerate migration 0008 in-place (replaces 0008_careless_lake.sql
  with 0008_smart_lyja.sql containing DESC indexes + new columns)
- Update integration test: add reserved columns to CREATE TABLE in
  beforeAll; add 7th test for peer→grant cascade delete

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

jason.woltje commented 2026-04-22 01:53:30 +00:00

Author

Owner

Copy Link

Addressed review:

• federation_audit_log indexes now use created_at DESC NULLS LAST for reverse-chronological scans (PRD §7.3)
• Reserved M4 columns added (query_hash, outcome, bytes_out) per TASKS.md M2-01 spec
• Migration regenerated in place: 0008_careless_lake.sql → 0008_smart_lyja.sql (DESC indexes + new columns)
• Integration test updated: CREATE TABLE IF NOT EXISTS now includes reserved columns; 7th test added for peer→grant cascade delete
• All 7 integration tests pass; typecheck, lint, format:check all green
• Force-pushed feat/federation-m2-schema (commit 0e0ad9de)

Addressed review: - `federation_audit_log` indexes now use `created_at DESC NULLS LAST` for reverse-chronological scans (PRD §7.3) - Reserved M4 columns added (`query_hash`, `outcome`, `bytes_out`) per TASKS.md M2-01 spec - Migration regenerated in place: `0008_careless_lake.sql` → `0008_smart_lyja.sql` (DESC indexes + new columns) - Integration test updated: `CREATE TABLE IF NOT EXISTS` now includes reserved columns; 7th test added for peer→grant cascade delete - All 7 integration tests pass; typecheck, lint, format:check all green - Force-pushed `feat/federation-m2-schema` (commit `0e0ad9de`)

jason.woltje merged commit 54c278b871 into main 2026-04-22 02:02:22 +00:00

jason.woltje referenced this issue from a commit 2026-04-22 02:02:23 +00:00

feat(db): federation schema — grants/peers/audit_log [FED-M2-01] (#486)

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

jason.woltje

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: mosaicstack/stack#486