SRV-02 — Database schema v1 — Prisma models, migrations & reference seeds
Implement the data-model baseline as multi-file Prisma schema; first migration; seeds for wilayas/communes, brands, categories, roles/permissions, settings and reference lists.
| Field | Details |
|---|---|
| Task code | SRV-02 |
| Phase | P0 — Foundations (cadrage, UX/UI, architecture & contract) |
| Product scope | V1 |
| Status | Done (Sun 18 Oct 2026) |
| Start | Sun 18 Oct 2026 |
| Due | Sun 18 Oct 2026 |
| Responsible | Anis — Full-stack developer |
| Co-responsible | — |
| Effort | 1.5 person-day(s) planned · 6 from scratch · size S |
| Area · app | Server · server |
| Depends on | SRV-01, PRJ-03 |
| Unblocks | SRV-03, OPS-01 |
| Milestones | — |
Summary
Implement the data-model baseline as multi-file Prisma schema; first migration; seeds for wilayas/communes, brands, categories, roles/permissions, settings and reference lists.
Description
Step 2: the data model in the SRS (database design) becomes an executable Prisma 7 schema on PostgreSQL 17, split into one .prisma file per module as frozen in SRV-01, with the first migration and the reference seeds every environment needs (GEO-01, ADM-04, ADM-05, AUD-02). It encodes the 17 Oct decisions: ProductVariant carries priceSuperGros, priceDemiGros, priceDetail (integer centimes, HT), vatRate, recommendedPrice and the restricted purchasePrice; Reseller.customerType is required; AttributeDefinition/VariantAttributeValue, SupportRegion/SupportRegionWilaya, ReturnRequest/ReturnItem and CreditNote are V1; PriceList, PriceListItem and QuantityTier are not created. Integrity lives in the database where possible: unique normalised reseller phone, unique business numbers per sequence, non-negative stock checks, history tables (OrderStatusChange, OrderChange, StockMovement, PriceChange). Out of scope: business logic and endpoints. OPS-01 (demo seed) and SRV-03 (contract shapes) depend on it.
Note
Subtasks
| # | Subtask | Owner |
|---|---|---|
| 1 | Multi-file schema packages/db/prisma/schema/<module>.prisma for the 24 modules with UUIDv7 ids generated app-side | Anis |
| 2 | Catalog and pricing models: ProductVariant three prices + vatRate + recommendedPrice + purchasePrice, AttributeDefinition (type, unit, allowed values, per category), VariantAttributeValue, PriceChange | Anis |
| 3 | Ordering, returns and finance models: Order/OrderItem snapshots, OrderStatusChange, OrderChange, ReturnRequest/ReturnItem, Invoice, Payment, PaymentAllocation, CreditAccount, CreditNote | Anis |
| 4 | Constraints and indexes: unique normalised phone, unique business numbers, CHECK physical ≥ 0 and reserved ≥ 0 on StockLevel, partial indexes for order queues and catalog search (pg_trgm) | Anis |
| 5 | Platform tables: AuditLog, Setting, NumberSequence, IdempotencyRecord, OutboxEvent, ExportJob, ImportJob, AppVersionPolicy, ReferenceItem | Anis |
| 6 | First migration and migration workflow (prisma migrate dev / deploy, review of generated SQL in PRs) | Anis |
| 7 | Reference seeds: official wilayas and communes (FR/AR), brands HOCO/ACEFAST/BOROFONE, category tree, starter attribute definitions (power W, connector, cable length, colour, capacity mAh, Bluetooth version) | Anis |
| 8 | Access seeds: 8 roles, permission catalogue (18 modules × 7 actions), default role matrix | Anis |
| 9 | Settings defaults (stock.reserveOn=VALIDATED, stock.deductOn=SHIPPED, orders.assistedConfirmationHours=72, orders.cancelAllowedStatuses, GPS 150 m/100 m), reference lists (8 list keys), number sequences, default support region | Anis |
| 10 | Schema review against the API contract needs as first consumer | Imad |
Deliverables
- packages/db/prisma/schema/*.prisma (one file per module) and packages/db/prisma.config.ts
- packages/db/prisma/migrations/0001_init
- packages/db/src/seed/{geo,catalog,access,settings,reference-lists,support-regions}.ts
- Generated ER diagram linked from /docs/srs/database-design
Acceptance criteria
-
prisma migrate deployon an empty PostgreSQL 17 database succeeds andpnpm db:seedis idempotent (second run changes nothing) - No PriceList, PriceListItem or QuantityTier table exists in the V1 migration; ProductVariant has the three price columns (PRC-01)
- Inserting a second reseller with the same normalised phone fails on the unique constraint (CRM-03)
- A StockLevel update that makes physical or reserved negative is rejected by the database
- All 8 roles and the full permission catalogue are seeded; reference lists contain the 8 list keys of AUTHORING §3
- Every entity of the service catalogue exists with its canonical name
Requirements covered
| Requirement | Title | Release |
|---|---|---|
| GEO-01 | Official wilaya and commune reference (FR/AR names) used by addresses, zones and filters | V1 |
| ADM-04 | Unique human-readable numbers for resellers, orders, invoices, tours, visits, tickets and returns | V1 |
| ADM-05 | Reference lists: business types, potentials, visit types, not-done reasons, issue tags, ticket types, carriers | V1 |
| AUD-02 | Entity histories: order changes, status changes, stock movements and price changes are kept and viewable in context | V1 |
Test measures
- DB test suite against a real Postgres (Testcontainers or the compose service): constraints for phone uniqueness, stock non-negativity, business number uniqueness
- Seed idempotency test (run twice, compare row counts)
- Migration drift check in CI (
prisma migrate diffagainst the schema)
Risks
- Integer overflow on large order/invoice totals in centimes — store totals and aggregates as BigInt and expose them as numbers in the contract (safe up to 2^53)
- Official wilaya/commune list changes (new wilayas) — seed from a versioned source file and keep codes stable so addresses are not broken
KPI
- Delivered by Sun 18 Oct 2026 within 1.5 person-day(s).
- Every acceptance criterion above demonstrated before hand-off; linked requirements' acceptance criteria pass.
- Zero open critical defects, permission gaps or data-integrity regressions at sign-off.
Generated from content/plan/tasks.json by scripts/generate-team-tasks.mjs. Dates are computed by the scheduler (Sunday–Thursday weeks, holidays excluded) — change the plan, not this page.
SRV-01 — Service catalogue — module boundaries, events & ownership (freeze)
Review and freeze the 24 server modules: responsibilities, data each owns, events each emits, jobs, and which apps consume them.
SRV-03 — API contract v1 — oRPC contract package, OpenAPI export & mock server
Every V1 endpoint as a typed contract (Zod request/response, errors, pagination) in packages/contracts; OpenAPI 3.1 generated and published; Prism mock server so Imad builds against it the same day.