# Postal Code Enrichment Plan

## Goal

Fill missing `postal_code` values for `liste_desj` entries using only the local Quebec postal dataset:

- Source CSV: `/usr/local/var/www/megatron/php/runtime/codepostal/CSV.csv`
- No Canada Post
- No Google
- No fallback path
- Ignore apartment/unit when matching

The intended write target is:

- `public.liste_desj_entry_activity.postal_code`

The source rows come from:

- `public.liste_desj`

## Constraints and Decisions

### Confirmed constraints

- The CSV is large: about `2.8G`, about `5.25M` lines.
- `liste_desj` is also large: about `3.83M` rows.
- Missing postal codes are heavily concentrated in `QC`.
- Matching must be local-only.
- Unit / apartment must not be part of the lookup key.

### Decision

Do not scan the CSV on demand from PHP.

Instead:

1. Import the CSV into a dedicated Postgres lookup table.
2. Normalize lookup keys.
3. Match `liste_desj` rows against that local indexed lookup.
4. Save only deterministic matches.

## Files Added / Changed

### Added

- [POSTALCODE.md](/usr/local/var/www/megatron/POSTALCODE.md)
- [php/tools/lib/liste_desj_postal_local.php](/usr/local/var/www/megatron/php/tools/lib/liste_desj_postal_local.php)
- [php/tools/import_codepostal_qc_lookup.php](/usr/local/var/www/megatron/php/tools/import_codepostal_qc_lookup.php)
- [php/tools/fill_liste_desj_postal_codes_local.php](/usr/local/var/www/megatron/php/tools/fill_liste_desj_postal_codes_local.php)

### Changed

- [php/src/Repositories/ClientRepository.php](/usr/local/var/www/megatron/php/src/Repositories/ClientRepository.php)

## Database Objects Added

### Lookup table

`public.codepostal_qc_lookup`

Columns:

- `lookup_id`
- `civic_number`
- `street_name`
- `city`
- `postal_code`
- `civic_number_norm`
- `street_norm`
- `city_norm`
- `source_row_count`
- `postal_code_count`
- `is_unique_postal`
- `created_at`
- `updated_at`

### Indexes

- unique index on `(city_norm, civic_number_norm, street_norm, postal_code)`
- match index on `(city_norm, civic_number_norm, street_norm)` where `is_unique_postal = true`
- index on `postal_code`

## Matching Strategy

### Lookup key

Each postal-code match is based on:

1. normalized city
2. normalized civic number
3. normalized street name

Apartment / unit is intentionally ignored.

## Current Matching Rules

These are the current proposed rules for the postal-code filler.

### Core rule

A postal code may be filled only when one source result in `codepostal_qc_raw` is the clear match for the input address.

### Input fields to use

From `liste_desj`:

- `street_address`
- `city`
- `province`

Ignore:

- `apt`
- unit
- suite
- box / `CP` / `RR` fragments for match identity

### Province gate

1. Attempt this local filler only when `province = QC`
2. Do not use this filler for non-QC rows

### Address parsing

1. Extract civic number from the start of `street_address`
2. Treat the remainder as street text
3. If no civic number can be extracted, do not fill

Example:

- `1131 39E AV CP331`
- civic number = `1131`
- street text = `39E AV`

### Input normalization

1. Uppercase
2. Remove accents
3. Collapse punctuation and repeated spaces
4. Standardize street abbreviations:
   - `AV`, `AVE` -> `AVENUE`
   - `CH` -> `CHEMIN`
   - `BD`, `BLVD`, `BOUL` -> `BOULEVARD`
   - `RTE` -> `ROUTE`
   - `RG` -> `RANG`
   - `ST` -> `SAINT`
   - `STE` -> `SAINTE`
   - `MT` -> `MONT`
5. Standardize ordinals:
   - `1ER`, `1RE` -> `1E`
   - `39E` stays `39E`
