feat(migrations): reconcile collaboration tables missed by initial-schema backfill (B3) #151

Merged

varutasu merged 1 commit from convoy/reconcile-b3-collaboration-tables into main

2026-07-06 12:27:46 -04:00

varutasu commented

2026-06-14 09:04:56 -04:00

(Migrated from github.com)

Summary

Brief 3 of reconcile-historical-add-scripts (convoy 22ebef2).

Captures the DDL half of scripts/add-collaboration-features.js (a no-go-zone historical script) that the initial-schema backfill missed:

collection_permissions (collaboration roles + invite tokens)
collection_activity (audit-trail; details JSONB)
users.is_pending (invited-but-not-yet-accepted users)
4 indexes (idx_collection_permissions_collection_id, idx_collection_permissions_user_id, idx_collection_activity_collection_id, idx_collections_visibility)

lib/permission-middleware.js::withCollectionPermission reads collection_permissions and logCollectionActivity writes collection_activity — both are runtime invariants. Prod has these surfaces from the historical script running; this migration brings fresh Neon branches to parity so npm install → npm run setup-db alone is sufficient onboarding.

Files changed

migrations/1781000000003_reconcile-collaboration-tables.js (new, +101 LOC)

Acceptance criteria

Exactly one new migration file with pre-assigned timestamp 1781000000003
Idempotent: CREATE TABLE IF NOT EXISTS (2x), ADD COLUMN IF NOT EXISTS (1x), CREATE INDEX IF NOT EXISTS (4x)
FKs match the historical script verbatim (ON DELETE CASCADE on collection_id / user_id; ON DELETE SET NULL on collection_activity.user_id; bare REFERENCES users(id) on invited_by)
CHECK constraints on role and status match the historical script
UNIQUE(collection_id, user_id) and UNIQUE on invite_token preserved
details JSONB per .cursor/rules/schema-map.mdc
down() is a hard-stub throw matching initial-schema.js's pattern
Per-statement comments cite the historical script intent (file docstring)
No edits to scripts/, no edits to convoy file, no other migrations touched

Test plan

