TWiT-Ads v2 Rebuild

TWiT-Ads v2 — Specification & Staged Build Plan

Sources: Legacy System Analysis, Rewrite Considerations, Interview Findings (Lisa + Debi, 2026-07-06). Open items in Follow-up Questions. This is the master spec; each build stage gets its own detailed implementation plan in ~/Projects/twit-ads/Plans/ written just-in-time before execution.

Mission-critical caveat: this system is the revenue workflow of the company. Every stage ships small, is verified before the next begins, and the legacy system keeps running in parallel until Lisa and Debi both sign off on cutover.


1. Product summary

A web-based insertion-order (IO) system for TWiT's ad sales, replacing the 2014 WebForms app. It manages: the sellable catalog → proposals → signed orders → episode ad placement (fair rotation) → ad copy → daily rundown → host fees, with inventory that is mathematically impossible to oversell. Seven staff users in two teams (Sales, Continuity) plus read-only rundown consumers. Numbers/delivery reporting, invoicing (QBO), and client reporting documents remain outside the system in v1.

Design tenets (from the interviews):

  1. No errors. Enter once, propagate everywhere. Errors cost money (makegoods).
  2. Proposals hold nothing; signatures hold everything. Inventory is only consumed by finalize.
  3. Everything sellable lives in the system — episode ads, billboard weeks, newsletters, social posts, banners, one custom line.
  4. The math is automated — softness/value-add deducts, discounts, net-to-TWiT. "Nobody can do math."
  5. Familiar screens, decluttered. Same conceptual pages (orders list, order editor, inventory, upcoming schedule, copy, rundown, host fees); everything dead removed; the word "broadcast" gone.
  6. Fast. Legacy is "just very slow." Server-rendered pages on Postgres with 7 users should feel instant.

2. Users & roles

Role People Can
admin Leo Everything, user management, config
sales Lisa, Ty Build/edit/finalize orders, view everything, exports
continuity Debi, Sebastian, Veva Everything sales can, plus copy, rotation, rundowns, host fees, episode moves
ratecard (flag) Lisa, Debi Edit master rate card & catalog prices (flag on top of role)
rundown link Editors, Micah Read-only rundown web page via long-random-token URL, no login

Notes: role-gating is deliberately loose within staff (any proposal builder can override prices per line — the human review step before send-out is the control). User provisioning must handle an arbitrary number of salespeople (more coming). Auth: local username/password, built as the possibly-permanent mechanism — Leo is not certain he wants Google SSO (decide after testing); keep the auth layer pluggable so SSO is an add, not a rework. All authored records carry created_by/updated_by.

3. Domain model

Names in code should follow these; plural snake_case tables, singular Go types.

Historic data (scope change, Leo 2026-07-06): the original "keep nothing" decision is reversed — "I think we will want historic data, so be prepared to import the historic data under the new schema." Consequences baked in now: imported entities carry a nullable legacy_id (+ source table) provenance column; imported historic orders/placements are flagged imported and read-only; the schema export in AIGEN_sql_format_helper/ defines the mapping, but the actual import (stage E0) needs a fresh data dump from the live SQL Server (via Paul/Azure) at import time.

Money rule (Leo, 2026-07-06): TWiT never uses cents — every dollar amount is a whole dollar. Storage stays integer cents (multiples of 100) so intermediate arithmetic is exact, but inputs round to the nearest dollar on parse, every computed amount (line values, deducts, discounts, commissions, net-to-TWiT) rounds to the nearest whole dollar at each step, and displays show whole dollars with thousands commas, right-justified. Impressions are integers with commas. Dates are civil dates in America/Los_Angeles, always displayed MM/DD/YYYY (Leo, 2026-07-06) (episodes have no meaningful time-of-day for sales purposes). No magic sentinels — nullable columns and enums instead of 7/15/1991 and show_id=32.

Order math (the contract)

gross subtotal      = Σ all lines at stated value (value-add lines included at full value)
value-add deduct    = Σ value-add lines (dollars AND impressions)
revised total       = gross subtotal − value-add deduct        ← "layered in for softness"
order discount      = % or flat $, applied to revised total (money only, never impressions)
final total         = revised total − discount
guaranteed impressions = Σ paid-line impressions only
agency commission   = final total × agency.commission% (if agency)
net to TWiT         = final total − commission