6. Remove noise tokens from street text:
   - `APP`
   - `APT`
   - `APPARTEMENT`
   - `UNITE`
   - `UNIT`
   - `SUITE`
   - `BUREAU`
   - `CP`
   - `BP`
   - `CASE POSTALE`
   - `RR`
7. Normalize city the same way:
   - uppercase
   - deaccent
   - remove punctuation noise
   - normalize `ST`, `STE`, `MT`
   - remove admin words:
     - `MUNICIPALITE`
     - `VILLE`
     - `CANTON`
     - `PAROISSE`
     - `VILLAGE`

### Source row normalization

For each candidate row in `codepostal_qc_raw`, normalize:

- `numero_municipal`
- street variants:
  - `odonyme_recompose_long`
  - `odonyme_recompose_normal`
  - `odonyme_recompose_court`
- locality variants:
  - `nom_municipalite`
  - `nom_municipalite_complet`
  - `nom_arrondissement`
  - `nom_mrc`
  - `nom_region_administrative`

### Matching order

1. Exact civic number match is mandatory
2. Exact normalized street match against any allowed source street variant
3. Exact normalized locality match using the locality order below
4. If more than one row matches, reduce by distinct postal code count
5. If more than one postal code remains, do not fill

### City / locality rules

Primary locality match fields:

1. `nom_municipalite`
2. `nom_municipalite_complet`
3. `nom_arrondissement`

Secondary fallback locality fields, only if all primary locality fields fail:

4. `nom_mrc`
5. `nom_region_administrative`

So `liste_desj.city` may match any of:

- `nom_municipalite`
- `nom_municipalite_complet`
- `nom_arrondissement`
- `nom_mrc` only if no primary locality field matched
- `nom_region_administrative` only if no other locality field matched

This allows borough names such as `Saint-Leonard` to match source rows whose municipality is `Montreal` when the row also contains `nom_arrondissement = Saint-Leonard`.

### Street rules

1. Prefer `odonyme_recompose_long`
2. Then `odonyme_recompose_normal`
3. Then `odonyme_recompose_court`
4. Street match must be exact after normalization
5. Do not fuzzy-match street names

### Postal fill rule

Fill only if:

1. civic number matched
2. street matched
3. locality matched
4. the resulting postal code is unique

“Postal code is unique” means:

- multiple matched rows are acceptable if they all have the same postal code
- do not fill if matched rows produce more than one distinct postal code

Safe example:

- row A -> `J0K 2Y0`
- row B -> `J0K 2Y0`

Result:

- fill `J0K 2Y0`

Unsafe example:

- row A -> `H1A 1A1`
- row B -> `H1A 1A2`

Result:

- do not fill

### Refusal rules

Do not fill when:

- no civic number
- no street text
- no city
- multiple possible postal codes remain
- source locality match is missing
- street match depends on fuzzy guessing

### Audit / report rules

Each processed row should be classified as one of:

- `matched_unique`
- `matched_same_postal_multiple_rows`
- `no_civic_number`
- `no_city`
- `no_street_match`
- `no_locality_match`
- `ambiguous_postal_code`

### First safe implementation

The safest first implementation should be:

1. QC only
2. mandatory civic number
3. exact normalized street match
4. city may match municipality, complete municipality name, arrondissement, then MRC/region only if earlier locality fields fail
5. fill only when one distinct postal code remains

### Input parsing

`liste_desj.street_address` is parsed into:

- civic number
- remaining street text

Example:

- `1131 39E AV CP331` -> civic `1131`, street `39E AV`

Noise like `CP331`, `RR 1`, `APP`, `APT`, `UNITE`, etc. is stripped during normalization.

### Deterministic behavior

Only exact local matches are written.

If no match exists:

- leave unresolved

If the same `(city_norm, civic_number_norm, street_norm)` maps to multiple postal codes:

- do not auto-fill

## Importer Tool

### File

