# ADR-053: Per-Brand Counter Data Lives on recce.submissions — Not on counters.counter

## Status

Accepted, 2026-06-22.

## Status History

```yaml
status_history:
  - date: 2026-06-22
    status: Proposed
    changed_by: hkl
    reason: Formalising brand data placement decision for multi-brand counters
    changed_via: adr-kit (360lm)
  - date: 2026-06-22
    status: Accepted
    changed_by: hkl
    reason: Per-brand data (distributor, fy_sales, glow_sign, inshop) on recce.submissions; counters.counter is brand-agnostic
    changed_via: adr-kit (360lm)
```

## Context

A counter (retail outlet) can stock multiple brands. The `counters.counter` table records the counter itself (name, address, GPS, owner, contact). Some commercial fields (which distributor supplies that counter, annual sales figures, brand visibility metrics like glow signs and in-shop displays) are brand-specific: Counter X may have a glow sign for Brand A but not Brand B. Storing these on `counters.counter` would require either per-brand columns (Brand_A_glow_sign, Brand_B_glow_sign — schema explosion) or a separate `counter_brand_data` table. The insight was that per-brand commercial data is naturally tied to a Recce visit: it represents what the field agent observed about one brand at that counter on that visit date.

## Decision

Per-brand commercial data (`distributor`, `fy_sales`, `glow_sign`, `inshop`) is stored on `recce.submissions`, NOT on `counters.counter`. A counter's brand-level commercial profile is built by querying its latest Recce submissions per brand.

`counters.counter` stores only brand-agnostic data: physical address, GPS coordinates, owner name, contact number, channel type, `brand_codes TEXT[]` (which brands are stocked — membership only, no commercial data).

`brand_codes TEXT[]` on the counter records which brands the counter carries; the associated Recce submissions carry the observed commercial metrics for each brand on each visit.

**Decision Maker:** hkl

## Alternatives Considered

- **Store per-brand columns on counters.counter (e.g. brand_a_glow_sign, brand_b_fy_sales).** Rejected: schema grows quadratically as brands × metrics; adding a new metric requires ALTER TABLE; columns for brands the counter doesn't stock are always NULL.
- **Separate counter_brand_data table (counter_id, brand, metric, value).** Rejected: EAV (Entity-Attribute-Value) anti-pattern — querying is complex, type safety is lost; Recce submissions already capture brand-level observations in a structured schema.
- **Store latest per-brand values in counters.counter via trigger (denormalized for fast query).** Rejected: adds trigger complexity to maintain denormalized state; the Recce submissions table is already indexed for per-counter per-brand queries; premature optimization.

## Consequences

**Positive:**
- `counters.counter` stays lean — only physical counter identity, no brand-specific fields.
- Per-brand commercial data has a natural timestamp (the Recce visit date) for trend analysis.
- Adding a new brand to the platform requires no schema change to counters.counter.
- Historical brand performance at a counter is preserved (multiple Recce submissions over time).

**Negative / Trade-offs:**
- Querying "current distributor for Counter X, Brand A" requires a subquery: latest Recce submission for (counter_id, brand) pair.
- If a counter never has a Recce submission for a brand, there is no commercial data — this is a data gap, not a code bug.

**Risks and mitigations:**
- Developer adds a brand-specific column to counters.counter: mitigated by this ADR and code review; the correct place is always recce.submissions.

## Related Decisions

- ADR-047 (Recce client visibility per-brand filter) — client users see submissions filtered by brand; this schema design enables that filter.
- ADR-041 (supersede-not-delete for engaged Recce records) — per-brand data is preserved in superseded records.

## References

- `memory/dbt_counters.md` — "Per-brand commercial data (distributor / fy_sales / glow_sign / inshop) is NOT in counters.* — it lives on recce.submissions"
- `counters/index.html` — brand_codes[] on counter; commercial data query via recce.submissions
- `recce/index.html` — per-brand submission fields