SUPABASE CARDS TABLE MIGRATION

Confirm you need this

• Card count in dashboard hc-metadata exceeds 200
• OR second JV partner needs isolated board
• OR cron write frequency causes page_versions bloat

Open Supabase SQL Editor

Go to: supabase.com/dashboard/project/eizwhjpclwpcbhwrztwm/sql/new

Run the CREATE TABLE migration

-- Cards table for athio-ops dashboard
CREATE TABLE IF NOT EXISTS cards (
  id TEXT PRIMARY KEY,
  dashboard_id UUID NOT NULL REFERENCES pages(id),
  title TEXT NOT NULL,
  board TEXT NOT NULL DEFAULT 'athio',
  owner TEXT NOT NULL,
  priority TEXT NOT NULL DEFAULT 'medium'
    CHECK (priority IN ('high','medium','low')),
  stage TEXT NOT NULL DEFAULT 'Backlog',
  maturity INTEGER NOT NULL DEFAULT 0
    CHECK (maturity BETWEEN 0 AND 5),
  deadline TEXT,
  time_budget TEXT,
  dependencies TEXT[] DEFAULT '{}',
  depends_on_cards TEXT[] DEFAULT '{}',
  min_outcome TEXT DEFAULT '',
  perfect_outcome TEXT,
  artifact_url TEXT,
  artifact_description TEXT,
  feeds_cards TEXT[] DEFAULT '{}',
  source_page TEXT DEFAULT '',
  source_quote TEXT DEFAULT '',
  source_type TEXT DEFAULT 'manual'
    CHECK (source_type IN ('transcript','manual','agent','cron','chat')),
  pbn TEXT[] DEFAULT '{}',
  pbn_url TEXT,
  notes TEXT DEFAULT '',
  goal TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  archived_at TIMESTAMPTZ,
  created_by TEXT DEFAULT 'system'
);

-- Indexes for common queries
CREATE INDEX idx_cards_dashboard ON cards(dashboard_id);
CREATE INDEX idx_cards_owner ON cards(owner);
CREATE INDEX idx_cards_board ON cards(board);
CREATE INDEX idx_cards_stage ON cards(stage);
CREATE INDEX idx_cards_deadline ON cards(deadline);

-- Card history (audit trail)
CREATE TABLE IF NOT EXISTS card_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  card_id TEXT NOT NULL REFERENCES cards(id),
  action TEXT NOT NULL,
  performed_by TEXT NOT NULL,
  details JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_card_history_card ON card_history(card_id);
CREATE INDEX idx_card_history_date ON card_history(created_at);

-- Card comments (threaded discussion)
CREATE TABLE IF NOT EXISTS card_comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  card_id TEXT NOT NULL REFERENCES cards(id),
  author TEXT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_card_comments_card ON card_comments(card_id);

-- Boards table
CREATE TABLE IF NOT EXISTS boards (
  id TEXT PRIMARY KEY,
  dashboard_id UUID NOT NULL REFERENCES pages(id),
  label TEXT NOT NULL,
  color TEXT NOT NULL DEFAULT '#B85C18',
  stages TEXT[] NOT NULL DEFAULT '{"Backlog","In Progress","Review","Done"}',
  owner TEXT NOT NULL,
  client_visible BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- RLS policies (service role bypasses, anon blocked)
ALTER TABLE cards ENABLE ROW LEVEL SECURITY;
ALTER TABLE card_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE card_comments ENABLE ROW LEVEL SECURITY;
ALTER TABLE boards ENABLE ROW LEVEL SECURITY;

-- Allow service role full access
CREATE POLICY "Service role full access" ON cards
  FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Service role full access" ON card_history
  FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Service role full access" ON card_comments
  FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "Service role full access" ON boards
  FOR ALL USING (true) WITH CHECK (true);

Export current cards from hc-metadata

Run this script from the athio-ops repo:

// scripts/migrate-cards-to-db.js
// Run: node scripts/migrate-cards-to-db.js

const { createClient } = require('@supabase/supabase-js')
require('dotenv').config({ path: '.env.local' })

const sb = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL,
  process.env.SUPABASE_SERVICE_ROLE_KEY
)

const DASHBOARD_ID = '2185821d-4418-4c99-96fd-e6f0af468a9a'

