SDD Technical Brief

01 — Problem Statement

The operational gap

Caio is a creative strategist at HW Network who writes direct-response ad scripts (hooks, microleads, VSL bodies) for the American 50+ supplement market. His workflow depends on a swipe file — a categorized database of competitor ads, reference creatives, and his own past work — classified using a proprietary taxonomy (ISA + Creative DNA).

Current pain: Notion databases cannot handle this data via API reliably. The Supabase Table Editor is too raw for daily operational use. Pedro (Creative Ops) needs a simple, browser-based interface to input, browse, filter, and manage 500–2000 creative entries daily. Caio needs the same data queryable via Claude Code (natural language) and Obsidian (local markdown reference).

Core insight: The database backend (Supabase) and the working interface (dashboard) are separate concerns. Supabase stores and serves. The dashboard is where humans work. Claude Code is where Caio queries.

02 — Users & Access Patterns

Three user archetypes

User 1: Pedro (Creative Ops) — Primary daily operator

• Actions: Create entries, categorize ads, update fields, filter/search, bulk tag
• Access: Browser (own computer), needs URL
• Technical level: Intermediate — comfortable with forms, dropdowns, filters. No code, no terminal
• Session pattern: 2–4 hours/day, entering 5–15 records, reviewing/filtering 20–50

User 2: Caio (Creative Strategist) — Query & oversight

• Actions: Search by taxonomy filters, review performance data, export subsets, analyze patterns
• Access: Claude Code via Supabase MCP (natural language queries), Obsidian (local reference), browser (dashboard)
• Session pattern: 30–60 min/day browsing, ad hoc queries via Claude Code

User 3: Future creative ops (scalability)

• Actions: Same as Pedro
• Access: Same browser URL, own login
• Note: Auth system should support multiple users from Day 1 even if Pedro is the only one initially

03 — Technology Stack

The stack

Key architectural decisions

Why NOT a low-code tool (Retool, Appsmith, NocoDB)

• Adds a dependency and a bill
• Caio’s UI/UX standards exceed what these tools produce
• Custom dark-theme aesthetic requires full CSS control
• Claude Code can scaffold the entire Next.js app in one session

Why NOT Obsidian Dataview as the primary interface

• Pedro would need to install Obsidian and learn Dataview
• YAML frontmatter data entry is error-prone
• No multi-user concurrent access
• No real-time sync between operators

Why Supabase over raw PostgreSQL

• MCP integration out of the box with Claude Code
• Auth + RLS built in
• Real-time subscriptions (future: live updates when Pedro adds entries)
• Dashboard auto-generated for admin-level debugging

04 — Data Model

Schema definition

Table: creatives

The core table. Each row = one ad creative (competitor or own).

