Card Binder Schema Changes

Vylpes commented

2026-04-28 17:49:48 +01:00

Owner

Epic: #281
Story Points: 5

SO THAT I can save card binder info persistently

Acceptance Criteria

GIVEN I am a developer
WHEN the project is migrated
THEN the database schema will include the data required for the new Card Binder feature

Subtasks

Add BinderSlot entity and migration
Add tests
Add documentation

Notes

BinderSlot NEW
- id (PK, UUID)
- UserId (FK)
- PageNumber (int, 1..3) - Up to 3 for now so don't constrain in db
- SlotIndex (int, 1..9) - 1-based
- CardNumber (string)

Epic: #281 Story Points: 5 --- SO THAT I can save card binder info persistently ## Acceptance Criteria GIVEN I am a developer WHEN the project is migrated THEN the database schema will include the data required for the new Card Binder feature ## Subtasks - [ ] Add `BinderSlot` entity and migration - [ ] Add tests - [ ] Add documentation ## Notes - BinderSlot NEW - id (PK, UUID) - UserId (FK) - PageNumber (int, 1..3) - Up to 3 for now so don't constrain in db - SlotIndex (int, 1..9) - 1-based - CardNumber (string)

Vylpes added this to the 0.12.0 milestone

2026-04-28 17:49:48 +01:00

Vylpes added the

needs

estimate

label

2026-04-28 17:55:15 +01:00

Vylpes added a new dependency

2026-04-28 18:15:37 +01:00

#496 Card Binder Service

Vylpes added a new dependency

2026-04-28 18:19:15 +01:00

#499 Inventory Service AvailableQuantity function

Vylpes referenced this issue

2026-04-28 18:27:03 +01:00

Card Binder #281

Vylpes added the

type

story

label

2026-04-28 18:30:34 +01:00

Smithy-bot was assigned by Vylpes

2026-09-07 12:35:32 +01:00

Smithy-bot commented

2026-09-08 12:05:52 +01:00

Member

Story point estimate: 5

