# Phase 2 Notes — Product Database Architecture

## Design decisions worth flagging

**`product_relations` is one table, not nine.** The brief lists
`similar_products`, `alternative_products`, `compatible_products`, etc.
as separate entities. A relationship between two products is one
concept (`product_id`, `related_product_id`, `type`) regardless of which
type it is — nine tables with identical columns would violate the "no
duplicate systems" rule from Phase 1. Instead, `product_relations` has a
`type` enum, and `Product` exposes every named relationship as its own
typed accessor (`similarProducts()`, `alternativeProducts()`,
`compatibleProducts()`, `upgrades()`, `downgrades()`, `accessories()`,
`incompatibleProducts()`, `newerModels()`, `olderModels()`) — so calling
code never touches the `type` string directly, but there's only one
schema to maintain.

**Specs are four tables, not one EAV blob**, specifically so different
categories can have genuinely different schemas without touching
`products`:
- `category_attributes` — the global attribute dictionary (`ram`,
  `tonnage`, `cooling_capacity`, ...), defined once.
- `category_specifications` — which attributes apply to *this* category,
  in which group, in what order, filterable/comparable or not. This is
  what makes a Smartphone and an AC have completely different spec sets.
- `product_specifications` — the raw, as-entered value per product
  (`"5000 mAh"`).
- `specification_values` — the normalized numeric/text value split into
  its own table, so filtering/sorting (`RAM >= 8`) never parses
  `raw_value` strings at query time.

**Prices are normalized, never formatted strings.** `amount_minor` is an
unsigned bigint (paise), `currency` is an ISO code. `prices` holds one
current row per offer (upserted); `price_history` is append-only and
indexed on `(offer_id, recorded_at)` — this is the table expected to
grow fastest, per the Phase 1 architecture doc's scalability notes.

## Duplicate-detection strategy this schema supports

| Strategy (from the brief) | How it's implemented |
|---|---|
| Exact identifier matching | `product_identifiers` has a unique constraint on `(type, value)` — a duplicate GTIN/EAN/UPC is rejected at the database level, not just flagged later |
| Brand + MPN | Indexed via `products` `(brand_id, mpn)` |
| Brand + model | Indexed via `products` `(brand_id, model_number)` |
| Variant matching | Indexed via `product_variants` `(product_id, storage_gb, ram_gb, color, size, region)` |
| Fuzzy matching (later) | Deliberately not attempted in Phase 2 — needs a real similarity algorithm (e.g. trigram/Levenshtein) that belongs in the Ingestion module's import pipeline, not the schema itself |

## N+1 risk areas identified

See `tests/Feature/NPlusOneRiskTest.php` for the guarding tests. Four
access patterns are the highest risk as catalog size grows:

1. **Product listings** resolving `->brand` / `->category` per row —
   always eager load: `Product::with(['brand', 'category'])`.
2. **Product detail / spec resolution** is a 3-hop chain
   (`product → productSpecification → categorySpecification → attribute`,
   plus `→ value`) — always eager load with nested `with()`:
   `$product->load(['specifications.value', 'specifications.categorySpecification.attribute'])`.
3. **Comparison pages** multiply risk #2 by the number of products being
   compared — the Comparison module (Phase 4) must eager load all
   products' specs in one pass, not per product in a loop.
4. **Offer listings** resolving `->price` / `->merchant` per offer —
   eager load: `Offer::with(['price', 'merchant'])`.

None of these are fixed by a magic global setting — every controller/
service that lists products, specs, or offers in Phase 3+ needs to
consciously eager load along these chains. `Model::shouldBeStrict()` is
already enabled outside production (see `AppServiceProvider`), which
throws on accessing an unloaded relation in local/testing — this catches
the mistake in development rather than in a production slow-query log.

## What I could not verify in this environment

Same constraint as Phase 1: this sandbox has no network access to
Packagist and no PHP/Composer binary, so none of the migrations,
seeders, or tests below have actually been executed here. Everything
was written by hand and reviewed for correctness, but you need to run
the verification steps yourself.

## Running Phase 2

```bash
php artisan migrate:fresh
php artisan db:seed
```

This runs, in order: the Phase 1 foundation migrations, then all 11
Phase 2 migrations, then `DatabaseSeeder` → `CatalogDemoSeeder` →
`CommercialDemoSeeder` → `ProductRelationsDemoSeeder` →
`ComparisonDemoSeeder` → `RecommendationDemoSeeder`.

## Verifying it worked

```bash
php artisan tinker
>>> \App\Modules\Catalog\Models\Product::count()          // >= 20
>>> \App\Modules\Catalog\Models\Product::first()->variants
>>> \App\Modules\Catalog\Models\Product::first()->specifications->load('value')
>>> \App\Modules\Catalog\Models\Category::with('categorySpecifications.attribute')->get()
>>> \App\Modules\Pricing\Models\Offer::first()->priceHistory
```

Or run the automated checks:

```bash
php artisan test --filter=CatalogRelationshipsTest
php artisan test --filter=NPlusOneRiskTest
php artisan test --filter=DemoSeedersTest
```

All three files are new in Phase 2 — `CatalogRelationshipsTest` proves
core relationships and constraints, `NPlusOneRiskTest` proves the eager-
loading patterns above stay flat, `DemoSeedersTest` proves the seeded
volumes meet the brief's minimums (3 categories / 10 brands / 20
products / variants / specs / merchants / offers / price history /
comparisons / recommendation data).

## Known gaps / explicitly deferred (not started in Phase 2)

- No controllers, routes, or Blade views for any of this data yet — pure
  schema + models + demo data, per the phase scope.
- Fuzzy duplicate matching, the actual scoring algorithm, and the
  recommendation-weighting engine are stubbed with demo data only, not
  implemented — they're Ingestion/Scoring/Discovery module work in later
  phases.
- Filament admin resources for these models don't exist yet.