docs/database.md

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