Area	Points	Rationale
`BinderSlot` entity + EF migration	3	Single new table with defined columns (`id`, `UserId` FK, `PageNumber`, `SlotIndex`, `CardNumber`); follows existing patterns in the codebase
Tests	1	Migration applies cleanly; entity mapping and FK constraints behave as expected
Documentation	1	Schema/entity documented for downstream binder command work (#496+)

Total: 5

Criteria are specific enough to size — entity fields and subtasks are well defined, and epic #281 provides broader Card Binder context. Moving to needs/tests for QA script authoring.

## Story point estimate: **5** | Area | Points | Rationale | |------|--------|-----------| | `BinderSlot` entity + EF migration | 3 | Single new table with defined columns (`id`, `UserId` FK, `PageNumber`, `SlotIndex`, `CardNumber`); follows existing patterns in the codebase | | Tests | 1 | Migration applies cleanly; entity mapping and FK constraints behave as expected | | Documentation | 1 | Schema/entity documented for downstream binder command work (#496+) | **Total: 5** Criteria are specific enough to size — entity fields and subtasks are well defined, and epic #281 provides broader Card Binder context. Moving to `needs/tests` for QA script authoring.

Smithy-bot added

and removed

labels

2026-09-08 12:05:58 +01:00

Smithy-bot commented

2026-09-09 12:04:18 +01:00

Member

QA test scripts (against acceptance criteria and subtasks)

Preconditions for all scripts unless noted:

Local/dev database (not production); backup or disposable DB instance.
develop (or target branch) builds and existing migrations apply cleanly before this change (baseline).
A test user row exists in the users table for FK validation.

TC-01 — Migration applies cleanly

Covers: GIVEN a developer WHEN the project is migrated THEN schema includes Card Binder data

On a fresh DB (or after rolling back to pre-BinderSlot state), run the project migration command (e.g. yarn db:migrate / dotnet ef database update — per repo convention).
Expect: migration succeeds with no errors; BinderSlot table is created.
Re-run migrations on an already-migrated DB.
Expect: idempotent / no-op; no duplicate-table or constraint errors.

TC-02 — `BinderSlot` table structure

Covers: entity fields from Notes (id, UserId, PageNumber, SlotIndex, CardNumber)

Inspect the generated migration and live schema (SQL \d / SSMS / information_schema).
Expect:

Column	Type	Constraints
`id`	UUID	PRIMARY KEY
`UserId`	matches users PK type	NOT NULL, FK → users
`PageNumber`	int	NOT NULL (no DB max of 3 — app-level only)
`SlotIndex`	int	NOT NULL
`CardNumber`	string/varchar	NOT NULL (or nullable only if explicitly decided — default expect NOT NULL)

Confirm no DB check constraint limiting PageNumber to 1–3 (per issue: "don't constrain in db").
Confirm no DB check constraint limiting SlotIndex to 1–9 (1-based is app convention, not enforced at DB layer unless documented otherwise).

TC-03 — Foreign key behaviour

Covers: UserId FK integrity

Insert a BinderSlot row with a valid UserId.
Expect: insert succeeds.
Insert with a non-existent UserId.
Expect: FK violation / rejected.
Delete a user who has binder slots (if user deletion is supported).
Expect: behaviour matches project convention (CASCADE delete slots, RESTRICT, or soft-delete) — document which was chosen.

TC-04 — `BinderSlot` entity mapping

Covers: Add BinderSlot entity subtask

Locate the BinderSlot entity class; confirm property names and types match the migration columns.
Confirm it is registered in the ORM context (DbContext / TypeORM entity list).
Round-trip: create entity instance → save → load by id.
Expect: all fields persist and read back correctly (PageNumber, SlotIndex, CardNumber, UserId).

TC-05 — Slot indexing convention (app layer)

Covers: SlotIndex 1-based (1..9)

If seed/fixture data or unit tests exist, confirm slots use 1-based indices (slot 1, not 0).
Document in entity or migration comments that SlotIndex is 1-based for downstream binder UI/commands (#496+).
Expect: no off-by-one assumptions in tests; convention is documented.

TC-06 — Page number flexibility

Covers: PageNumber int, up to 3 for now, not DB-constrained

Insert rows with PageNumber = 1, 2, 3.
Expect: all succeed.
Insert with PageNumber = 4 (or higher).
Expect: succeeds at DB level (no check constraint); app validation may come later in command issues.

TC-07 — Multiple slots per user/page

Covers: schema supports binder layout persistence

For one UserId, insert 9 slots on PageNumber = 1 with distinct SlotIndex (1–9) and distinct CardNumber values.
Expect: all 9 rows persist.
Insert a second page (PageNumber = 2) with at least one slot.
Expect: rows coexist; no unintended unique constraint on (UserId, PageNumber) unless explicitly required (default: multiple slots per page allowed).

TC-08 — Unit / integration tests

Covers: Add tests subtask

yarn test (or project test runner) includes new BinderSlot / migration tests.
Expect:

Migration up/down (or apply) test passes.
Entity mapping test: save + load with all fields.
FK constraint test: invalid UserId rejected.

Full test suite is green; no regressions in existing migration tests.

TC-09 — Documentation

Covers: Add documentation subtask

Schema/entity docs exist (README migration section, docs/ page, or entity XML/JSDoc — per repo convention).
Expect: documents BinderSlot columns, FK to user, 1-based SlotIndex, and that PageNumber is not DB-capped (app limits to 3 pages for now).
Docs reference epic #281 and note this schema unblocks binder command work (#496+).

TC-10 — Downstream readiness smoke (schema only)

Covers: "data required for the new Card Binder feature"

Manually insert a realistic binder layout (3 pages × up to 9 slots) for a test user via ORM or SQL.
Query all slots for that user ordered by PageNumber, SlotIndex.
Expect: data is queryable in the shape future binder commands will need; no missing columns for persisting card numbers per slot.

Criteria and field spec are specific enough to script without going back to needs/criteria. Moving to needs/approval.

**QA test scripts** (against acceptance criteria and subtasks) Preconditions for all scripts unless noted: - Local/dev database (not production); backup or disposable DB instance. - `develop` (or target branch) builds and existing migrations apply cleanly before this change (baseline). - A test user row exists in the users table for FK validation. --- ### TC-01 — Migration applies cleanly **Covers:** GIVEN a developer WHEN the project is migrated THEN schema includes Card Binder data 1. On a fresh DB (or after rolling back to pre-`BinderSlot` state), run the project migration command (e.g. `yarn db:migrate` / `dotnet ef database update` — per repo convention). **Expect:** migration succeeds with no errors; `BinderSlot` table is created. 2. Re-run migrations on an already-migrated DB. **Expect:** idempotent / no-op; no duplicate-table or constraint errors. ### TC-02 — `BinderSlot` table structure **Covers:** entity fields from Notes (`id`, `UserId`, `PageNumber`, `SlotIndex`, `CardNumber`) 1. Inspect the generated migration and live schema (SQL `\d` / SSMS / `information_schema`). **Expect:** | Column | Type | Constraints | |--------|------|-------------| | `id` | UUID | PRIMARY KEY | | `UserId` | matches users PK type | NOT NULL, FK → users | | `PageNumber` | int | NOT NULL (no DB max of 3 — app-level only) | | `SlotIndex` | int | NOT NULL | | `CardNumber` | string/varchar | NOT NULL (or nullable only if explicitly decided — default expect NOT NULL) | 2. Confirm **no** DB check constraint limiting `PageNumber` to 1–3 (per issue: "don't constrain in db"). 3. Confirm **no** DB check constraint limiting `SlotIndex` to 1–9 (1-based is app convention, not enforced at DB layer unless documented otherwise). ### TC-03 — Foreign key behaviour **Covers:** `UserId` FK integrity 1. Insert a `BinderSlot` row with a valid `UserId`. **Expect:** insert succeeds. 2. Insert with a non-existent `UserId`. **Expect:** FK violation / rejected. 3. Delete a user who has binder slots (if user deletion is supported). **Expect:** behaviour matches project convention (CASCADE delete slots, RESTRICT, or soft-delete) — document which was chosen. ### TC-04 — `BinderSlot` entity mapping **Covers:** Add `BinderSlot` entity subtask 1. Locate the `BinderSlot` entity class; confirm property names and types match the migration columns. 2. Confirm it is registered in the ORM context (`DbContext` / TypeORM entity list). 3. Round-trip: create entity instance → save → load by `id`. **Expect:** all fields persist and read back correctly (`PageNumber`, `SlotIndex`, `CardNumber`, `UserId`). ### TC-05 — Slot indexing convention (app layer) **Covers:** `SlotIndex` 1-based (1..9) 1. If seed/fixture data or unit tests exist, confirm slots use 1-based indices (slot 1, not 0). 2. Document in entity or migration comments that `SlotIndex` is 1-based for downstream binder UI/commands (#496+). **Expect:** no off-by-one assumptions in tests; convention is documented. ### TC-06 — Page number flexibility **Covers:** `PageNumber` int, up to 3 for now, not DB-constrained 1. Insert rows with `PageNumber` = 1, 2, 3. **Expect:** all succeed. 2. Insert with `PageNumber` = 4 (or higher). **Expect:** succeeds at DB level (no check constraint); app validation may come later in command issues. ### TC-07 — Multiple slots per user/page **Covers:** schema supports binder layout persistence 1. For one `UserId`, insert 9 slots on `PageNumber` = 1 with distinct `SlotIndex` (1–9) and distinct `CardNumber` values. **Expect:** all 9 rows persist. 2. Insert a second page (`PageNumber` = 2) with at least one slot. **Expect:** rows coexist; no unintended unique constraint on `(UserId, PageNumber)` unless explicitly required (default: multiple slots per page allowed). ### TC-08 — Unit / integration tests **Covers:** Add tests subtask 1. `yarn test` (or project test runner) includes new `BinderSlot` / migration tests. **Expect:** - Migration up/down (or apply) test passes. - Entity mapping test: save + load with all fields. - FK constraint test: invalid `UserId` rejected. 2. Full test suite is green; no regressions in existing migration tests. ### TC-09 — Documentation **Covers:** Add documentation subtask 1. Schema/entity docs exist (README migration section, `docs/` page, or entity XML/JSDoc — per repo convention). **Expect:** documents `BinderSlot` columns, FK to user, 1-based `SlotIndex`, and that `PageNumber` is not DB-capped (app limits to 3 pages for now). 2. Docs reference epic #281 and note this schema unblocks binder command work (#496+). ### TC-10 — Downstream readiness smoke (schema only) **Covers:** "data required for the new Card Binder feature" 1. Manually insert a realistic binder layout (3 pages × up to 9 slots) for a test user via ORM or SQL. 2. Query all slots for that user ordered by `PageNumber`, `SlotIndex`. **Expect:** data is queryable in the shape future binder commands will need; no missing columns for persisting card numbers per slot. --- Criteria and field spec are specific enough to script without going back to `needs/criteria`. Moving to `needs/approval`.

Smithy-bot removed their assignment

2026-09-09 12:04:23 +01:00

Vylpes was assigned by Smithy-bot

2026-09-09 12:04:23 +01:00

Smithy-bot added

and removed

labels

2026-09-09 12:04:23 +01:00