Data model

What this answers: what are the canonical domain entities, how do they relate, and what invariants hold?

The Prisma schema at prisma/schema.prisma is the source of truth. This page is the explanation.

For a field-by-field reference, see Reference → Prisma models.

Class diagram

classDiagram
    class Retailer {
      +id: uuid
      +name: string «unique»
      +slug: string «unique»
      +domain: string
      +scraperId: string «unique»
      +active: bool
    }

    class Product {
      +id: uuid
      +category: Category
      +brand: string
      +model: string
      +slug: string «unique»
      +cpuSocket?: string
      +ramType?: string
      +tdpWatts?: int
      +psuWattage?: int
      +specs: Json
      +imageUrl?: string
      +msrpUsd?: decimal
    }

    class Listing {
      +id: uuid
      +retailerId: uuid
      +productId?: uuid
      +url: string
      +retailerSku?: string
      +titleRaw: string
      +matchConfidence?: float
      +matchStatus: string
      +lastSeenAt: datetime
    }

    class ListingPrice {
      +id: uuid
      +listingId: uuid
      +priceUsd?: decimal
      +inStock: bool
      +scrapedAt: datetime
    }

    class Click {
      +id: uuid
      +clickToken: string «unique»
      +retailerId: uuid
      +listingId: uuid
      +source: string
      +ipHash?: string
      +createdAt: datetime
    }

    class Category {
      <<enum>>
      CPU
      GPU
      MOTHERBOARD
      RAM
      PSU
      CASE
      COOLER
      STORAGE
      LAPTOP
      PREBUILT
    }

    Retailer "1" --o "*" Listing : sells
    Product "1" --o "*" Listing : variants
    Product --> Category : category
    Listing "1" --o "*" ListingPrice : history
    Retailer "1" --o "*" Click
    Listing "1" --o "*" Click

Entities

Retailer

A Lebanese merchant whose listings 961tech indexes. M1 has 3 (PCAndParts, 961Souq, Macrotronics); target is 6-8 by end of M2 (#20).

scraperId is the slug used to look up the scraper module in src/scrapers/sites/. One scraper per retailer.

Product

A canonical SKU in 961tech's catalog (e.g., "Corsair RM850x"). Independent of any retailer — many retailers may carry it. Identified by category + brand + model + spec.

The typed columns (cpuSocket, ramType, tdpWatts, etc.) hold the most-queried specs for fast filtering and compatibility checks. The free-form specs: Json blob holds everything else (clock speeds, cache sizes, port counts) without bloating the indexed schema.

imageUrl is backfilled by the scraper pipeline — see Ingest pipeline. The first scraper that finds an image for a matched product wins.

Listing

One retailer's offer of a canonical Product. Key facts:

• productId is nullable. A scraped Listing may exist before the matcher resolves it (or may never resolve if the title is too ambiguous). matchStatus tracks this — see Listing lifecycle.
• (retailerId, url) is unique — re-scraping the same URL updates the existing row, never creates a duplicate.
• titleRaw preserves the retailer's original listing title; useful for matcher debugging and re-running matchers later.
• matchConfidence is a float 0-1 from the matcher; matchStatus summarises it (unmatched, weak, matched, manual).

ListingPrice

A historical price snapshot. Each scrape run inserts a new ListingPrice row (it does not overwrite). This drives:

• UC-F Price history charts (#8)
• UC-G Price drop alerts (#14)

Cascades on Listing delete — orphaned price history is meaningless.

The (listingId, scrapedAt desc) index supports the most common query: "latest price for this listing."

Click

Outbound deep-link record. Created when a user clicks "Buy" on a Listing (UC-2); the request lands on /api/go/r/[retailerId]/p/[listingId], gets a clickToken, the Click row is written, and the user is 302-redirected to the retailer.

source tracks where the click originated (product-detail, build-cart, etc.). ipHash is a salted hash for affiliate fraud detection — see src/app/api/go/r/[retailerId]/p/[listingId]/route.ts. The salt is IP_HASH_SECRET (env vars).

Will be reconciled against retailer-confirmed conversions for UC-L Affiliate reconciliation (#17).

Invariants

These rules aren't expressible in the schema but the code maintains them:

1. Exactly one Listing per (Retailer, URL) pair. Enforced by the @@unique([retailerId, url]) constraint.
2. A Listing's productId may be null but never invalid. The matcher only sets productId after the Product exists; cascade behaviour assumes this.
3. matchStatus and matchConfidence move together. matchStatus = 'matched' requires matchConfidence > 0.7 (threshold defined in src/lib/matching.ts). Below that, matchStatus = 'weak'.
4. ListingPrice is append-only. Never updated, only inserted. History is the source of truth.
5. Click.clickToken is single-use — once redeemed via S2S postback (#17) the conversion is recorded against it and the token can't be reused.

Future entities

These don't exist yet. When they land, this page updates in the same PR.

• Build — saved PC build. Cookie-persisted for Visitors, account-bound for Builders. Composes 8 nullable Product references (one per slot). See ADR-0003.
• User — added with Auth.js (#11). Has many Builds.
• Subscription — for UC-G price drop alerts (#14). Joins User to either Build or specific Products with a threshold.
• Conversion — written when an S2S postback redeems a Click. Powers UC-L.