Every quantity in this contract is rounded to the nearest whole dollar at each step (house rule — no cents, ever; percentages like the discount and commission round after applying). Presentation lumped: value-add lines print inline zeroed ($0 / 0 impressions), no deduct block. Presentation below_the_line: lines print at full value with the deduct block beneath. Same stored data, two renderings. This math is pure-function Go with exhaustive table-driven tests — the single most-verified code in the system.

Availability & the sea of red

4. Workflows (acceptance narratives)

Each stage's acceptance tests are drawn from these.

W1 — Lisa builds three proposals. New order → advertiser (create inline; later edits its name freely) → agency ARM, salesperson Lisa, title "Recommended" → add Intelligent Machines → date picker shows availability → picks 4 dates → rates auto-fill from card, she bumps CPM down on one line → adds a billboard week + newsletter → marks two IM episodes value-add → 5% order discount → terms net-45 (boilerplate auto-fills with advertiser name) → duplicates the order twice, edits into "Minimum" and "Option B" → prints PDF of each → sends via DocuSign herself. Inventory unchanged. Same dates appear in a competitor's proposal freely.

W2 — Signature race. Client B signs first; Debi finalizes B. Lisa's proposal for client A shares two dates → A's row turns red in the list; opening it shows exactly which lines conflict; finalize refuses until she swaps dates; then finalizes. QBO handoff stays human (Jennifer gets the PDF + emails as today).

W3 — Debi intake from an agency. Builds the inbound IO in-system to check the math against the rate card; system totals expose the $1,000 discrepancy; Slack back-and-forth; stays pending until Lisa nods; finalize.

W4 — Fair rotation. On finalize, placements auto-fill round-robin. Debi opens Upcoming → show view, moves advertisers up/down across the year so nobody eats all last positions. Order close + monthly audit both take minutes.

W5 — Copy lifecycle. New copy Doc from client → continuity creates ad_copy with Doc ID + per-show codes/URLs → assignment rule (evergreen or date-window) → placements resolve copy automatically → morning attention list shows any placement in next N days without copy, or copy missing URL/code. Mid-campaign copy change = new ad_copy + updated assignment going forward; aired episodes keep history.