node --check migrations/1781000000003_*.js → exit 0
npm run lint → 0 errors (1 pre-existing baseline warning unchanged)
npm run test:run → 131/131 pass (no fixture breakage; tests don't exercise these tables directly)
Static idempotency proof: grep -r "collection_permissions\|collection_activity\|is_pending\|idx_collection_permissions\|idx_collection_activity\|idx_collections_visibility" migrations/ returns only this new file
Optional Neon-branch verification per § Verification plan (D5) — operator-driven post-merge once B1/B2/B4/B5 also land

Notes for reviewer

Cross-brief column dependency on B2. idx_collections_visibility references collections.visibility, which B2 (reconcile-collections-columns, timestamp 1781000000002) adds. At npm run migrate up time the timestamps enforce ordering (B2 < B3 → B2 runs first), so this is satisfied. The architect's plan allocated all 4 historical-script indexes to B3; per the convoy's D7 "Audited ordering risks" B3 was listed as depending on collections + users only, but the visibility column dependency is implicit in the index. Worth a glance — if B2 is delayed/reordered, B3 would fail to apply on a partial-state env. Acceptable risk: timestamps are immutable and B1-B5 are siblings landing into main.
Owner-permission DML backfill from the historical script is NOT captured. Per Brief outline B3: "Defer owner-permission backfill DML — fresh envs have no pre-existing collections needing backfill." This matches D3's assumption that prod is the source of truth and the script's DML already ran there.
details JSONB runtime shape. lib/permission-middleware.js::logCollectionActivity writes ${JSON.stringify(details)} into the column, matching the JSONB type declared here.
All FK targets exist by migration time. collections and users are created by 1779853647564_initial-schema; cards / scan tables / etc. are not referenced by this migration. No FK ordering bug.

Made with Cursor

## Summary Brief 3 of `reconcile-historical-add-scripts` (convoy `22ebef2`). Captures the DDL half of `scripts/add-collaboration-features.js` (a no-go-zone historical script) that the `initial-schema` backfill missed: - `collection_permissions` (collaboration roles + invite tokens) - `collection_activity` (audit-trail; `details JSONB`) - `users.is_pending` (invited-but-not-yet-accepted users) - 4 indexes (`idx_collection_permissions_collection_id`, `idx_collection_permissions_user_id`, `idx_collection_activity_collection_id`, `idx_collections_visibility`) `lib/permission-middleware.js::withCollectionPermission` reads `collection_permissions` and `logCollectionActivity` writes `collection_activity` — both are runtime invariants. Prod has these surfaces from the historical script running; this migration brings fresh Neon branches to parity so `npm install` → `npm run setup-db` alone is sufficient onboarding. ## Files changed - `migrations/1781000000003_reconcile-collaboration-tables.js` (new, +101 LOC) ## Acceptance criteria - [x] Exactly one new migration file with pre-assigned timestamp `1781000000003` - [x] Idempotent: `CREATE TABLE IF NOT EXISTS` (2x), `ADD COLUMN IF NOT EXISTS` (1x), `CREATE INDEX IF NOT EXISTS` (4x) - [x] FKs match the historical script verbatim (`ON DELETE CASCADE` on `collection_id` / `user_id`; `ON DELETE SET NULL` on `collection_activity.user_id`; bare `REFERENCES users(id)` on `invited_by`) - [x] CHECK constraints on `role` and `status` match the historical script - [x] `UNIQUE(collection_id, user_id)` and `UNIQUE` on `invite_token` preserved - [x] `details JSONB` per `.cursor/rules/schema-map.mdc` - [x] `down()` is a hard-stub throw matching `initial-schema.js`'s pattern - [x] Per-statement comments cite the historical script intent (file docstring) - [x] No edits to scripts/, no edits to convoy file, no other migrations touched ## Test plan - [x] `node --check migrations/1781000000003_*.js` → exit 0 - [x] `npm run lint` → 0 errors (1 pre-existing baseline warning unchanged) - [x] `npm run test:run` → 131/131 pass (no fixture breakage; tests don't exercise these tables directly) - [x] Static idempotency proof: `grep -r "collection_permissions\|collection_activity\|is_pending\|idx_collection_permissions\|idx_collection_activity\|idx_collections_visibility" migrations/` returns only this new file - [ ] Optional Neon-branch verification per § Verification plan (D5) — operator-driven post-merge once B1/B2/B4/B5 also land ## Notes for reviewer - **Cross-brief column dependency on B2.** `idx_collections_visibility` references `collections.visibility`, which B2 (`reconcile-collections-columns`, timestamp `1781000000002`) adds. At `npm run migrate up` time the timestamps enforce ordering (B2 < B3 → B2 runs first), so this is satisfied. The architect's plan allocated all 4 historical-script indexes to B3; per the convoy's D7 "Audited ordering risks" B3 was listed as depending on `collections` + `users` only, but the `visibility` column dependency is implicit in the index. Worth a glance — if B2 is delayed/reordered, B3 would fail to apply on a partial-state env. Acceptable risk: timestamps are immutable and B1-B5 are siblings landing into `main`. - **Owner-permission DML backfill from the historical script is NOT captured.** Per Brief outline B3: "Defer owner-permission backfill DML — fresh envs have no pre-existing collections needing backfill." This matches D3's assumption that prod is the source of truth and the script's DML already ran there. - **`details JSONB` runtime shape.** `lib/permission-middleware.js::logCollectionActivity` writes `${JSON.stringify(details)}` into the column, matching the JSONB type declared here. - **All FK targets exist by migration time.** `collections` and `users` are created by `1779853647564_initial-schema`; `cards` / scan tables / etc. are not referenced by this migration. No FK ordering bug. Made with [Cursor](https://cursor.com)

vercel[bot] commented

2026-06-14 09:05:02 -04:00

(Migrated from github.com)

The latest updates on your projects. Learn more about Vercel for GitHub.

Project	Deployment	Actions	Updated (UTC)
tcg-vault	Ready	Preview, Comment	Jun 14, 2026 1:19pm

[vc]: #gzJEkGs2Wq1lLS5QXLbfRd1PiBfPCAEKkQ8MW0ORuAQ=:eyJpc01vbm9yZXBvIjp0cnVlLCJ0eXBlIjoiZ2l0aHViIiwicHJvamVjdHMiOlt7Im5hbWUiOiJ0Y2ctdmF1bHQiLCJwcm9qZWN0SWQiOiJwcmpfRjZXOEVvRkd3Y0g3aWVGcnRvRlNlOXdVVkFhNSIsImxpdmVGZWVkYmFjayI6eyJyZXNvbHZlZCI6MCwidW5yZXNvbHZlZCI6MCwidG90YWwiOjAsImxpbmsiOiJ0Y2ctdmF1bHQtZ2l0LWNvbnZveS1yZWNvbmNpLTZiYjA3Ni1yYW5kYWxsLXN0aWxsd2VsbHMtcHJvamVjdHMudmVyY2VsLmFwcCJ9LCJpbnNwZWN0b3JVcmwiOiJodHRwczovL3ZlcmNlbC5jb20vcmFuZGFsbC1zdGlsbHdlbGxzLXByb2plY3RzL3RjZy12YXVsdC84WmNRNmlCUUU3ekFha2FpazlOV2hoVWUxZzhVIiwicHJldmlld1VybCI6InRjZy12YXVsdC1naXQtY29udm95LXJlY29uY2ktNmJiMDc2LXJhbmRhbGwtc3RpbGx3ZWxscy1wcm9qZWN0cy52ZXJjZWwuYXBwIiwibmV4dENvbW1pdFN0YXR1cyI6IkRFUExPWUVEIiwicm9vdERpcmVjdG9yeSI6bnVsbH1dLCJyZXF1ZXN0UmV2aWV3VXJsIjoiaHR0cHM6Ly92ZXJjZWwuY29tL3ZlcmNlbC1hZ2VudC9yZXF1ZXN0LXJldmlldz9vd25lcj1zdHdsLWxhYnMmcmVwbz10Y2ctdmF1bHQmcHI9MTUxIn0= The latest updates on your projects. Learn more about [Vercel for GitHub](https://vercel.link/github-learn-more). | Project | Deployment | Actions | Updated (UTC) | | :--- | :----- | :------ | :------ | | [tcg-vault](https://vercel.com/randall-stillwells-projects/tcg-vault) | ![Ready](https://vercel.com/static/status/ready.svg) [Ready](https://vercel.com/randall-stillwells-projects/tcg-vault/8ZcQ6iBQE7zAakaik9NWhhUe1g8U) | [Preview](https://tcg-vault-git-convoy-reconci-6bb076-randall-stillwells-projects.vercel.app), [Comment](https://vercel.live/open-feedback/tcg-vault-git-convoy-reconci-6bb076-randall-stillwells-projects.vercel.app?via=pr-comment-feedback-link) | Jun 14, 2026 1:19pm | <a href="https://vercel.com/vercel-agent/request-review?owner=stwl-labs&repo=tcg-vault&pr=151" rel="noreferrer"><picture><source media="(prefers-color-scheme: dark)" srcset="https://agents-vade-review.vercel.sh/request-review-dark.svg"><source media="(prefers-color-scheme: light)" srcset="https://agents-vade-review.vercel.sh/request-review-light.svg"><img src="https://agents-vade-review.vercel.sh/request-review-light.svg" alt="Request Review"></picture></a>

github-actions[bot] commented

2026-06-14 09:08:35 -04:00

(Migrated from github.com)

Pipeline Health

Build + CI gates

Gate	Status
Vercel build (Preview)	✅ pass
CI: Lint	✅ pass
CI: Schema map fresh	❌ skipped
Preview smoke	❌ failure
Visual diff	⏭ skipped or pending

Build runs on Vercel; this CI runs lint and schema-map drift only (no duplicate build).

Role reports

Role	Status
Reviewer report	⏳ pending
A11y audit	⏳ pending
Design system audit	⏳ pending

See individual comments above for details. This rollup updates automatically.

## Pipeline Health ### Build + CI gates | Gate | Status | | --- | --- | | Vercel build (Preview) | ✅ pass | | CI: Lint | ✅ pass | | CI: Schema map fresh | ❌ skipped | | Preview smoke | ❌ failure | | Visual diff | ⏭ skipped or pending | _Build runs on Vercel; this CI runs lint and schema-map drift only (no duplicate build)._ ### Role reports | Role | Status | | --- | --- | | Reviewer report | ⏳ pending | | A11y audit | ⏳ pending | | Design system audit | ⏳ pending | See individual comments above for details. This rollup updates automatically.