CREATE TABLE creatives (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  created_by UUID REFERENCES auth.users(id),

  -- Identity
  title TEXT NOT NULL,
  source_url TEXT,
  source_platform TEXT CHECK (source_platform IN ('meta', 'youtube', 'tiktok', 'other')),
  local_file_path TEXT,
  thumbnail_url TEXT,

  -- Classification: ISA Taxonomy
  hook_type TEXT CHECK (hook_type IN (
    'curiosity', 'fear', 'social_proof', 'conditional', 'imperative',
    'defeat', 'result', 'walkthrough', 'open_loop', 'question',
    'contrast', 'authority_claim'
  )),
  hook_technique TEXT CHECK (hook_technique IN (
    'tratamento_errado', 'cena_universal', 'mecanismo_direto',
    'burden_emocional', 'discovery_first', 'friendly_fire',
    'resultado_prova', 'confissao'
  )),
  opening_device TEXT CHECK (opening_device IN (
    'question', 'statement', 'conditional', 'command',
    'story', 'statistic', 'contrast'
  )),

  -- Classification: Content
  mechanism TEXT CHECK (mechanism IN (
    'ingredient', 'process', 'discovery', 'deficiency',
    'combination', 'lifestyle_change'
  )),
  tone TEXT CHECK (tone IN (
    'urgent', 'empathetic', 'authority', 'conversational',
    'alarmist', 'confessional', 'scientific', 'conspiratorial'
  )),
  cta_type TEXT CHECK (cta_type IN (
    'quiz', 'free_report', 'doctor_consult', 'video',
    'direct_purchase', 'limited_offer'
  )),
  visual_format TEXT CHECK (visual_format IN (
    'green_screen', 'podcast_clip', 'car_confession',
    'walking_talking', 'screen_recording', 'ugc_testimonial',
    'broll_vo', 'slideshow'
  )),

  -- Classification: Quality
  quality_tag TEXT CHECK (quality_tag IN ('strong', 'medium', 'weak')),
  usability TEXT CHECK (usability IN (
    'hook_only', 'full_structure', 'angle_inspiration',
    'voc_source', 'visual_reference'
  )),

  -- Content
  copy_angle TEXT,
  first_15s_transcription TEXT,
  key_phrases TEXT[],
  full_transcription TEXT,
  notes TEXT,

  -- Metadata
  duration_seconds INTEGER,
  niche TEXT CHECK (niche IN (
    'weight_loss', 'joint_pain', 'brain_health',
    'gut_health', 'blood_sugar', 'heart_health',
    'general_wellness', 'other'
  )),
  offer TEXT,
  language TEXT DEFAULT 'en',

  -- Performance (for own creatives)
  is_own_creative BOOLEAN DEFAULT false,
  ctr DECIMAL(5,2),
  cpm DECIMAL(8,2),
  hook_rate DECIMAL(5,2),
  vturb_retention_15s DECIMAL(5,2),
  vturb_retention_30s DECIMAL(5,2),
  vturb_retention_60s DECIMAL(5,2),
  spend DECIMAL(10,2),
  status TEXT CHECK (status IN (
    'draft', 'in_production', 'live', 'analyzing', 'iterate', 'dead'
  )),

  -- Tags (flexible)
  tags TEXT[]
);

-- Indexes for common query patterns
CREATE INDEX idx_creatives_hook_type ON creatives(hook_type);
CREATE INDEX idx_creatives_niche ON creatives(niche);
CREATE INDEX idx_creatives_offer ON creatives(offer);
CREATE INDEX idx_creatives_quality ON creatives(quality_tag);
CREATE INDEX idx_creatives_is_own ON creatives(is_own_creative);
CREATE INDEX idx_creatives_status ON creatives(status);
CREATE INDEX idx_creatives_created ON creatives(created_at DESC);
CREATE INDEX idx_creatives_tags ON creatives USING GIN(tags);
CREATE INDEX idx_creatives_key_phrases ON creatives USING GIN(key_phrases);

-- Full text search on transcriptions and copy angle
ALTER TABLE creatives ADD COLUMN fts tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english',
      coalesce(title, '') || ' ' ||
      coalesce(copy_angle, '') || ' ' ||
      coalesce(first_15s_transcription, '') || ' ' ||
      coalesce(notes, '')
    )
  ) STORED;
CREATE INDEX idx_creatives_fts ON creatives USING GIN(fts);

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER creatives_updated_at
  BEFORE UPDATE ON creatives
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Table: offers

Lookup table for active offers.

CREATE TABLE offers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  niche TEXT NOT NULL,
  target_demographic TEXT,
  funnel_type TEXT,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now()
);

Table: briefings

Links creatives to writing sessions.

CREATE TABLE briefings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ DEFAULT now(),
  created_by UUID REFERENCES auth.users(id),
  offer_id UUID REFERENCES offers(id),
  title TEXT NOT NULL,
  piece_type TEXT CHECK (piece_type IN ('hook', 'microlead', 'vsl_body', 'full_vsl')),
  direction_notes TEXT,
  status TEXT CHECK (status IN ('pending', 'in_writing', 'review', 'done')) DEFAULT 'pending',
  reference_creative_ids UUID[],
  voc_entries JSONB,
  performance_context JSONB
);

Row Level Security

-- Enable RLS
ALTER TABLE creatives ENABLE ROW LEVEL SECURITY;
ALTER TABLE offers ENABLE ROW LEVEL SECURITY;
ALTER TABLE briefings ENABLE ROW LEVEL SECURITY;

-- All authenticated users can read all data
CREATE POLICY "Authenticated users can read creatives"
  ON creatives FOR SELECT TO authenticated USING (true);