W6 — Rundown (shape confirmed by Debi's sample, reference/rundown-sample-2026-07-07.png). Keyed to the record day: "Daily Rundown Recorded on Tuesday, 7/7" lists each show recording that day as a block — record time window, show + episode number, TD — with one row per placement in position order: advertiser + copy number ("Bitwarden 48"), promo URL. Billboard reminders print as bold instruction lines above each affected show ("NEEDS RECORDED BLACK HAT BILLBOARD. Placement is immediately after the cold open and before the TWiT.tv network open."). Below the tables: links to each copy Doc ("Security Now — Cohesity 3"). Editors open a stable tokenized URL; Debi hits "copy as text" and pastes to Slack; data change → page reflects it immediately. The system also generates the per-show TD note text she schedules in Slack today (reference/rundown-slack-note-*.png): "@TD — Ads — MUST include Black Hat Billboard" + each ad's reader constraints ("Cohesity — do not mention competitors…", "Bitwarden — standard"). v1 generates the text for her to paste; posting to Slack automatically is phase F. No PDF anywhere.

W7 — Host fees. On the 1st, Sebastian opens last month, checkmarks who hosted each episode (pre-checked from show defaults), runs the report, exports, sends to Lisa + Jennifer.

W8 — Reports. Lisa: inventory % sold for Q3 (traditional calendar) → drill into TWiT to see who's on which episodes. Year-end: sales by advertiser with gross/commission/net-to-TWiT, exported to Sheets/XLSX, reconciled against QBO. Advertiser page: collapsible history, total spend, active years.

5. Screens (familiar-but-clean inventory)

  1. Orders — list, blue pending / green finalized, red conflict badges, filter by advertiser/status/year, advertiser rollups.
  2. Order editor — header, lines, date pickers, catalog add, softness/discount blocks, terms, notes; Print view; XLSX export; Duplicate.
  3. Inventory — % sold per show, arbitrary date range, drill-down to episode/slot occupancy.
  4. Upcoming schedule — per show, placements per episode, fair-rotation reorder, blank = unsold.
  5. Ad copy — copy records + per-show promo grid + assignments.
  6. Rundown — internal view + tokenized read-only page + copy-as-text.
  7. Attention (new) — morning list: conflicts, missing copy, missing URLs, unassigned upcoming slots.
  8. Host fees — setup + monthly report.
  9. Admin — shows/episodes, rate card, catalog, terms templates, users, audit log.

UI conventions: server-rendered, no SPA build step, htmx for in-page interactions (date picker, reorder, live totals), print stylesheets for the order PDF. Layout echoes the legacy screens (labels modernized: "Insertion Order", "New Order"). Every grid exports XLSX/CSV. Multiple tabs/windows always safe.

6. Architecture & stack

Explicitly out of v1 (phase-2 backlog): DocuSign API, Uniblab delivery import (unblocked — Uniblab has a complete API, per Leo 2026-07-06), Spotify impressions (no known automated path; the true-up/gross-up stays manual), client reporting document automation, invoicing/QBO integration, internal commission tracking, CRM depth, AI copy drafting, auto fair-rotation suggestions.

7. Engineering conventions (binding for every stage)

  1. TDD, red/green (superpowers:test-driven-development) — money math, availability, and finalize get exhaustive table-driven tests before implementation.
  2. One change at a time — build, test, confirm, next. No batching "for efficiency."
  3. Every stage ends: all tests green, docker compose up demo on Framework, stage checklist in the plan file ticked, Leo verifies before the next stage starts (UI stages: Lisa or Debi eyeball it too).
  4. Migrations are forward-only and numbered; schema changes never edit old migrations.
  5. No secrets in source; testbed secrets via env file, production via the Bitwarden-materialized pattern.
  6. Audit log entries for every mutation of orders, placements, rate cards, episodes.
  7. Git repo local + NAS remote (never public GitHub).

8. Staged build plan

Each stage = one to two focused Opus 4.8 sessions with its own just-in-time plan file (Plans/<stage>.md) written from this spec (superpowers:writing-plans), executed with TDD, and verified before proceeding. Stages are ordered so the system is demoable end-to-end as early as possible.

Phase A — Foundation

Phase B — Orders (the heart)

🚦 Checkpoint 1 (after B6): Lisa builds real proposals in the testbed alongside the legacy system for a week. Feedback folded in before Phase C.

Phase C — Continuity operations

🚦 Checkpoint 2 (after C5): full continuity workflow runs in parallel for one real order end-to-end.

Phase D — Reporting & hardening

Phase E — Production

Phase F (backlog, post-cutover)

DocuSign API · Uniblab delivery integration — top phase-2 candidate (Patrick, 2026-07-06: the API can report which advertisers ran on which episodes, and their log-processing analytics could feed delivery data straight into the system; he has "long wanted end-to-end data in a rewritten system." This would replace Debi/Veva's Friday manual Uniblab→spreadsheet copying: sold-vs-delivered per order line, in-system) · client reporting doc automation · invoice generation/QBO · internal commissions · MCP server · AI copy drafting · CRM-lite · Google SSO (if wanted) · scheduled Slack posting of rundown + TD notes (v1 generates the text; Debi pastes) · order checklist engine (Debi's new/existing-advertiser checklists — ~/Projects/twit-ads/reference/checklists.md — as in-app per-order task lists with attention-dashboard nudges) · Elroy integration (push URL/codes/sponsor data — kills today's triple entry; Elroy is the TWiTcast backend and Leo already has API access, so this is unblocked whenever wanted).

External systems map (from Debi's checklists + Leo, 2026-07-06): TwitAds is canon. Elroy = TWiTcast backend (sponsor pages, URL-code queue; API access in hand — phase-2 push target). AFB = not a saleable unit; ignore (checklist references are legacy noise). Lower thirds, banner graphics, and similar are generated independently (Kevin/Anthony; Patrick runs banner serving/reporting) — the system sells the banner line item but never touches graphics or serving. TNM-signed orders forbid direct client contact; host onboarding calls have their own ritual. v1's job in this ecosystem is exactly what the checklists show: finalize (task 1), fair rotation (task 10), promo URL/codes (task 11), copy into the system (tasks 13–14).

9. Risks & mitigations

Risk Mitigation
Oversell/double-book (the unforgivable) DB unique constraint + transactional finalize + concurrency tests in B5; property-based test hammering finalize
Money math wrong Pure-function core, golden tests from real orders (B1), Lisa reconciliation (D2)
Copy/rundown regression breaks shows C2–C3 verified against real days; parallel run; legacy stays available
Scope creep from wish lists Phase F backlog is the pressure valve; v1 scope frozen at this spec, changes go through Leo
Testbed data loss pg_dump to NAS nightly from A1; restore drill in D3
Opus session drift across stages Per-stage plan files + repo CLAUDE.md carry conventions; every stage starts by reading both