# 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.