pages.laporte.cloud · twit-ads

TWiT-Ads v2 Rebuild

A5 — Rate Card & Sellable Catalog Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: superpowers:executing-plans (or subagent-driven-development). Checkbox steps.

Goal: Versioned rate cards: per-show midroll rates (downloads/episode, CPM, cost/episode) and catalog-product rates (billboard week, newsletter issue, social post, banner impression), effective-dated with append-only history; rate-card-flag gating; the resolution function B-phase copies onto order lines.

Architecture: products (data-driven catalog — Lisa's new production rate card must be enterable without code changes) plus two versioned rate tables, show_rates and product_rates, each unique on (target, effective_date), resolved by "latest effective_date ≤ as-of wins." Rates are COPIED onto order lines at booking (B-phase), so editing history never mutates existing orders — the legacy latest-always-wins trap is dead by construction. Idioms per A2–A4.

Global Constraints (as A1–A4; read ~/Projects/twit-ads/CLAUDE.md first)

Domain decisions (flag to Leo if wrong):

  1. Show rates carry downloads/episode + CPM + cost/episode as independent fields (UI offers CPM×downloads/1000 auto-fill for cost, but override allowed — matches legacy rate card).
  2. Products seeded by migration as data: Opening Billboard (per week), Newsletter (per issue), Social Media Post (per post), Banner (per impression). Product rates carry price + guaranteed impressions per unit. New/renamed products are UI operations, not migrations.
  3. Rate entries are editable/deletable with audit (typo repair) — safe because orders copy rates at booking; unique (target, effective_date) prevents ambiguity.
  4. The custom line type (live events etc.) has no catalog entry — it's free text + flat amount at order time (B4).

Task 1: Migration + rates store

Files: app/internal/db/migrations/00005_ratecard.sql, app/internal/rates/rates.go (+ testhelper + tests; truncate list grows: show_rates, product_rates, products, ...).

-- +goose Up
CREATE TABLE products (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text NOT NULL UNIQUE,
    unit_label text NOT NULL,             -- week | issue | post | impression
    active     boolean NOT NULL DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO products (name, unit_label) VALUES
    ('Opening Billboard', 'week'),
    ('Newsletter', 'issue'),
    ('Social Media Post', 'post'),
    ('Banner', 'impression');

CREATE TABLE show_rates (
    id                    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    show_id               bigint NOT NULL REFERENCES shows(id),
    effective_date        date   NOT NULL,
    downloads_per_episode int    NOT NULL CHECK (downloads_per_episode >= 0),
    cpm_cents             bigint NOT NULL CHECK (cpm_cents >= 0),
    cost_per_episode_cents bigint NOT NULL CHECK (cost_per_episode_cents >= 0),
    created_at            timestamptz NOT NULL DEFAULT now(),
    UNIQUE (show_id, effective_date)
);
CREATE TABLE product_rates (
    id                   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id           bigint NOT NULL REFERENCES products(id),
    effective_date       date   NOT NULL,
    price_cents          bigint NOT NULL CHECK (price_cents >= 0),
    impressions_per_unit int    NOT NULL DEFAULT 0 CHECK (impressions_per_unit >= 0),
    created_at           timestamptz NOT NULL DEFAULT now(),
    UNIQUE (product_id, effective_date)
);
-- +goose Down
DROP TABLE product_rates; DROP TABLE show_rates; DROP TABLE products;

Interfaces (types ShowRate{ID, ShowID, EffectiveDate, DownloadsPerEpisode int, CPMCents, CostPerEpisodeCents int64}, Product{ID, Name, UnitLabel string, Active}, ProductRate{ID, ProductID, EffectiveDate, PriceCents int64, ImpressionsPerUnit int}):

type Store struct{ Pool *pgxpool.Pool }
// SetShowRate (insert; unique-violation → error), UpdateShowRate, DeleteShowRate,
// ShowRateHistory(showID) (newest first), RateForShow(showID, asOf) (ShowRate, error) — latest effective<=asOf; ErrNoRate when none.
// Products: ListProducts, CreateProduct, UpdateProduct.
// SetProductRate/UpdateProductRate/DeleteProductRate/ProductRateHistory/RateForProduct — mirror the show functions.

Tests (the load-bearing one is version resolution): two entries (Jan 1 / Jul 1); asOf Jun 30 → Jan rate; asOf Jul 1 → Jul rate; asOf before Jan 1 → ErrNoRate. Plus history ordering, duplicate effective_date rejected, product CRUD. Red → implement → just test-db green → commit.

Task 2: Money helper + ratecard middleware

Files: app/internal/server/money.go (+test), middleware addition (+test).

Task 3: UI

Files: app/internal/server/rates_ui.go, templates ratecard.html (current card: per active show downloads/CPM/cost + per product price/impressions, each with effective date + History link), show_rates.html (history + add/edit/delete forms), product_rates.html (same per product + product create/edit at top). Deps gains Rates RateStore interface; home nav gains "Rate Card"; main.go wires. Audit entities show_rate/product_rate/product. Routes: GET /rates (view, requireUser); GET/POST /rates/shows/{id} + POST /rates/shows/{id}/{rateID}/delete (requireRatecard); same shape /rates/products/{id}; POST /rates/products (create product), POST /rates/products/{id}/edit (rename/active). Tests: view open to sales; mutations 403 without flag; add show rate with dollars input lands as cents; delete audits.

Task 4: Wire, E2E, close out

Decisions & deviations (executed 2026-07-06 by Kenobi/Fable 5)