Spaces:
Runtime error
Runtime error
Cyril Dupland
feat(prices): implement price management with date ranges, including creation, retrieval, and history listing; update schemas and API documentation
15f1cb0 | # Database workflow | |
| The project uses Supabase SQL migrations as source of truth and Prisma for Python client generation. | |
| ## Mandatory flow | |
| 1. Create schema change in local Supabase. | |
| 2. Generate migration: | |
| - `supabase db diff -f <migration_name>` | |
| 3. Review migration SQL in `supabase/migrations/`. | |
| 4. Rebuild local DB: | |
| - `supabase db reset` | |
| 5. Sync Prisma schema: | |
| - `prisma db pull` | |
| - `prisma generate` | |
| 6. Run tests, then commit SQL + Prisma schema + code/tests. | |
| ## Rules | |
| - Never edit an already applied migration. | |
| - Never edit `prisma/schema.prisma` manually. | |
| - Avoid direct schema-only changes in remote dashboard. | |
| ## RLS and Application Roles | |
| Row level security policies are part of the database contract and must be | |
| managed through dedicated Supabase migrations. | |
| - Document policy changes in `docs/rls.md`. | |
| - Record larger access-control decisions as ADRs under `docs/adr/`. | |
| - Keep backend Prisma queries aligned with RLS behavior because Prisma direct | |
| connections bypass RLS. | |
| See `docs/rls.md` and `docs/adr/0003-rls-supplier-status.md`. | |
| --- | |
| ## Schema Extensions | |
| ### Price date ranges | |
| The `prices` table supports multiple rows per `(article_id, supplier_id)` with validity windows. See [prices.md](prices.md) for business rules. | |
| - **`date_start`**: mandatory start date (default `CURRENT_DATE`). | |
| - **`date_end`**: optional end date; `NULL` means an open/default price. | |
| - **`prices_one_open_per_article_supplier`**: partial unique index on open prices. | |
| - **`prices_promo_no_overlap`**: partial exclusion constraint preventing overlapping promo ranges. | |
| ### Groups & Brands | |
| The `groups` table allows clustering multiple brands (e.g., Stellantis) for administrative or reporting purposes. | |
| - **`groups`**: Contains `id` (UUID), `name` (varchar), and timestamps. | |
| - **`brand`**: Modified to include an optional `group_id` foreign key referencing `groups(id)` (with `ON DELETE SET NULL` behavior). | |
| ### Many-to-Many Supplier Discounts | |
| The database supports linking each supplier discount line to multiple suppliers. | |
| - **`supplier_discount`**: Holds global/contextual discount metadata (has no direct supplier link). | |
| - **`supplier_discount_lines`**: Contains specific discount amounts. The legacy `supplier_id` column is dropped. | |
| - **`supplier_discount_suppliers`**: Join table providing the many-to-many link between `supplier_discount_lines` and `suppliers`. Contains `supplier_discount_line_id` and `supplier_id`. | |
| --- | |
| ## Database Functions & RPCs | |
| The schema defines several custom PostgreSQL functions and RPCs exposed to authenticated users: | |
| | Function / RPC | Parameters | Purpose | | |
| | --- | --- | --- | | |
| | `public.is_admin()` | None | Checks if the current authenticated user has the `admin` app role. | | |
| | `public.normalize_reference_number(value text)` | `value` (text) | Returns the normalized reference (strips leading lowercase `a`). | | |
| | `public.find_article_references_by_ref(p_ref text)` | `p_ref` (text) | Looks up reference numbers matching `p_ref` (applies normalization first). | | |
| | `public.upsert_article_by_main_ref(...)` | `p_reference_number` (text), `p_label_fr` (text), `p_label_en` (text), `p_category_id` (uuid) | Atomic upsert of an article and its main reference number. Used for unit catalog item creation. | | |
| | `public.admin_list_suppliers(p_status)` | `p_status` (public.supplier_status) | Returns suppliers matching the status, bypassing active-only RLS policies. Restricted to admins. | | |
| --- | |
| ## Database Error Handling & Invariants | |
| When writing script queries, handling transaction commits, or calling RPCs directly from the clients, developers must anticipate the following database error codes and triggers: | |
| - **`42501` (Insufficient Privilege)**: Returned if a non-admin user attempts to run an admin RPC (e.g., `admin_list_suppliers`, `upsert_article_by_main_ref`) or violates table RLS policies. | |
| - **`23505` (Unique Violation)**: | |
| - `article_references_main_ref_unique_idx`: Triggered if a rename or insertion attempts to assign a main reference that already exists as a main reference for a different article. | |
| - `prices_one_open_per_article_supplier`: Triggered if a second open price (`date_end IS NULL`) is inserted for the same `(article_id, supplier_id)` pair. | |
| - **`23P01` (Exclusion Violation)**: | |
| - `prices_promo_no_overlap`: Triggered when two promotional price ranges overlap for the same `(article_id, supplier_id)`. | |
| - **`check_violation` (`has no main reference`)**: Raised at commit time if an `articles` row is inserted but no corresponding main reference (`main = true` in `article_references`) is inserted/linked within the same transaction. Ensure any creation flow performs both inserts in a single atomic transaction. | |