CREATE POLICY "Authenticated users can insert creatives"
  ON creatives FOR INSERT TO authenticated WITH CHECK (true);

CREATE POLICY "Authenticated users can update creatives"
  ON creatives FOR UPDATE TO authenticated USING (true);

-- Same for offers and briefings
CREATE POLICY "read_offers" ON offers FOR SELECT TO authenticated USING (true);
CREATE POLICY "insert_offers" ON offers FOR INSERT TO authenticated WITH CHECK (true);
CREATE POLICY "update_offers" ON offers FOR UPDATE TO authenticated USING (true);

CREATE POLICY "read_briefings" ON briefings FOR SELECT TO authenticated USING (true);
CREATE POLICY "insert_briefings" ON briefings FOR INSERT TO authenticated WITH CHECK (true);
CREATE POLICY "update_briefings" ON briefings FOR UPDATE TO authenticated USING (true);

05 — Dashboard Features (CRUD)

The interface spec

5.1 Creative Entry Form (Create/Edit)

A multi-section form with:

Section 1: Identity

• Title (text input, required)
• Source URL (text input, auto-detect platform from URL pattern)
• Platform (auto-filled or manual select)
• Thumbnail (file upload to Supabase Storage, or URL)

Section 2: ISA Classification

• Hook type (dropdown with all 12 options)
• Hook technique (dropdown with all 8 options)
• Opening device (dropdown with all 7 options)
• All dropdowns should show human-readable labels, store snake_case values

Section 3: Content Classification

• Mechanism (dropdown, 6 options)
• Tone (dropdown, 8 options)
• CTA type (dropdown, 6 options)
• Visual format (dropdown, 8 options)

Section 4: Quality & Usability

• Quality tag (3-option selector: strong/medium/weak with color indicators)
• Usability (dropdown, 5 options)
• Niche (dropdown, 8 options)
• Offer (dropdown, populated from offers table)

Section 5: Content

• Copy angle (text input, 1 sentence)
• First 15s transcription (textarea)
• Key phrases (tag input — type and press enter to add)
• Notes (textarea, free-form)
• Duration (number input, seconds)

Section 6: Performance (conditional — only if is_own_creative is checked)

• Toggle: “This is our creative” (boolean)
• If true, show: CTR, CPM, Hook Rate, VTurb retention fields (15s/30s/60s), Spend, Status dropdown

5.2 Creative List View (Read)

The main working view. A filterable, searchable table/grid of all creatives.

Display columns (default)

• Thumbnail (small preview)
• Title
• Hook type (colored tag)
• Tone (colored tag)
• Quality (colored indicator)
• Offer
• Niche
• Duration
• Created date
• Status (if own creative)

Filters (sidebar or top bar)

• Hook type (multi-select)
• Tone (multi-select)
• Quality tag (multi-select)
• Niche (multi-select)
• Offer (multi-select)
• Visual format (multi-select)
• Is own creative (toggle)
• Status (multi-select, only when is_own_creative filter is on)
• Date range (created_at)

Search

Full-text search across title, copy_angle, first_15s_transcription, notes, key_phrases. Uses the fts tsvector column for fast search.

Sort

• By date (default: newest first)
• By CTR, hook_rate, spend (for own creatives)
• By quality tag

Pagination

25 items per page, cursor-based (Supabase range queries).

5.3 Creative Detail View

• All fields displayed (read mode)
• Edit button switches to form mode
• If has source_url → “Open original” link
• If has video file → embedded video player
• Performance chart (if own creative, show retention curve placeholder)
• Related creatives: “Similar hook type + same niche” (auto-query)
• “Use as reference” button → adds to a briefing

5.4 Bulk Operations

• Multi-select rows (checkbox)
• Bulk update: quality tag, offer, tags
• Bulk delete (with confirmation)
• Bulk export as CSV

5.5 Offers Management

Simple CRUD for the offers lookup table: Add/edit/archive offers. Each offer shows count of associated creatives.

5.6 Briefings View (Future Phase)

• List of briefings with status
• Click to see associated references, VOC, performance context
• Link to create new briefing, pulling in selected creatives as references

06 — Claude Code Integration

Natural language queries

Query patterns (via Supabase MCP)