[php/tools/import_codepostal_qc_lookup.php](/usr/local/var/www/megatron/php/tools/import_codepostal_qc_lookup.php)

### Purpose

Import `CSV.csv` into `public.codepostal_qc_lookup`.

### Usage

```bash
php /usr/local/var/www/megatron/php/tools/import_codepostal_qc_lookup.php /usr/local/var/www/megatron/php/runtime/codepostal/CSV.csv --truncate --batch-size=5000
```

### Behavior

- reads the CSV header
- uses:
  - `numero_municipal`
  - `code_postal`
  - `odonyme_recompose_long` or fallback `odonyme_recompose_normal`
  - `nom_municipalite` preferred over `nom_municipalite_complet`
- normalizes values
- batch upserts rows into `codepostal_qc_lookup`
- runs final uniqueness refresh

### Important implementation note

`nom_municipalite` must be preferred over `nom_municipalite_complet`.

Reason:

- `nom_municipalite_complet` often contains prefixes like `Municipalité de ...`
- `liste_desj.city` stores the plain municipality name
- using the complete admin label causes avoidable mismatches

## Filler Tool

### File

[php/tools/fill_liste_desj_postal_codes_local.php](/usr/local/var/www/megatron/php/tools/fill_liste_desj_postal_codes_local.php)

### Purpose

Fill missing `liste_desj_entry_activity.postal_code` values using only the local lookup table.

### Usage

Dry run:

```bash
php /usr/local/var/www/megatron/php/tools/fill_liste_desj_postal_codes_local.php --limit=500 --dry-run --report-file=/tmp/liste_desj_postal_dryrun_500.json
```

Real run:

```bash
php /usr/local/var/www/megatron/php/tools/fill_liste_desj_postal_codes_local.php --limit=500 --report-file=/tmp/liste_desj_postal_run_500.json
```

Offset batch:

```bash
php /usr/local/var/www/megatron/php/tools/fill_liste_desj_postal_codes_local.php --limit=500 --offset=500 --report-file=/tmp/liste_desj_postal_run_500_1000.json
```

Single serial:

```bash
php /usr/local/var/www/megatron/php/tools/fill_liste_desj_postal_codes_local.php --serial=78 --dry-run
```

### Default scope

- default province filter is `QC`
- writes only when a unique local match exists

## Repository Support Added

### Methods added in `ClientRepository`

- `ensureCodePostalQcLookupTable()`
- `truncateCodePostalQcLookupTable()`
- `upsertCodePostalQcLookupRows()`
- `refreshCodePostalQcLookupUniqueness()`
- `countCodePostalQcLookupRows()`
- `findCodePostalQcMatchesForKeys()`

### Existing method extended

`listListeDesjPostalCodeCandidates()` now accepts an optional province filter.

## Verification Already Performed

### Syntax verification

Confirmed:

- `php/tools/lib/liste_desj_postal_local.php`
- `php/tools/import_codepostal_qc_lookup.php`
- `php/tools/fill_liste_desj_postal_codes_local.php`
- `php/src/Repositories/ClientRepository.php`

all passed `php -l`.

### End-to-end local matching verification

Confirmed with real rows from `liste_desj` and real rows from `CSV.csv`.

Verified serials:

- `78`
- `171`

These matched correctly in dry-run mode after importer normalization was corrected.

### Bug found and fixed during implementation

#### Duplicate upsert collision

Problem:

- importer batches could contain duplicate keys inside one SQL insert
- Postgres rejected `ON CONFLICT DO UPDATE` for the same target row twice in one statement

Fix:

- deduplicate and aggregate rows inside `upsertCodePostalQcLookupRows()` before insert

#### City normalization mismatch

Problem:

- importer initially preferred `nom_municipalite_complet`
- that introduced values like `Municipalité de Sainte-Marcelline-de-Kildare`
- `liste_desj.city` used plain forms like `Sainte-Marcelline-de-Kildare`

Fix:

