database_migration.sql

-- SQL Migration: Add missing columns to posts table
-- This will add the columns that the application schema expects

ALTER TABLE posts
ADD COLUMN title TEXT,
ADD COLUMN content_snippet TEXT,
ADD COLUMN generated_content TEXT,
ADD COLUMN published BOOLEAN DEFAULT FALSE,
ADD COLUMN error TEXT;

-- If you want to verify the table structure after the migration:
-- \d posts  (in psql)
-- or
-- SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'posts';
-- SQL Migration: Extend posts table for queue system, scheduled posting, and images

-- Add new columns to posts table
ALTER TABLE posts
ADD COLUMN image_url TEXT,
ADD COLUMN image_asset_path TEXT,
ADD COLUMN topic TEXT DEFAULT 'AI_NEWS',
ADD COLUMN source TEXT,
ADD COLUMN scheduled_post_time TIMESTAMP,
ADD COLUMN queue_status TEXT DEFAULT 'PENDING',
ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- Create index for efficient queue queries
CREATE INDEX IF NOT EXISTS idx_queue_status_scheduled ON posts(queue_status, scheduled_post_time);
CREATE INDEX IF NOT EXISTS idx_topic ON posts(topic);

-- Optional: View for pending posts in queue
CREATE OR REPLACE VIEW pending_queue AS
SELECT id, title, topic, scheduled_post_time, queue_status, created_at
FROM posts
WHERE queue_status IN ('PENDING', 'DRAFT', 'SCHEDULED')
ORDER BY scheduled_post_time ASC, created_at ASC;