5f6d24c550
- Fixed database.py line 479: Changed 'CURRENT_TIMESTAMP' string to actual datetime - Added datetime import for proper UTC timestamp generation - This fixes the PostgreSQL error: invalid input syntax for type timestamp - Added migration file for campaign_status table 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude <noreply@anthropic.com>
60 lines
2.1 KiB
SQL
60 lines
2.1 KiB
SQL
-- Migration: Add campaign_status table
|
|
-- Purpose: Track campaign processing and live status to prevent duplicate webhooks
|
|
-- Date: November 5, 2025
|
|
|
|
\echo 'Creating campaign_status table...'
|
|
|
|
CREATE TABLE IF NOT EXISTS campaign_status (
|
|
-- Primary Key
|
|
id SERIAL PRIMARY KEY,
|
|
|
|
-- Campaign Identification
|
|
campaign_id VARCHAR(255) UNIQUE NOT NULL, -- DAM campaign folder ID (unique!)
|
|
campaign_number VARCHAR(50) NOT NULL, -- C000000078
|
|
campaign_name VARCHAR(500) NOT NULL,
|
|
|
|
-- Live Status
|
|
live_campaign VARCHAR(3) NOT NULL, -- 'YES' or 'NO'
|
|
status VARCHAR(10) NOT NULL, -- A1, A2, A4, A5, B1, B2, etc.
|
|
|
|
-- Webhook Tracking
|
|
webhook_sent BOOLEAN DEFAULT FALSE,
|
|
webhook_sent_at TIMESTAMP,
|
|
|
|
-- Timestamps
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
\echo 'Table created'
|
|
|
|
-- Create indexes
|
|
CREATE INDEX IF NOT EXISTS idx_campaign_status_campaign_id ON campaign_status(campaign_id);
|
|
CREATE INDEX IF NOT EXISTS idx_campaign_status_number ON campaign_status(campaign_number);
|
|
CREATE INDEX IF NOT EXISTS idx_campaign_status_status ON campaign_status(status);
|
|
CREATE INDEX IF NOT EXISTS idx_campaign_status_live ON campaign_status(live_campaign);
|
|
CREATE INDEX IF NOT EXISTS idx_campaign_status_webhook_sent ON campaign_status(webhook_sent);
|
|
|
|
\echo 'Indexes created'
|
|
|
|
-- Create trigger for auto-updating updated_at
|
|
CREATE TRIGGER update_campaign_status_updated_at
|
|
BEFORE UPDATE ON campaign_status
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
\echo 'Trigger created'
|
|
|
|
\echo ''
|
|
\echo '============================================================'
|
|
\echo 'Migration 002 complete!'
|
|
\echo '============================================================'
|
|
\echo 'Table: campaign_status'
|
|
\echo 'Indexes: 5'
|
|
\echo 'Triggers: 1'
|
|
\echo ''
|
|
\echo 'Purpose: Tracks campaign processing and live status'
|
|
\echo ' - Prevents duplicate A1→A2 processing'
|
|
\echo ' - Prevents duplicate A4 webhooks'
|
|
\echo ' - Tracks which campaigns are going live (YES/NO)'
|
|
\echo '============================================================'
|