"Show me all strong curiosity hooks for weight loss"
SELECT * FROM creatives
WHERE quality_tag = 'strong'
  AND hook_type = 'curiosity'
  AND niche = 'weight_loss'

"What are our top 5 performing hooks by CTR?"
SELECT title, ctr, hook_type, offer
FROM creatives
WHERE is_own_creative = true AND ctr IS NOT NULL
ORDER BY ctr DESC LIMIT 5

"Find ads that mention GLP-1 in the transcription"
SELECT * FROM creatives
WHERE fts @@ to_tsquery('english', 'GLP-1')

"Compare hook rate between curiosity and fear hooks on BurnSlim"
SELECT hook_type, AVG(hook_rate), COUNT(*)
FROM creatives
WHERE is_own_creative = true
  AND offer = 'BurnSlim'
  AND hook_type IN ('curiosity', 'fear')
GROUP BY hook_type

"What hooks have we not tried on CoreStrength?"
SELECT DISTINCT hook_type FROM creatives
WHERE niche = 'joint_pain' AND quality_tag = 'strong'
EXCEPT
SELECT DISTINCT hook_type FROM creatives
WHERE is_own_creative = true AND offer = 'CoreStrength'

Obsidian sync (optional, future)

A Claude Code agent or cron job can export filtered views as markdown. Each exported note = one creative with YAML frontmatter (for Dataview) + body content.

07 — File Structure

Next.js project layout

08 — Implementation Phases

Five-day roadmap

Phase 1 — Foundation (Day 1)

• Supabase project setup (or use existing)
• Run migration SQL (tables, indexes, RLS, triggers)
• Scaffold Next.js app with Supabase client
• Auth flow (login page, auth provider, protected routes)
• Deploy skeleton to Vercel
• Verify Claude Code can query via Supabase MCP

Phase 2 — Core CRUD (Days 2–3)

• Creative entry form (all 6 sections)
• Creative list view with basic table
• Creative detail view
• Offers CRUD page

Phase 3 — Filters & Search (Day 4)

• Filter sidebar with multi-select for all taxonomy fields
• Full-text search integration
• Sort controls
• Pagination

Phase 4 — Polish & Operations (Day 5)

• Bulk operations (multi-select, bulk tag, bulk delete)
• CSV export
• Auto-save on form
• Toast notifications
• Mobile responsiveness (Pedro may use tablet)

Phase 5 — UI/UX Application (Day 6)

• Apply Caio’s UI/UX briefing (color scheme, fonts, aesthetic)
• Dark theme implementation
• Tag color system for taxonomy fields
• Thumbnail previews
• Final visual polish

09 — Environment Variables

Configuration

NEXT_PUBLIC_SUPABASE_URL=https://xxx.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJ...
SUPABASE_SERVICE_ROLE_KEY=eyJ...          # Server-side only, never exposed to client

10 — Constraints & Non-Goals

Scope boundaries

Constraints

• Must work on modern browsers (Chrome, Firefox, Safari)
• Must be responsive (Pedro may use different screen sizes)
• Must handle 2000 entries without performance degradation
• Auth is required — no public access
• All data stays in Supabase (no local storage as source of truth)

Non-goals (v1)

• No real-time collaboration (multiple users editing same record)
• No video playback inside the dashboard (link out to source)
• No AI-powered auto-classification (future: Claude API could auto-fill ISA fields from transcription)
• No Obsidian sync automation (manual export via Claude Code for now)
• No mobile app (responsive web is sufficient)
• No briefings CRUD in v1 (future phase — briefings stay in Notion for now)

11 — Open Questions

Decisions pending

1. Supabase project: Use existing project or create a new one dedicated to creatives.?
2. Supabase Storage: Use it for thumbnails/video files, or just store URLs?
3. Domain: Custom domain (e.g. creatives.hwnetwork.com) or Vercel default URL?
4. Auth method: Email/password only, or add magic link?

12 — Handoff Notes

Ready for execution

This SDD is ready for Claude Code execution. The implementation sequence:

1. Feed this document to Claude Code as context
2. Provide the UI/UX briefing (color scheme, fonts, component aesthetic) as a second input
3. Claude Code runs the Supabase migration, scaffolds Next.js, implements CRUD, deploys to Vercel
4. Iterate on visual polish with the UI/UX briefing