docs/references.md

Article reference numbers

Rule

If a reference_number starts with a single lowercase a, that character is removed before storage and lookup. Examples:

Input	Stored / matched
a12345	12345
aA12	A12
A12	A12 (unchanged)
aa12	a12 (only the first a)

Source of truth: Postgres function public.normalize_reference_number(text) (immutable). Python code uses the mirror helper normalize_reference() in app/domain/reference_normalization.py.

Writes

Any insert or update on public.article_references.reference_number is normalized by the trigger article_references_normalize_ref (BEFORE INSERT OR UPDATE OF reference_number).

• FastAPI imports: Pydantic validators on ArticleImportRow and PriceImportRow call normalize_reference() before bulk upsert or resolution.
• Article catalogue imports may also write alias references (main = false) via reference_old, and rename an article via reference_new (see imports.md).
• Front-end / Supabase client: you may send aXXX or XXX; the stored value is always normalized.
• SQL scripts: prefer public.upsert_article_by_main_ref(...), which normalizes its p_reference_number argument.
• Frontend article creation flow is documented in frontend-catalog-create.md.

Reads / search

Do not filter with .eq('reference_number', userInput) on the client. Use the RPC:

const { data, error } = await supabase.rpc('find_article_references_by_ref', {
  p_ref: userInput,
});

The RPC normalizes p_ref the same way as the trigger.

FastAPI repositories that query by reference should call normalize_reference() on the parameter before WHERE reference_number = $1 (imports already do this via validators and ArticleReferenceRepository.resolve_reference_numbers).

Data migration

Existing rows with a leading a are updated in migration 20260521100000_normalize_article_reference.sql. Before applying on production, run the collision audit from that migration’s comment if both a12345 and 12345 could exist as main references.

Changing the rule

Update both public.normalize_reference_number and normalize_reference() in Python, then add tests in tests/unit/test_reference_normalization.py and integration tests under tests/integration/test_reference_rpc.py.