- importer now prefers `nom_municipalite`
- city normalization strips tokens like:
  - `MUNICIPALITE`
  - `VILLE`
  - `CANTON`
  - `PAROISSE`
  - `COMMUNAUTE`
  - `METROPOLITAINE`
  - `VILLAGE`

## Current Runtime State

At the time this file was written:

- full import of `CSV.csv` had been started
- row ingestion completed
- visible lookup row count reached about `3,461,565`
- importer was still running `refreshCodePostalQcLookupUniqueness()`

This means:

- the lookup table is populated
- the importer session may still be active
- postal-code fill runs should wait until that import process fully finishes

## How To Check If Import Is Finished

```bash
psql -h /tmp -p 5432 -U appsmith -d appsmith -c "select state, wait_event_type, wait_event from pg_stat_activity where datname='appsmith';"
```

Interpretation:

- if you still see an `active` row for the importer, wait
- if no importer session remains, proceed

Useful additional check:

```bash
psql -h /tmp -p 5432 -U appsmith -d appsmith -c "select count(*) from public.codepostal_qc_lookup;"
```

## What Is Left To Do

### 1. Wait for the importer to finish

Do not start batch fill while the importer still holds schema / update work on `codepostal_qc_lookup`.

### 2. Run a QC dry-run sample

Recommended first command:

```bash
php /usr/local/var/www/megatron/php/tools/fill_liste_desj_postal_codes_local.php --limit=500 --dry-run --report-file=/tmp/liste_desj_postal_dryrun_500.json
```

### 3. Review the report

Inspect:

```bash
sed -n '1,200p' /tmp/liste_desj_postal_dryrun_500.json
```

Check:

- matched rows look correct
- unmatched rows are plausible misses rather than parser mistakes

### 4. Run a small real batch

If the dry run looks good:

```bash
php /usr/local/var/www/megatron/php/tools/fill_liste_desj_postal_codes_local.php --limit=500 --report-file=/tmp/liste_desj_postal_run_500.json
```

### 5. Continue in batches

Example:

```bash
php /usr/local/var/www/megatron/php/tools/fill_liste_desj_postal_codes_local.php --limit=500 --offset=500 --report-file=/tmp/liste_desj_postal_run_500_1000.json
```

Then continue increasing offset.

### 6. Scale batch size only after confidence

Recommended order:

1. `500`
2. `2000`
3. `5000`

Only increase if:

- performance is acceptable
- reports look correct

## Suggested Resume Checklist For Another Chat

If this work is resumed in another chat, do this:

1. Read this file.
2. Confirm importer is finished.
3. Confirm lookup row count:
   ```bash
   psql -h /tmp -p 5432 -U appsmith -d appsmith -c "select count(*) from public.codepostal_qc_lookup;"
   ```
4. Run a dry-run sample:
   ```bash
   php /usr/local/var/www/megatron/php/tools/fill_liste_desj_postal_codes_local.php --limit=500 --dry-run --report-file=/tmp/liste_desj_postal_dryrun_500.json
   ```
5. Inspect report quality.
6. Run real batch if quality is acceptable.

## Explicit Non-Goals

Not part of this plan:

- Canada Post integration
- Google Places integration
- external APIs
- apartment/unit-based matching
- fallback matching paths
- silent fuzzy auto-corrections

## Quick Reference

### Full import

```bash
php /usr/local/var/www/megatron/php/tools/import_codepostal_qc_lookup.php /usr/local/var/www/megatron/php/runtime/codepostal/CSV.csv --truncate --batch-size=5000
```

### Dry run

```bash
php /usr/local/var/www/megatron/php/tools/fill_liste_desj_postal_codes_local.php --limit=500 --dry-run --report-file=/tmp/liste_desj_postal_dryrun_500.json
```

### Real run

```bash
php /usr/local/var/www/megatron/php/tools/fill_liste_desj_postal_codes_local.php --limit=500 --report-file=/tmp/liste_desj_postal_run_500.json
```