Skip to main content

Knowledge > Runbooks > Content Ops > Migrate Blog Posts to Supabase

Migrate Blog Posts from blog-posts.ts to Supabase articles Table

Move the 9 audited blog articles from the static TypeScript data file (src/lib/blog-posts.ts) into the production articles table in Supabase. This unblocks Email #2 (which links to the theological-traditions article) and enables database-driven article management going forward.

Prerequisites​

  • All 9 articles in src/lib/blog-posts.ts have been audited and fixed for accuracy
  • Supabase SQL access via Supabase dashboard or mcp__plugin_supabase_supabase__execute_sql MCP tool
  • Migration script: churchwiseai-web/scripts/migrate-blog-posts.ts

Context​

Current state:

  • Articles exist in src/lib/blog-posts.ts (static TypeScript objects)
  • Blog pages query src/lib/articles.ts, which reads from Supabase articles table
  • Result: Articles from blog-posts.ts are not visible. Email #2 links to a 404.

After migration:

  • Articles will be in Supabase articles table
  • src/lib/articles.ts will return them automatically
  • All links (Email #2, etc.) will work
  • Future articles can be managed via database or admin interface

Steps​

1. Create the articles Table Schema (if it doesn't exist)​

Run this SQL in the Supabase SQL editor or via MCP tool:

CREATE TABLE IF NOT EXISTS public.articles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  excerpt TEXT,
  content TEXT,
  status TEXT NOT NULL DEFAULT 'published' CHECK (status IN ('draft', 'published', 'archived', 'scheduled')),
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  read_time INTEGER DEFAULT 5,
  
  -- Author as JSONB
  author JSONB DEFAULT NULL,
  
  -- Category as JSONB
  category JSONB DEFAULT NULL,
  
  -- Tags as JSONB array
  tags JSONB DEFAULT NULL,
  
  -- Featured image
  featured_image_url TEXT,
  featured_image_alt TEXT,
  
  -- SEO metadata as JSONB
  seo JSONB DEFAULT NULL,
  
  -- Indexes for common queries
  CONSTRAINT articles_content_not_empty CHECK (content IS NOT NULL AND content <> '')
);

-- Create indexes for common queries
CREATE INDEX IF NOT EXISTS idx_articles_slug ON articles(slug) WHERE status IN ('published', 'archived');
CREATE INDEX IF NOT EXISTS idx_articles_published ON articles(published_at DESC) WHERE status IN ('published', 'archived');
CREATE INDEX IF NOT EXISTS idx_articles_status ON articles(status);

Verify:

SELECT column_name, data_type FROM information_schema.columns 
WHERE table_name = 'articles' 
ORDER BY ordinal_position;

2. Run the Migration Script​

From C:\dev\churchwiseai-web:

pnpm tsx scripts/migrate-blog-posts.ts

The script will:

  1. Read all articles from src/lib/blog-posts.ts
  2. Transform each into Supabase format (JSONB for author, category, tags, seo)
  3. Insert into articles table via SUPABASE_SERVICE_ROLE_KEY
  4. Log success/failure for each article
  5. Output a summary report

Expected output:

✓ Migrating 9 blog posts to Supabase articles table...

✓ Article: anthropic-ai-safety-and-christian-conscience
✓ Article: five-questions-ai-provider-safety
✓ Article: theological-traditions-and-ai
... (6 more articles)

✓ Migration complete! 9 articles inserted.
✓ Verify: SELECT COUNT(*) FROM articles WHERE status = 'published';

3. Verify the Migration in Supabase​

In the Supabase dashboard SQL editor:

-- Count articles
SELECT COUNT(*) FROM articles WHERE status = 'published';

-- Check specific article
SELECT slug, title, published_at, read_time FROM articles WHERE slug = 'theological-traditions-and-ai';

-- List all article titles
SELECT slug, title, published_at FROM articles WHERE status IN ('published', 'archived') ORDER BY published_at DESC;

Expected results:

  • Count: 9
  • theological-traditions-and-ai should be published and visible
  • All 9 articles should have correct titles and dates

In your local dev environment or deployed staging:

GET /blog/theological-traditions-and-ai

Expected result: Article loads with full content, no 404.

5. Verify Blog Page Functions​

Test in a browser:

  1. Index page: GET /blog

    • Should display all 9 articles
    • Newest first
    • Excerpt, title, read time visible

  2. Article detail: GET /blog/theological-traditions-and-ai

    • Full content loads
    • Author, category, tags visible
    • Featured image displays (if present)

  3. Search: Search for "theological"

    • Article appears in results

  4. Category filter: Filter by "AI Ethics" or "Theology"

    • Relevant articles appear

6. Optional: Add Featured Images Post-Migration​

The articles table supports featured_image_url and featured_image_alt. If you want to add featured images:

  1. Upload images to Supabase Storage (or host externally)
  2. Update articles with image URLs:
UPDATE articles 
SET featured_image_url = 'https://your-cdn.com/image.jpg',
    featured_image_alt = 'Image description'
WHERE slug = 'article-slug';

Rollback​

If migration fails or you need to reverse:

-- Delete all migrated articles
DELETE FROM articles WHERE status IN ('published', 'archived');

-- OR drop the entire table if needed
DROP TABLE IF EXISTS articles;

Then restore articles from blog-posts.ts and diagnose the issue.

Next Steps​

  1. Update Remotion graphics pipeline — Now that articles are in Supabase, blog graphics (ScriptureCard, StatCard components) can be generated and stored as media assets
  2. Archive old blog-posts.ts — Once verified, move src/lib/blog-posts.ts to a docs/archive/ folder
  3. Build article admin dashboard — UI to create/edit/delete articles without code
  4. SEO optimization — Add Open Graph images, structured data, meta tags per article
  • knowledge/runbooks/technical-ops/supabase-migration.md — General Supabase migration process
  • knowledge/runbooks/content-ops/ — Other content management runbooks
  • churchwiseai-web/src/lib/articles.ts — How articles are queried
  • churchwiseai-web/src/lib/blog-posts.ts — Source data being migrated