Data architecture

Catalog Data Model

Department → type → subtype, modeled for a catalog that does not sit still

PostgreSQLSupabaseTypeScript

A Postgres schema designed around the real shape of a liquor catalog: departments, types, subtypes, variants, tags, images, with constraints strict enough to catch bad data and migrations loose enough to evolve.

Technical narrative

This is where the archive goes deeper than the homepage: architecture choices, operating constraints, and the decisions that make the product maintainable over time.

The hierarchy

Products classify along a three-level hierarchy. Department is stable (spirits, wine, beer, rtd). Type is per-department (whiskey, tequila, vodka…). Subtype is per-type and is where the vocabulary keeps growing: bourbon, rye, Tennessee, Scotch, Irish, Canadian, Japanese, American.

department: enum-like text column, ~4 stable values
primary_subtype: text, CHECK constraint per department; the enforcement surface
label_identity: distinct from subtype (e.g. whiskey bottle identity vs classification)
flavored_expression: boolean, separates e.g. flavored whiskeys from base expressions
review_state: review | storefront | hidden | archived; content moderation
storefront_status: gate for anon-visible rows, referenced by RLS
is_featured: boolean, drives homepage and merchandising surfaces
base_family: extra classification dimension for cross-type grouping

Variants and inventory

A product has one or more product_variants rows: same product, different size or bottling, each with its own price_cents and active flag. inventory is keyed by variant_id and stores on_hand plus a low_stock_threshold. Sellability on a product is computed from variant aggregates: active_variant_count, sellable_variant_count, total_on_hand. That lets the storefront sort by availability without denormalizing stock onto the product row.

Mobile read

Products stay central

Brand, department, variants, and inventory branch from one durable product record.

Inventory is queryable

Shelf, warehouse, and compliance data can change without rewriting storefront logic.

Promos are separate

Pricing and merchandising rules attach to the model instead of living inside page code.

Scroll the detail view if you want the full map. Variants sit between products and inventory; product_tags and product_images hang off products; orders join back through order_lines → variants.

The migration-driven CHECK constraint

Fourteen migrations in scripts/migrations/ have the shape 20260407_expand_primary_subtype_check_for_*.sql. Each one widens the CHECK on products.primary_subtype for a specific type as new subtypes arrive.

sql

-- representative: adding japanese_whisky to the whiskey subtype set
ALTER TABLE products
  DROP CONSTRAINT IF EXISTS products_primary_subtype_whiskey_chk;

ALTER TABLE products
  ADD CONSTRAINT products_primary_subtype_whiskey_chk
  CHECK (
    department <> 'spirits'
    OR primary_subtype IS NULL
    OR primary_subtype IN (
      'bourbon', 'rye', 'tennessee_whiskey',
      'scotch_whisky', 'irish_whiskey',
      'canadian_whisky', 'japanese_whisky',
      'american_whiskey'
    )
  );

Strictness for free at the database layer. The storefront never has to defend against a bad subtype.

Query patterns drive indexing

Indexes follow the real queries: storefront facets (department + primary_subtype + storefront_status), wizard matches (product_id on product_tags), admin search (trigram over brand + name), and stock-sorted listings (inventory.on_hand partial index on active variants).

bt-liquor/src/lib/product-types.tsTypeScript mirrors of department/type/subtype enums

Back to the archive Work With Me