async function migrate() {
  // 1. Read current metadata
  const { data: page } = await sb
    .from('pages')
    .select('html_content')
    .eq('id', DASHBOARD_ID)
    .single()

  const metaMatch = page.html_content
    .match(/<script id="hc-metadata"[^>]*>([\s\S]*?)<\/script>/)
  const metadata = JSON.parse(metaMatch[1])
  const cards = metadata.cards || []

  console.log(`Found ${cards.length} cards to migrate`)

  // 2. Insert each card
  for (const card of cards) {
    const { error } = await sb.from('cards').upsert({
      id: card.id,
      dashboard_id: DASHBOARD_ID,
      title: card.title,
      board: card.board || 'athio',
      owner: card.owner,
      priority: card.priority || 'medium',
      stage: card.stage || 'Backlog',
      maturity: card.maturity || 0,
      deadline: card.deadline || null,
      time_budget: card.time_budget || null,
      dependencies: card.dependencies || [],
      depends_on_cards: card.depends_on_cards || [],
      min_outcome: card.min_outcome || '',
      artifact_url: card.artifact_url || null,
      feeds_cards: card.feeds_cards || [],
      source_page: card.source_page || '',
      source_quote: card.source_quote || '',
      source_type: card.source_type || 'manual',
      pbn: card.pbn || [],
      notes: card.notes || '',
      created_by: 'migration',
    }, { onConflict: 'id' })

    if (error) {
      console.error(`Failed: ${card.id}`, error.message)
    } else {
      console.log(`Migrated: ${card.id} — ${card.title}`)
    }

    // 3. Migrate history entries
    for (const h of (card.history || [])) {
      await sb.from('card_history').insert({
        card_id: card.id,
        action: h.action,
        performed_by: h.by || 'system',
        details: { date: h.date },
      })
    }
  }

  // 4. Migrate boards
  const boards = metadata.boards || {}
  for (const [id, board] of Object.entries(boards)) {
    await sb.from('boards').upsert({
      id,
      dashboard_id: DASHBOARD_ID,
      label: board.label,
      color: board.color,
      stages: board.stages,
      owner: board.owner || 'derek',
      client_visible: board.client_visible || false,
    }, { onConflict: 'id' })
    console.log(`Migrated board: ${id}`)
  }

  console.log(`\nDone. ${cards.length} cards migrated.`)
  console.log('Verify: SELECT count(*) FROM cards;')
}

migrate().catch(console.error)

Verify migration in Supabase

In SQL editor, run:

SELECT count(*) FROM cards;
SELECT id, title, owner, stage, maturity FROM cards ORDER BY created_at;

Count should match the number of cards in hc-metadata.

Update lib/cards/service.ts

Change from hc-metadata read/write to Supabase table read/write:

// BEFORE (hc-metadata):
// const page = await sb.from('pages').select('html_content')...
// const metadata = JSON.parse(...)
// return metadata.cards

// AFTER (cards table):
export async function getCards(dashboardId: string) {
  const sb = await createClient()
  const { data } = await sb
    .from('cards')
    .select('*')
    .eq('dashboard_id', dashboardId)
    .is('archived_at', null)
    .order('created_at', { ascending: false })
  return data || []
}

export async function createCard(dashboardId: string, card: Partial<Card>) {
  const sb = await createClient()
  const { data, error } = await sb.from('cards').insert({
    id: card.id || `card-${Date.now()}`,
    dashboard_id: dashboardId,
    ...card,
  }).select().single()
  if (error) throw error

  // Log history
  await sb.from('card_history').insert({
    card_id: data.id,
    action: 'created',
    performed_by: card.created_by || 'system',
  })
  return data
}

// Similar for updateCard, archiveCard...

Update dashboard JSX to fetch from API instead of metadata

Change card loading from reading hc-metadata to calling athio-ops API:

// BEFORE (reads from embedded JSON):
// const metadata = loadMetadata()
// const cards = metadata.cards

// AFTER (fetches from API):
useEffect(() => {
  fetch(API_URL + '/api/cards', {
    headers: getAuthHeaders()
  })
  .then(r => r.json())
  .then(setCards)
}, [])

Deploy + test

• Push athio-ops changes, verify Vercel deploys
• Republish dashboard JSX via NowPage
• Open dashboard — cards should load from DB, not metadata
• Create a card — verify it appears in Supabase cards table
• Drag a card — verify stage updates in DB
• Drop a transcript — verify new cards land in DB

Remove cards from hc-metadata (optional)

Once DB is verified, strip the cards[] array from the dashboard page's hc-metadata to reduce page size. Keep boards config in metadata for now.

Update crons to read from DB

The cron routes (EOD, stale, kickoff) currently read from hc-metadata. Update them to query the cards table directly. This is faster and doesn't require parsing HTML.

Final verification

• Dashboard loads cards from DB
• Card CRUD works end-to-end
• Ingestion creates cards in DB
• Crons read from DB
• Old hc-metadata cards are in page_versions backup
• Supabase table has RLS enabled

If anything breaks

Cards are still in the page_versions backup. To rollback:

1. Revert athio-ops lib/cards/service.ts to hc-metadata version (git revert)
2. Revert dashboard JSX to metadata-reading version (republish from backup)
3. Cards table stays in Supabase (no data lost) — just unused
4. Re-migrate when ready to try again