Legacy TWiT Ad-Sales System — Analysis

Source: full code review of ~/Projects/twit-ads/site_extract/wwwroot/ and the schema export in ~/Projects/twit-ads/AIGEN_sql_format_helper/. Compiled 2026-06-11.

What it is

A custom-built internal web app (circa 2014, evolved through ~2025) that runs TWiT's entire podcast advertising operation: rate cards → broadcast orders → episode ad scheduling → ad copy → daily rundown PDFs → download/listener reporting → host fees. One Azure-hosted monolith plus a SQL Server database where most of the business logic actually lives.

Tech stack

~83 ASPX pages, ~30 VB code-behinds, 3 MVC API controllers (BO, Rundown, adsched), 154 stored procedures, 13 SQL functions.

Domain model (the entities that matter)

• Show (SHOWS, 48 rows) — title, abbrev, parent/retired flags, recurrence via Telerik Appointments (iCal RRULE text)
• Episode (EPISODES, ~15k rows) — rec/pub dates & times, cancelled flag, media week/quarter/year denormalized from BroadcastCalendar
• Advertiser (Advertisers, 587) — essentially just a name + a placeholder_id pointing at a default ad used to fill unsold slots
• Advertisement (Advertisements, 4.6k) — ad copy record: title, two "lower third" text lines, rundown blurb, billboard HTML, and crucially copy = a Google Docs file ID. The actual copy text is never stored in the DB.
• Promo_Codes (90k) — per-(ad, show) promo code / URL / extra text. promo_id doubles as the PDF cache key.
• Rate card (Rates, 526) — per show: ad units/ep, downloads/ep, impressions/ep, CPM, cost/ep; versioned by as_of, latest always wins
• Broadcast Order (bo_header2, 30k; bo_lines2, 181k; bo_line_dates2, 1.2M) — the sales contract: advertiser, quarters+year, salesperson, broker, terms (Net30/60/90), makegood linkage, notes. Lines pick a show + specific episode dates + units + CPM/cost.
• EP_ADS (22k) — the actual placement: episode × position (integer slot 1..max_ads) × ad, back-referenced to the BO line that sold it
• Banners (bo_banners2, banner_rates) — separate web-display inventory sold by week with % impressions
• Specials — hardcoded to show_id=32 (News)
• HF schema — host fee tracking (hosts, fees, ep_fees, monthly_fees) with its own setup/report pages
• Stats — pt_dl (3.6M rows, Podtrac downloads), Livenums (5M rows, 15-min live-viewer samples), yt_dl (YouTube views), surv.results (1.6M survey answers on a separate legacy DB)

Naming convention warning: suffix 1/2 = newer production version (bo_header2 is live, bo_header is dead); bak_*, bu_*, *temp, test* litter the schema. Of ~146 tables, maybe 40 are live.

Core workflows

1. Order entry (BO lifecycle)

BOs.aspx list → newbo_int.aspx creates an interim draft (timestamp+uid keyed) → newBO.aspx editor: pick advertiser, quarters/year, terms; add line items per show (episode dates from ep_dates proc, rates auto-filled from latest rate card, CPM/cost overridable); optional banner lines. Draft lives in *_interim tables so multiple drafts coexist.

Finalize (role-gated): bo_ep_ads enumerates episodes covered → AVAIL_POS finds the next free slot per episode (simple round-robin, no premium-position logic) → INS_EP_AD writes placements → header accepted timestamp set, order locks. Editing after finalize requires edit_exist_bo/Admin_all roles. Deletion only for future-dated orders. Sentinel value '7/15/1991' is used as "null accepted date" in several places.

Outputs: HTML print view (print_bo.aspx), Excel export (/api/BO/dlexcbo), Telerik yearly-totals report.

2. Episode & ad scheduling

Shows have RRULE recurrences (Telerik RadScheduler on Appointments); episodes are generated from these (generation code not in the extract — likely a job or manual proc run). ep_date_move / cancel_ep handle changes (role Edit_Ep). Inventory views (Inventory.aspx, upcoming.aspx, byadvertiser.aspx) show fill rates: episodes × slots, ads remaining, % full. ad_forecast projects capacity.

3. Ad copy → rundown (the most load-bearing and fragile flow)

