# 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`](../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](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](frontend-catalog-create.md).

## Reads / search

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

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