• Copy is authored in Google Docs; the DB stores only the file ID.
• A Google Apps Script hosted on jeffn@twit.tv's account clones the doc, substitutes {SHOW_TITLE} / {PROMO_CODE} / {PROMO_URL} / {PROMO_OTHER} placeholders, and returns the new doc ID.
• The server exports it to PDF via a deprecated Google Docs export endpoint, caches in /PDFs/ keyed by promo_id, tracks in rundown_files2 (58k rows).
• RD2.aspx + RundownController assemble the daily rundown: iTextSharp cover page + per-episode Leo page + every ad's PDF merged into Rundown_YYYYMMDD.pdf. This is what hosts read on air.
• Cache invalidation = comparing Google Drive modifiedDate against the DB, client-side, with a hardcoded API key.

4. Stats & reporting

• Podtrac: no API — pt/scrape.aspx screen-scrapes the Podtrac publisher dashboard with a hardcoded auth cookie, manually triggered by button click. pt_merge normalizes into pt_dl; reports aggregate 3/7/30-day download windows.
• Live viewers: Livenums gets 15-min samples from multiple stream sources (ice/shout/etc.); Highcharts dashboard.
• YouTube: yt_dl view counts via scrape_yt procs (using spHTTPRequest — HTTP calls from inside SQL Server).
• Reports: by episode / advertiser / show / month (stats_byep2, stats_monthly, stats_byadv), ad-delivery vs. BO comparisons (Compare_BO_DL.sql).

5. Listener survey

Separate surv schema on a different, older SQL server (survCS connection, sa login). 1.6M responses, per-show frequency/satisfaction questions, dynamic pivot tables (surv.sf{n}) built per saved filter. SurveyMonkey API package installed but apparently unused — looks like batch imports.

6. Marketing kit

Drag-and-drop PDF builder (Marketing_Kit/print_kit.aspx) assembling show pages, host bios, and uploaded PDFs into advertiser-facing kits.

Roles (ASP.NET Identity, 17 defined, ~47 users)

Admin_all, Finalize_BO, edit_exist_bo, Edit_Ep, editrd (rundowns), view_stats, podtrac, plus standard sales users. Salespeople/brokers are data rows (salespeople, brokers), not login-linked in any deep way. Broker field is stored but drives no commission logic.

External dependencies (every one a rewrite decision)

1. Google Docs/Drive — ad copy CMS + the Apps Script on a personal staff account + deprecated export API + API key hardcoded in client JS
2. Podtrac — screen-scraping with a baked auth cookie
3. Google OAuth — staff login
4. Azure Blob — mp3 ad asset uploads (Telerik RadCloudUpload)
5. YouTube — scraped view counts
6. Telerik — UI is unextractable from it
7. Local filesystem /PDFs/ — rundown cache, not in blob storage

Notable hazards & debt

• SQL injection via string concatenation in multiple pages (survey filter names, query-string IDs)
• Secrets hardcoded in source: Podtrac cookie, Google API key (client-side!), OAuth client ID; connection strings incl. an sa login in Web.config
• Business logic split across 154 procs with versioned-by-suffix evolution (rundown4, ep_dates3, mod_bo2...) — the name tells you the timeline, not the purpose
• Magic values: show_id=32 for specials, '7/15/1991' null-date sentinel, hardcoded -7/-8 timezone offsets
• No automated ETL — stats imports are button-clicks; no scheduler found
• No tests, no real audit trail (a mod_bo_log table and last_modified columns), no approval workflow beyond the finalize flag
• InProc sessions (can't scale out), New Relic dead, debug labels ("BBB", "HHHH") visible in production UI
• Schema is ~⅔ dead weight: bak_*/bu_*/*temp/test* tables, five backup copies of EPISODES alone

What the system actually does well (don't lose these)

• The interim/draft pattern: salespeople park half-built orders without colliding
• Per-show promo codes on one ad record — clean idea, heavily used (90k rows)
• Placeholder ads auto-filling unsold inventory
• The rundown as the single daily artifact hosts rely on — whatever replaces this must be at least as turnkey
• Rate card versioning by as_of date
• Makegood orders linked to parent BOs
• The broadcast-calendar (media week/quarter) framing that sales thinks in