pg-schema.sql
1 -- ============================================================================= 2 -- 333 Method Automation Database Schema — PostgreSQL 16 3 -- 4 -- Converted from SQLite schema.sql (99+ migrations applied) 5 -- Source: db/schema.sql (reference schema generated from live SQLite DB) 6 -- 7 -- Usage: psql -d mmo -f pg-schema.sql 8 -- 9 -- Schemas: 10 -- m333 — core application tables 11 -- ops — operational tables (cron, pipeline control, settings, migrations) 12 -- tel — telemetry tables (metrics, agent system) 13 -- ============================================================================= 14 15 -- Required extension for case-insensitive text (email columns) 16 CREATE EXTENSION IF NOT EXISTS citext; 17 18 -- Create schemas 19 CREATE SCHEMA IF NOT EXISTS m333; 20 CREATE SCHEMA IF NOT EXISTS ops; 21 CREATE SCHEMA IF NOT EXISTS tel; 22 23 SET search_path TO m333, ops, tel, public; 24 25 26 -- ============================================================================= 27 -- m333 SCHEMA — Core Application Tables 28 -- ============================================================================= 29 30 -- ===== Core Pipeline Tables ===== 31 32 -- Primary site data — one row per prospect domain discovered via SERP scraping 33 CREATE TABLE m333.sites ( 34 id BIGSERIAL PRIMARY KEY, 35 domain TEXT NOT NULL, 36 landing_page_url TEXT NOT NULL, 37 keyword TEXT NOT NULL, 38 screenshot_path TEXT, 39 html_dom TEXT, 40 http_status_code INTEGER, 41 -- score_json removed in migration 121 (data stored at data/scores/{id}.json via score-storage.js) 42 score REAL, 43 grade TEXT, 44 scored_at TIMESTAMPTZ, 45 rescored_at TIMESTAMPTZ, 46 enriched_at TIMESTAMPTZ, 47 competitor_domain TEXT, 48 -- contacts_json removed in migration 121 (data stored at data/contacts/{id}.json via contacts-storage.js) 49 evidence_pass1_json JSONB, 50 evidence_pass2_json JSONB, 51 evidence_json JSONB, 52 city TEXT, 53 country_code TEXT, 54 last_outreach_at TIMESTAMPTZ, 55 status TEXT DEFAULT 'found' CHECK(status IN ( 56 'found', 'assets_captured', 'prog_scored', 'semantic_scored', 'vision_scored', 57 'enriched_regex', 'enriched_llm', 'enriched', 58 'proposals_drafted', 'outreach_partial', 'outreach_sent', 59 'ignored', 'failing', 'high_score' 60 )), 61 error_message TEXT, 62 created_at TIMESTAMPTZ DEFAULT NOW(), 63 updated_at TIMESTAMPTZ DEFAULT NOW(), 64 google_domain TEXT, 65 language_code TEXT, 66 currency_code TEXT, 67 company_proof JSONB DEFAULT NULL, -- GDPR: JSON proof of company email verification 68 gdpr_verified BOOLEAN DEFAULT NULL, -- GDPR: TRUE if company email verified 69 gdpr_verified_at TIMESTAMPTZ DEFAULT NULL, -- GDPR: Timestamp of verification 70 ssl_status TEXT CHECK(ssl_status IN ('https', 'http', 'mixed', 'error')), 71 http_headers JSONB, -- JSON format 72 recapture_count INTEGER DEFAULT 0, -- Tracks asset recapture attempts (max 3) 73 recapture_at TIMESTAMPTZ, -- When site should be recaptured (7 days after broken site detection) 74 state TEXT, -- State/province for disambiguation (QLD, IL, NSW, CA, etc.) 75 locale_data JSONB, -- JSON: {htmlLang, hreflangs} 76 retry_count INTEGER DEFAULT 0, -- Tracks retry attempts across all stages 77 last_retry_at TIMESTAMPTZ, -- Timestamp of last retry attempt 78 assets_captured_at TIMESTAMPTZ, 79 form_fill_data JSONB, -- JSON: form fingerprint {url, fields, submitted_at} 80 conversation_status TEXT DEFAULT NULL CHECK(conversation_status IN ( 81 'draft', 'active', 'qualified', 'payment_requested', 'paid', 82 'report_delivered', 'not_interested', 'closed', 'unsubscribed' 83 )), 84 resulted_in_sale BOOLEAN DEFAULT FALSE, 85 sale_amount REAL DEFAULT 0, 86 key_pages_html JSONB DEFAULT NULL, -- JSON: rendered HTML from contact/about pages (browser enrichment pass) 87 perf_json JSONB DEFAULT NULL, -- JSON: page speed / Core Web Vitals {loadTime, domContentLoaded, firstPaint, firstContentfulPaint, transferSize, domInteractive, resourceCounts} 88 next_followup_at TIMESTAMPTZ, -- When next follow-up message should be generated/sent 89 chronic_failure_count INTEGER DEFAULT 0, -- How many times retryFailingSites has reset this site (>= 3 = dead_letter) 90 91 -- Industry classification extracted from score_json (migration 113) 92 industry_classification TEXT 93 ); 94 95 CREATE INDEX IF NOT EXISTS idx_sites_domain ON m333.sites(domain); 96 CREATE INDEX IF NOT EXISTS idx_sites_keyword ON m333.sites(keyword); 97 CREATE INDEX IF NOT EXISTS idx_sites_status ON m333.sites(status); 98 CREATE INDEX IF NOT EXISTS idx_sites_score ON m333.sites(score); 99 CREATE INDEX IF NOT EXISTS idx_sites_last_outreach ON m333.sites(last_outreach_at); 100 CREATE INDEX IF NOT EXISTS idx_sites_country ON m333.sites(country_code); 101 CREATE INDEX IF NOT EXISTS idx_sites_gdpr_verified ON m333.sites(gdpr_verified) WHERE gdpr_verified IS NOT NULL; 102 CREATE INDEX IF NOT EXISTS idx_sites_country_gdpr ON m333.sites(country_code, gdpr_verified) WHERE country_code IN ( 103 'DE', 'FR', 'IT', 'ES', 'NL', 'BE', 'AT', 'SE', 'DK', 'NO', 'IE', 'UK', 'PL' 104 ); 105 CREATE INDEX IF NOT EXISTS idx_sites_recapture_ready ON m333.sites(recapture_at) WHERE recapture_at IS NOT NULL; 106 CREATE INDEX IF NOT EXISTS idx_sites_recapture_count ON m333.sites(recapture_count) WHERE recapture_count > 0; 107 CREATE INDEX IF NOT EXISTS idx_sites_retry_count ON m333.sites(retry_count) WHERE retry_count > 0; 108 CREATE INDEX IF NOT EXISTS idx_sites_failing ON m333.sites(status) WHERE status = 'failing'; 109 CREATE INDEX IF NOT EXISTS idx_sites_high_score ON m333.sites(status) WHERE status = 'high_score'; 110 CREATE INDEX IF NOT EXISTS idx_sites_ignored ON m333.sites(status) WHERE status = 'ignored'; 111 CREATE INDEX IF NOT EXISTS idx_sites_enriched_regex ON m333.sites(status) WHERE status = 'enriched_regex'; 112 CREATE INDEX IF NOT EXISTS idx_sites_key_pages_html ON m333.sites(id) WHERE key_pages_html IS NOT NULL; 113 CREATE INDEX IF NOT EXISTS idx_sites_conversation_status ON m333.sites(conversation_status) WHERE conversation_status IS NOT NULL; 114 CREATE INDEX IF NOT EXISTS idx_sites_dead_letter ON m333.sites(status) WHERE status = 'dead_letter'; 115 CREATE INDEX IF NOT EXISTS idx_sites_next_followup ON m333.sites(next_followup_at) WHERE next_followup_at IS NOT NULL AND status = 'outreach_sent'; 116 CREATE INDEX IF NOT EXISTS idx_sites_industry_classification ON m333.sites(industry_classification, country_code, score) WHERE industry_classification IS NOT NULL; 117 -- New composite index for common status+country queries 118 CREATE INDEX IF NOT EXISTS idx_sites_status_country ON m333.sites(status, country_code); 119 120 -- Trigger to automatically log status changes to site_status table 121 CREATE OR REPLACE FUNCTION m333.log_site_status_change() RETURNS TRIGGER AS $$ 122 BEGIN 123 INSERT INTO m333.site_status (site_id, status, error_message, created_at) 124 VALUES (NEW.id, NEW.status, NEW.error_message, NOW()); 125 RETURN NEW; 126 END; 127 $$ LANGUAGE plpgsql; 128 129 CREATE TRIGGER log_site_status_change 130 AFTER UPDATE OF status ON m333.sites 131 FOR EACH ROW 132 WHEN (NEW.status IS DISTINCT FROM OLD.status) 133 EXECUTE FUNCTION m333.log_site_status_change(); 134 135 136 -- Site status history tracking (for dashboard charts and analytics) 137 CREATE TABLE m333.site_status ( 138 id BIGSERIAL PRIMARY KEY, 139 site_id BIGINT NOT NULL REFERENCES m333.sites(id) ON DELETE CASCADE, 140 status TEXT NOT NULL CHECK(status IN ( 141 'found', 'assets_captured', 'prog_scored', 'semantic_scored', 'vision_scored', 142 'enriched_regex', 'enriched_llm', 'enriched', 143 'proposals_drafted', 'outreach_partial', 'outreach_sent', 144 'ignored', 'failing', 'high_score', 'dead_letter' 145 )), 146 error_message TEXT, 147 created_at TIMESTAMPTZ DEFAULT NOW() 148 ); 149 150 CREATE INDEX IF NOT EXISTS idx_site_status_site_id ON m333.site_status(site_id); 151 CREATE INDEX IF NOT EXISTS idx_site_status_created_at ON m333.site_status(created_at); 152 CREATE INDEX IF NOT EXISTS idx_site_status_site_created ON m333.site_status(site_id, created_at); 153 CREATE INDEX IF NOT EXISTS idx_site_status_status ON m333.site_status(status); 154 155 156 -- Keywords tracking table (for campaign optimization and SERP scraping rotation) 157 CREATE TABLE m333.keywords ( 158 id SERIAL PRIMARY KEY, 159 keyword TEXT NOT NULL, 160 priority INTEGER DEFAULT 5 CHECK(priority >= 1 AND priority <= 10), 161 status TEXT DEFAULT 'active' CHECK(status IN ('active', 'inactive')), 162 search_count INTEGER DEFAULT 0, 163 zenrows_count INTEGER DEFAULT 0, 164 assets_scraped_count INTEGER DEFAULT 0, 165 low_scoring_count INTEGER DEFAULT 0, 166 rescored_count INTEGER DEFAULT 0, 167 search_volume INTEGER DEFAULT 0, 168 country_code TEXT NOT NULL, 169 google_domain TEXT NOT NULL, 170 last_scraped_at TIMESTAMPTZ, 171 last_searched_at TIMESTAMPTZ, 172 created_at TIMESTAMPTZ DEFAULT NOW(), 173 updated_at TIMESTAMPTZ DEFAULT NOW(), 174 UNIQUE(keyword, country_code) 175 ); 176 177 CREATE INDEX IF NOT EXISTS idx_keywords_country ON m333.keywords(country_code); 178 CREATE INDEX IF NOT EXISTS idx_keywords_google_domain ON m333.keywords(google_domain); 179 CREATE INDEX IF NOT EXISTS idx_keywords_status ON m333.keywords(status); 180 CREATE INDEX IF NOT EXISTS idx_keywords_priority ON m333.keywords(priority DESC); 181 CREATE INDEX IF NOT EXISTS idx_keywords_search_count ON m333.keywords(search_count); 182 CREATE INDEX IF NOT EXISTS idx_keywords_zenrows_count ON m333.keywords(zenrows_count); 183 CREATE INDEX IF NOT EXISTS idx_keywords_search_volume ON m333.keywords(search_volume DESC); 184 CREATE INDEX IF NOT EXISTS idx_keywords_priority_search_volume ON m333.keywords(priority DESC, search_volume DESC); 185 CREATE INDEX IF NOT EXISTS idx_keywords_last_scraped ON m333.keywords(last_scraped_at); 186 CREATE INDEX IF NOT EXISTS idx_keywords_last_searched ON m333.keywords(last_searched_at); 187 188 CREATE OR REPLACE FUNCTION m333.update_keywords_timestamp() RETURNS TRIGGER AS $$ 189 BEGIN 190 NEW.updated_at := NOW(); 191 RETURN NEW; 192 END; 193 $$ LANGUAGE plpgsql; 194 195 CREATE TRIGGER update_keywords_timestamp 196 BEFORE UPDATE ON m333.keywords 197 FOR EACH ROW 198 EXECUTE FUNCTION m333.update_keywords_timestamp(); 199 200 201 -- ===== Pricing Stub ===== 202 -- Stub table to satisfy the FK reference in messages.pricing_id. 203 -- pricing_id is always NULL in practice — this table is a placeholder. 204 CREATE TABLE m333.pricing ( 205 id SERIAL PRIMARY KEY 206 ); 207 208 209 -- ============================================================================= 210 -- ===== Messages (Omnichannel) ===== 211 -- ============================================================================= 212 213 -- Unified messages table — replaces both outreaches AND conversations. 214 -- Each message carries its own channel + contact info (omnichannel). 215 -- The conversation "thread" IS the site — all messages for a site form one conversation. 216 CREATE TABLE m333.messages ( 217 id BIGSERIAL PRIMARY KEY, 218 site_id BIGINT NOT NULL REFERENCES m333.sites(id) ON DELETE CASCADE, 219 direction TEXT NOT NULL DEFAULT 'outbound' CHECK(direction IN ('inbound', 'outbound')), 220 221 -- Per-message channel info (can vary per message within a site) 222 contact_method TEXT NOT NULL CHECK(contact_method IN ('sms', 'email', 'form', 'x', 'linkedin')), 223 contact_uri TEXT NOT NULL, 224 225 message_body TEXT, 226 subject_line TEXT, 227 228 -- Outbound: approval workflow (same for proposals AND follow-up replies) 229 approval_status TEXT CHECK(approval_status IN ( 230 'pending', 'approved', 'rework', 'rejected', 'gdpr_blocked', 'parked' 231 )), 232 rework_instructions TEXT, 233 exported_at TIMESTAMPTZ, 234 235 -- Outbound: delivery tracking 236 delivery_status TEXT CHECK(delivery_status IN ( 237 'queued', 'sending', 'sent', 'delivered', 'failed', 'bounced', 'retry_later', 'skipped' 238 )), 239 error_message TEXT, 240 retry_at TIMESTAMPTZ, 241 sent_at TIMESTAMPTZ, 242 delivered_at TIMESTAMPTZ, 243 email_id TEXT, 244 245 -- Engagement tracking (outbound) 246 opened_at TIMESTAMPTZ, 247 tracking_clicked_at TIMESTAMPTZ, 248 249 -- Inbound: classification 250 sentiment TEXT CHECK(sentiment IN ('positive', 'neutral', 'negative', 'objection')), 251 intent TEXT CHECK(intent IN ( 252 'inquiry', 'opt-out', 'interested', 'not-interested', 253 'pricing', 'schedule', 'unknown', 'autoresponder' 254 )), 255 256 -- Message type: 'outreach' (initial pipeline send) vs 'reply' (operator response to inbound) 257 -- Follow-up types: 'followup2' through 'followup5' for multi-touch sequences 258 message_type TEXT DEFAULT 'outreach', 259 260 -- Follow-up sequence tracking (1=initial outreach, 2-5=follow-ups) 261 sequence_step INTEGER DEFAULT 1, 262 263 -- Inbound: metadata 264 raw_payload JSONB, 265 is_read BOOLEAN DEFAULT FALSE, 266 read_at TIMESTAMPTZ, 267 processed_at TIMESTAMPTZ, 268 269 -- Payment (on specific messages that request/confirm payment) 270 payment_link TEXT, 271 payment_id TEXT, 272 payment_amount REAL, 273 payment_currency TEXT, 274 payment_amount_local INTEGER, 275 payment_amount_usd INTEGER, 276 exchange_rate REAL, 277 pricing_variant TEXT, 278 report_url TEXT, 279 280 -- Carried over from outreaches 281 our_account TEXT, 282 template_id TEXT, 283 zb_status TEXT, 284 285 reworded_at TIMESTAMPTZ DEFAULT NULL, 286 287 -- Contact provenance: how this contact URI was obtained (migration 118) 288 contact_basis TEXT, 289 290 -- Outreach quality tracking (migration 118+) 291 proofread_attempts INTEGER DEFAULT 0, 292 retry_count INTEGER DEFAULT 0, 293 294 -- Pricing reference (migration 122 - always NULL in practice) 295 pricing_id INTEGER DEFAULT NULL REFERENCES m333.pricing(id), 296 297 -- Scheduled send time (for follow-up sequencing) 298 scheduled_send_at TIMESTAMPTZ DEFAULT NULL, 299 300 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), 301 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() 302 ); 303 304 CREATE INDEX IF NOT EXISTS idx_messages_site_id ON m333.messages(site_id); 305 CREATE INDEX IF NOT EXISTS idx_messages_direction ON m333.messages(direction); 306 CREATE INDEX IF NOT EXISTS idx_messages_approval_status ON m333.messages(approval_status) WHERE approval_status IS NOT NULL; 307 CREATE INDEX IF NOT EXISTS idx_messages_delivery_status ON m333.messages(delivery_status) WHERE delivery_status IS NOT NULL; 308 CREATE INDEX IF NOT EXISTS idx_messages_email_id ON m333.messages(email_id) WHERE email_id IS NOT NULL; 309 CREATE INDEX IF NOT EXISTS idx_messages_created_at ON m333.messages(created_at); 310 CREATE INDEX IF NOT EXISTS idx_messages_intent ON m333.messages(intent) WHERE intent IS NOT NULL; 311 CREATE INDEX IF NOT EXISTS idx_messages_contact_uri ON m333.messages(contact_uri); 312 CREATE INDEX IF NOT EXISTS idx_messages_contact_method ON m333.messages(contact_method); 313 CREATE INDEX IF NOT EXISTS idx_messages_sent_at ON m333.messages(sent_at) WHERE sent_at IS NOT NULL; 314 CREATE INDEX IF NOT EXISTS idx_messages_updated_at ON m333.messages(updated_at); 315 CREATE INDEX IF NOT EXISTS idx_messages_template_id ON m333.messages(template_id) WHERE template_id IS NOT NULL; 316 CREATE INDEX IF NOT EXISTS idx_messages_exported_at ON m333.messages(exported_at) WHERE exported_at IS NULL; 317 CREATE INDEX IF NOT EXISTS idx_messages_retry_at ON m333.messages(retry_at) WHERE retry_at IS NOT NULL; 318 CREATE INDEX IF NOT EXISTS idx_messages_payment_id ON m333.messages(payment_id) WHERE payment_id IS NOT NULL; 319 CREATE INDEX IF NOT EXISTS idx_messages_zb_status ON m333.messages(zb_status) WHERE zb_status IS NOT NULL; 320 CREATE INDEX IF NOT EXISTS idx_messages_sequence_step ON m333.messages(site_id, sequence_step) WHERE direction = 'outbound' AND sequence_step IS NOT NULL; 321 322 -- Dedup: prevent duplicate outbound proposals to same contact for same site 323 CREATE UNIQUE INDEX IF NOT EXISTS idx_messages_dedup 324 ON m333.messages(site_id, contact_method, contact_uri) 325 WHERE direction = 'outbound' AND approval_status IN ('pending', 'approved'); 326 327 -- Auto-update updated_at on approval/delivery status change 328 CREATE OR REPLACE FUNCTION m333.messages_updated_at() RETURNS TRIGGER AS $$ 329 BEGIN 330 NEW.updated_at := NOW(); 331 RETURN NEW; 332 END; 333 $$ LANGUAGE plpgsql; 334 335 CREATE TRIGGER messages_updated_at 336 BEFORE UPDATE ON m333.messages 337 FOR EACH ROW 338 WHEN ( 339 COALESCE(NEW.approval_status, '') IS DISTINCT FROM COALESCE(OLD.approval_status, '') OR 340 COALESCE(NEW.delivery_status, '') IS DISTINCT FROM COALESCE(OLD.delivery_status, '') 341 ) 342 EXECUTE FUNCTION m333.messages_updated_at(); 343 344 345 -- ============================================================================= 346 -- ===== Compliance ===== 347 -- ============================================================================= 348 349 -- Global unsubscribe list (email) 350 CREATE TABLE m333.unsubscribed_emails ( 351 id SERIAL PRIMARY KEY, 352 email CITEXT NOT NULL UNIQUE, 353 message_id BIGINT REFERENCES m333.messages(id), 354 unsubscribed_at TIMESTAMPTZ DEFAULT NOW(), 355 source TEXT DEFAULT 'web' CHECK(source IN ('web', 'manual', 'bounce', 'complaint')), 356 created_at TIMESTAMPTZ DEFAULT NOW() 357 ); 358 359 CREATE INDEX IF NOT EXISTS idx_unsubscribed_emails_email ON m333.unsubscribed_emails(email); 360 CREATE INDEX IF NOT EXISTS idx_unsubscribed_emails_date ON m333.unsubscribed_emails(unsubscribed_at); 361 362 -- TCPA/CAN-SPAM opt-outs (phone & email) 363 CREATE TABLE m333.opt_outs ( 364 id SERIAL PRIMARY KEY, 365 phone TEXT, 366 email TEXT, 367 method TEXT NOT NULL CHECK(method IN ('sms', 'email')), 368 opted_out_at TIMESTAMPTZ DEFAULT NOW(), 369 source TEXT DEFAULT 'inbound' CHECK(source IN ('inbound', 'manual', 'bounce', 'complaint')), 370 created_at TIMESTAMPTZ DEFAULT NOW(), 371 UNIQUE(phone, method), 372 UNIQUE(email, method) 373 ); 374 375 CREATE INDEX IF NOT EXISTS idx_opt_outs_phone ON m333.opt_outs(phone, method); 376 CREATE INDEX IF NOT EXISTS idx_opt_outs_email ON m333.opt_outs(email, method); 377 CREATE INDEX IF NOT EXISTS idx_opt_outs_date ON m333.opt_outs(opted_out_at); 378 379 380 -- ============================================================================= 381 -- ===== Proposals & Templates ===== 382 -- ============================================================================= 383 384 -- Proposal templates for template-based outreach (zero LLM cost) 385 CREATE TABLE m333.proposal_templates ( 386 id SERIAL PRIMARY KEY, 387 template_type TEXT NOT NULL CHECK(template_type IN ('email', 'sms')), 388 country_code TEXT NOT NULL DEFAULT 'US', 389 language TEXT NOT NULL DEFAULT 'en', 390 subject_line TEXT, -- for email only 391 body_text TEXT NOT NULL, 392 variables JSONB NOT NULL, -- JSON array of field names used 393 sends INTEGER DEFAULT 0, 394 conversions INTEGER DEFAULT 0, 395 created_at TIMESTAMPTZ DEFAULT NOW(), 396 updated_at TIMESTAMPTZ DEFAULT NOW() 397 ); 398 399 CREATE INDEX IF NOT EXISTS idx_templates_type_country ON m333.proposal_templates(template_type, country_code); 400 CREATE INDEX IF NOT EXISTS idx_templates_language ON m333.proposal_templates(language); 401 402 CREATE OR REPLACE FUNCTION m333.update_proposal_templates_timestamp() RETURNS TRIGGER AS $$ 403 BEGIN 404 NEW.updated_at := NOW(); 405 RETURN NEW; 406 END; 407 $$ LANGUAGE plpgsql; 408 409 CREATE TRIGGER update_proposal_templates_timestamp 410 BEFORE UPDATE ON m333.proposal_templates 411 FOR EACH ROW 412 EXECUTE FUNCTION m333.update_proposal_templates_timestamp(); 413 414 -- Prompt feedback tracking (for AI learning system) 415 CREATE TABLE m333.prompt_feedback ( 416 id SERIAL PRIMARY KEY, 417 message_id BIGINT REFERENCES m333.messages(id) ON DELETE CASCADE, 418 site_id BIGINT REFERENCES m333.sites(id) ON DELETE CASCADE, 419 prompt_file TEXT NOT NULL, -- 'PROPOSAL.md', 'CONVERSION-SCORING.md', etc. 420 prompt_version INTEGER DEFAULT 1, -- Track which version was used 421 feedback_type TEXT NOT NULL CHECK(feedback_type IN ( 422 'rework', -- Operator requested rework 423 'rejected', -- Operator rejected outreach 424 'approved', -- Operator approved (positive signal) 425 'conversion', -- Resulted in sale (strong positive) 426 'no_response' -- Sent but no response after 14 days (weak negative) 427 )), 428 feedback_text TEXT, -- Rework instructions or rejection reason 429 feedback_category TEXT, -- Auto-categorized: 'tone', 'length', 'urgency', 'personalization', etc. 430 sentiment_before TEXT, -- Site sentiment before outreach 431 sentiment_after TEXT, -- Conversation sentiment (if replied) 432 resulted_in_sale BOOLEAN DEFAULT FALSE, 433 created_at TIMESTAMPTZ DEFAULT NOW() 434 ); 435 436 CREATE INDEX IF NOT EXISTS idx_prompt_feedback_prompt ON m333.prompt_feedback(prompt_file, prompt_version); 437 CREATE INDEX IF NOT EXISTS idx_prompt_feedback_type ON m333.prompt_feedback(feedback_type); 438 CREATE INDEX IF NOT EXISTS idx_prompt_feedback_category ON m333.prompt_feedback(feedback_category); 439 CREATE INDEX IF NOT EXISTS idx_prompt_feedback_created ON m333.prompt_feedback(created_at); 440 CREATE INDEX IF NOT EXISTS idx_prompt_feedback_message ON m333.prompt_feedback(message_id); 441 442 -- Prompt versions tracking table 443 CREATE TABLE m333.prompt_versions ( 444 id SERIAL PRIMARY KEY, 445 prompt_file TEXT NOT NULL, 446 version INTEGER NOT NULL, 447 content TEXT NOT NULL, -- Full prompt text 448 change_summary TEXT, -- What changed in this version 449 learning_applied TEXT, -- What feedback patterns influenced this version 450 created_at TIMESTAMPTZ DEFAULT NOW(), 451 UNIQUE(prompt_file, version) 452 ); 453 454 CREATE INDEX IF NOT EXISTS idx_prompt_versions_file ON m333.prompt_versions(prompt_file, version DESC); 455 456 -- Store latest export sheet URL for dashboard link 457 CREATE TABLE m333.export_sheets ( 458 id SERIAL PRIMARY KEY, 459 sheet_id TEXT NOT NULL, 460 sheet_url TEXT NOT NULL, 461 outreach_count INTEGER NOT NULL, 462 created_at TIMESTAMPTZ DEFAULT NOW() 463 ); 464 465 CREATE INDEX IF NOT EXISTS idx_export_sheets_created ON m333.export_sheets(created_at DESC); 466 467 468 -- ============================================================================= 469 -- ===== Country & Pricing ===== 470 -- ============================================================================= 471 472 -- Country configuration — pricing, compliance, localization settings per market 473 CREATE TABLE m333.countries ( 474 -- Primary key 475 country_code TEXT PRIMARY KEY, -- ISO 3166-1 alpha-2 (e.g., 'US', 'AU', 'UK') 476 477 -- Basic info 478 country_name TEXT NOT NULL, 479 google_domain TEXT NOT NULL, -- For SERP scraping (e.g., 'google.com.au') 480 language_code TEXT NOT NULL, -- ISO 639-1 (e.g., 'en', 'es', 'de') 481 timezone TEXT NOT NULL, -- IANA timezone (e.g., 'America/New_York') 482 483 -- Currency and localization 484 currency_code TEXT NOT NULL, -- ISO 4217 (e.g., 'USD', 'EUR', 'AUD') 485 currency_symbol TEXT NOT NULL, -- Display symbol (e.g., '$', 'EUR', '??') 486 date_format TEXT NOT NULL, -- Format pattern (e.g., 'DD/MM/YYYY') 487 488 -- Pricing (USD equivalent for comparison) 489 price_usd INTEGER NOT NULL, -- Price in USD cents (e.g., 29700 = $297) 490 pricing_tier TEXT NOT NULL CHECK(pricing_tier IN ( 491 'Premium+', 'Premium', 'Standard', 'Moderate', 'Emerging', 'Developing' 492 )), 493 494 -- Economic indicators 495 ppp_gdp_per_capita INTEGER, -- PPP GDP per capita (international dollars) 496 total_gdp_trillions REAL, -- Total GDP in trillions USD 497 498 -- Market classification 499 market_notes TEXT, -- Notes about market characteristics 500 is_price_sensitive BOOLEAN DEFAULT FALSE, -- Whether market is price-sensitive 501 is_premium_market BOOLEAN DEFAULT FALSE, -- Whether to emphasize premium positioning 502 503 -- Compliance and regulations 504 requires_gdpr_check BOOLEAN DEFAULT FALSE, -- EU/EEA countries requiring GDPR compliance 505 phone_format TEXT, -- E.164 format prefix (e.g., '+61', '+1') 506 mobile_pattern TEXT, -- Regex pattern to identify mobile numbers 507 508 -- Browser and localization settings 509 accept_language TEXT, -- Accept-Language header for HTTP requests 510 511 -- GDPR-specific fields (for EU countries) 512 company_types JSONB, -- JSON array of common company types 513 company_keywords JSONB, -- JSON array of company registration keywords 514 key_page_names JSONB, -- JSON array of common page names (contact, about, legal) 515 516 -- SEO and keyword data 517 common_cities JSONB, -- JSON array of major cities for keyword generation 518 519 -- Status and metadata 520 is_active BOOLEAN DEFAULT TRUE, -- Whether country is actively supported 521 created_at TIMESTAMPTZ DEFAULT NOW(), 522 updated_at TIMESTAMPTZ DEFAULT NOW(), 523 524 -- Pricing override tracking 525 price_overridden BOOLEAN DEFAULT FALSE, -- Whether price was manually overridden 526 override_reason TEXT, -- Reason for price override 527 override_date TIMESTAMPTZ, -- When price was overridden 528 529 -- Cultural pricing / A/B test fields (added by migrations) 530 price_usd_base INTEGER DEFAULT 30000, 531 price_usd_ppp INTEGER, 532 price_local INTEGER, 533 price_local_formatted TEXT, 534 exchange_rate REAL, 535 price_last_updated TIMESTAMPTZ, 536 pricing_variant TEXT DEFAULT 'control', 537 variant_multiplier REAL DEFAULT 1.0, 538 twilio_phone_number TEXT, -- E.164 local Twilio number (NULL = not purchased yet) 539 sms_enabled BOOLEAN NOT NULL DEFAULT FALSE -- 1 = we have/plan a local number, 0 = SMS disabled 540 ); 541 542 CREATE INDEX IF NOT EXISTS idx_countries_active ON m333.countries(is_active); 543 CREATE INDEX IF NOT EXISTS idx_countries_tier ON m333.countries(pricing_tier); 544 CREATE INDEX IF NOT EXISTS idx_countries_currency ON m333.countries(currency_code); 545 CREATE INDEX IF NOT EXISTS idx_countries_gdpr ON m333.countries(requires_gdpr_check); 546 CREATE INDEX IF NOT EXISTS idx_countries_pricing_variant ON m333.countries(pricing_variant); 547 CREATE INDEX IF NOT EXISTS idx_countries_price_updated ON m333.countries(price_last_updated); 548 549 CREATE OR REPLACE FUNCTION m333.update_countries_timestamp() RETURNS TRIGGER AS $$ 550 BEGIN 551 NEW.updated_at := NOW(); 552 RETURN NEW; 553 END; 554 $$ LANGUAGE plpgsql; 555 556 CREATE TRIGGER update_countries_timestamp 557 BEFORE UPDATE ON m333.countries 558 FOR EACH ROW 559 EXECUTE FUNCTION m333.update_countries_timestamp(); 560 561 -- Purchases from auditandfix.com (CRO audit report sales) 562 CREATE TABLE m333.purchases ( 563 id SERIAL PRIMARY KEY, 564 email TEXT NOT NULL, 565 landing_page_url TEXT NOT NULL, 566 phone TEXT, 567 paypal_order_id TEXT UNIQUE, 568 paypal_payer_id TEXT, 569 paypal_capture_id TEXT, 570 amount INTEGER NOT NULL, -- smallest currency unit (cents) 571 currency TEXT NOT NULL, -- ISO 4217 572 amount_usd INTEGER NOT NULL, -- normalized USD cents 573 country_code TEXT, 574 lang TEXT NOT NULL DEFAULT 'en', 575 ip_address TEXT, 576 user_agent TEXT, 577 message_id BIGINT REFERENCES m333.messages(id) ON DELETE SET NULL, 578 site_id BIGINT REFERENCES m333.sites(id), 579 status TEXT NOT NULL DEFAULT 'paid' 580 CHECK(status IN ('paid','processing','report_generated','delivered','failed','refunded')), 581 report_path TEXT, 582 report_score REAL, 583 report_grade TEXT, 584 delivered_at TIMESTAMPTZ, 585 refunded_at TIMESTAMPTZ, 586 refund_reason TEXT, 587 error_message TEXT, 588 retry_count INTEGER DEFAULT 0, 589 created_at TIMESTAMPTZ DEFAULT NOW(), 590 updated_at TIMESTAMPTZ DEFAULT NOW() 591 ); 592 593 CREATE INDEX IF NOT EXISTS idx_purchases_email ON m333.purchases(email); 594 CREATE INDEX IF NOT EXISTS idx_purchases_status ON m333.purchases(status); 595 CREATE INDEX IF NOT EXISTS idx_purchases_paypal_order ON m333.purchases(paypal_order_id); 596 CREATE INDEX IF NOT EXISTS idx_purchases_site_id ON m333.purchases(site_id); 597 CREATE INDEX IF NOT EXISTS idx_purchases_created_at ON m333.purchases(created_at); 598 CREATE INDEX IF NOT EXISTS idx_purchases_country_code ON m333.purchases(country_code); 599 600 CREATE OR REPLACE FUNCTION m333.update_purchases_timestamp() RETURNS TRIGGER AS $$ 601 BEGIN 602 NEW.updated_at := NOW(); 603 RETURN NEW; 604 END; 605 $$ LANGUAGE plpgsql; 606 607 CREATE TRIGGER update_purchases_timestamp 608 BEFORE UPDATE ON m333.purchases 609 FOR EACH ROW 610 EXECUTE FUNCTION m333.update_purchases_timestamp(); 611 612 613 -- Idempotency table for PayPal webhook events. 614 -- A row is inserted atomically (ON CONFLICT DO NOTHING) before processing. 615 -- If the insert lands 0 rows, the webhook is a duplicate and is skipped. 616 CREATE TABLE IF NOT EXISTS m333.processed_webhooks ( 617 order_id TEXT PRIMARY KEY, 618 processed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), 619 amount NUMERIC(12, 2), 620 currency TEXT 621 ); 622 623 624 -- ============================================================================= 625 -- ===== LLM Usage & Cost Tracking ===== 626 -- ============================================================================= 627 628 -- LLM usage tracking (per-request cost accounting) 629 CREATE TABLE m333.llm_usage ( 630 id BIGSERIAL PRIMARY KEY, 631 site_id BIGINT REFERENCES m333.sites(id), 632 stage TEXT NOT NULL CHECK(stage IN ( 633 'scoring', 'rescoring', 'proposals', 'enrichment', 634 'outreach', 'replies', 'overseer', 'keywords', 'agents', 635 'serps', 'assets', 'name-extraction', 'other' 636 )), 637 provider TEXT NOT NULL CHECK(provider IN ( 638 'anthropic', 'openrouter', 'claude-cli' 639 )), 640 model TEXT NOT NULL, 641 prompt_tokens INTEGER NOT NULL, 642 completion_tokens INTEGER NOT NULL, 643 total_tokens INTEGER NOT NULL, 644 estimated_cost NUMERIC(10, 6), 645 request_id TEXT, 646 created_at TIMESTAMPTZ DEFAULT NOW() 647 ); 648 649 CREATE INDEX IF NOT EXISTS idx_llm_usage_site ON m333.llm_usage(site_id); 650 CREATE INDEX IF NOT EXISTS idx_llm_usage_stage ON m333.llm_usage(stage); 651 CREATE INDEX IF NOT EXISTS idx_llm_usage_provider ON m333.llm_usage(provider); 652 CREATE INDEX IF NOT EXISTS idx_llm_usage_created ON m333.llm_usage(created_at); 653 CREATE INDEX IF NOT EXISTS idx_llm_usage_stage_created ON m333.llm_usage(stage, created_at); 654 655 -- OpenRouter credit balance log (for monitoring spend and alerts) 656 CREATE TABLE m333.openrouter_credit_log ( 657 id SERIAL PRIMARY KEY, 658 timestamp TIMESTAMPTZ DEFAULT NOW(), 659 label TEXT, -- API key label/name 660 usage REAL NOT NULL, -- Total spend in USD 661 credit_limit REAL, -- Credit limit in USD (null if unlimited) 662 remaining REAL, -- Calculated remaining credits (limit - usage) 663 is_free_tier BOOLEAN DEFAULT FALSE, -- true if free tier, false if paid 664 rate_limit JSONB, -- JSON of rate limit info 665 raw_response JSONB -- Full JSON response from API 666 ); 667 668 CREATE INDEX IF NOT EXISTS idx_openrouter_credit_log_timestamp ON m333.openrouter_credit_log(timestamp); 669 CREATE INDEX IF NOT EXISTS idx_openrouter_credit_log_remaining ON m333.openrouter_credit_log(remaining); 670 671 672 -- ============================================================================= 673 -- ===== Error Tracking & Review ===== 674 -- ============================================================================= 675 676 -- Error fix history (for dashboard reporting — both site errors and code bug fixes) 677 CREATE TABLE m333.error_fix_history ( 678 id SERIAL PRIMARY KEY, 679 site_id BIGINT REFERENCES m333.sites(id), -- NULL for code bugs 680 error_type TEXT NOT NULL, -- NETWORK_TIMEOUT, TYPE_ERROR, etc. 681 error_category TEXT NOT NULL CHECK(error_category IN ('SITE_ERROR', 'CODE_BUG')), 682 error_message TEXT, -- First 200 chars of error 683 fix_action TEXT NOT NULL, -- What fix was applied 684 fix_succeeded BOOLEAN DEFAULT FALSE, -- Did fix work? 685 file_changed TEXT, -- File path if code fix 686 attempted_at TIMESTAMPTZ DEFAULT NOW(), 687 created_at TIMESTAMPTZ DEFAULT NOW() 688 ); 689 690 CREATE INDEX IF NOT EXISTS idx_error_fix_history_site ON m333.error_fix_history(site_id); 691 CREATE INDEX IF NOT EXISTS idx_error_fix_history_date ON m333.error_fix_history(attempted_at); 692 CREATE INDEX IF NOT EXISTS idx_error_fix_history_category ON m333.error_fix_history(error_category); 693 CREATE INDEX IF NOT EXISTS idx_error_fix_history_type ON m333.error_fix_history(error_type); 694 695 -- Human review queue (code reviews, security reviews, etc.) 696 CREATE TABLE m333.human_review_queue ( 697 id SERIAL PRIMARY KEY, 698 file TEXT, 699 reason TEXT, 700 type TEXT, 701 priority TEXT DEFAULT 'medium', 702 status TEXT DEFAULT 'pending', 703 created_at TIMESTAMPTZ DEFAULT NOW(), 704 reviewed_at TIMESTAMPTZ, 705 reviewed_by TEXT, 706 notes TEXT 707 ); 708 709 CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_pending_reviews 710 ON m333.human_review_queue(file, type) 711 WHERE status = 'pending'; 712 713 -- Screenshot optimization tests (benchmarking cropping methods) 714 CREATE TABLE m333.screenshot_optimization_tests ( 715 id SERIAL PRIMARY KEY, 716 site_domain TEXT NOT NULL, 717 method_name TEXT NOT NULL CHECK(method_name IN ( 718 'sharp_attention', 'dom_bounding', 'edge_detection', 'hybrid' 719 )), 720 token_count INTEGER, 721 file_size_bytes INTEGER, 722 processing_time_ms INTEGER, 723 created_at TIMESTAMPTZ DEFAULT NOW() 724 ); 725 726 727 -- ============================================================================= 728 -- ===== Configuration ===== 729 -- ============================================================================= 730 731 -- Config table (key-value store for test compatibility) 732 -- NOTE: Removed from production databases by migration 038 (migrated to .env) 733 -- Kept in schema.sql for test compatibility only 734 CREATE TABLE m333.config ( 735 key TEXT PRIMARY KEY, 736 value TEXT NOT NULL 737 ); 738 739 -- Dashboard cache for pre-computed metrics (performance optimization) 740 CREATE TABLE m333.dashboard_cache ( 741 cache_key TEXT PRIMARY KEY, 742 cache_value JSONB NOT NULL, -- JSON serialized data 743 expires_at TIMESTAMPTZ NOT NULL, 744 created_at TIMESTAMPTZ DEFAULT NOW(), 745 updated_at TIMESTAMPTZ DEFAULT NOW() 746 ); 747 748 CREATE INDEX IF NOT EXISTS idx_dashboard_cache_expires ON m333.dashboard_cache(expires_at); 749 750 -- LLM-proposed regex patterns for unknown error messages (reviewed via dashboard) 751 CREATE TABLE m333.error_pattern_proposals ( 752 id SERIAL PRIMARY KEY, 753 pattern TEXT NOT NULL, 754 label TEXT NOT NULL, 755 group_name TEXT NOT NULL CHECK(group_name IN ('terminal', 'retriable')), 756 context TEXT NOT NULL CHECK(context IN ('site', 'outreach')), 757 example_errors JSONB, -- JSON array of sample error messages that matched 758 occurrence_count INTEGER, 759 status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'approved', 'rejected')), 760 created_at TIMESTAMPTZ DEFAULT NOW(), 761 reviewed_at TIMESTAMPTZ, 762 reviewed_by TEXT 763 ); 764 765 CREATE INDEX IF NOT EXISTS idx_error_pattern_proposals_status ON m333.error_pattern_proposals(status); 766 CREATE INDEX IF NOT EXISTS idx_error_pattern_proposals_context ON m333.error_pattern_proposals(context); 767 768 769 -- ============================================================================= 770 -- ops SCHEMA — Operational Tables 771 -- ============================================================================= 772 773 -- Cron job runtime locks (prevents concurrent execution) 774 CREATE TABLE ops.cron_locks ( 775 lock_key TEXT PRIMARY KEY, 776 acquired_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), 777 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), 778 description TEXT 779 ); 780 781 CREATE INDEX IF NOT EXISTS idx_cron_locks_updated ON ops.cron_locks(updated_at); 782 783 -- Cron jobs management (dynamic configuration of scheduled tasks) 784 CREATE TABLE ops.cron_jobs ( 785 id SERIAL PRIMARY KEY, 786 name TEXT NOT NULL UNIQUE, -- Human-readable name 787 task_key TEXT NOT NULL UNIQUE, -- Programmatic identifier 788 description TEXT, -- Job description 789 handler_type TEXT NOT NULL CHECK(handler_type IN ('function', 'command')), 790 handler_value TEXT NOT NULL, -- Function name or command to run 791 interval_value INTEGER NOT NULL, -- Interval number (e.g., 5, 15, 60) 792 interval_unit TEXT NOT NULL CHECK(interval_unit IN ('minutes', 'hours', 'days', 'weeks')), 793 enabled BOOLEAN DEFAULT TRUE, -- Whether job is active 794 last_run_at TIMESTAMPTZ, -- Last execution timestamp 795 created_at TIMESTAMPTZ DEFAULT NOW(), 796 updated_at TIMESTAMPTZ DEFAULT NOW(), 797 pause_pipeline BOOLEAN DEFAULT FALSE, -- Whether this job pauses the pipeline while running 798 critical BOOLEAN DEFAULT TRUE, -- Whether this is a critical job 799 timeout_seconds INTEGER DEFAULT NULL -- Max seconds to run before SIGTERM (NULL = 480s default) 800 ); 801 802 CREATE INDEX IF NOT EXISTS idx_cron_jobs_enabled ON ops.cron_jobs(enabled); 803 CREATE INDEX IF NOT EXISTS idx_cron_jobs_task_key ON ops.cron_jobs(task_key); 804 805 CREATE OR REPLACE FUNCTION ops.update_cron_jobs_timestamp() RETURNS TRIGGER AS $$ 806 BEGIN 807 NEW.updated_at := NOW(); 808 RETURN NEW; 809 END; 810 $$ LANGUAGE plpgsql; 811 812 CREATE TRIGGER update_cron_jobs_timestamp 813 BEFORE UPDATE ON ops.cron_jobs 814 FOR EACH ROW 815 EXECUTE FUNCTION ops.update_cron_jobs_timestamp(); 816 817 -- Cron job execution logs (for monitoring and debugging) 818 CREATE TABLE ops.cron_job_logs ( 819 id BIGSERIAL PRIMARY KEY, 820 job_name TEXT NOT NULL, 821 started_at TIMESTAMPTZ NOT NULL, 822 finished_at TIMESTAMPTZ, 823 status TEXT NOT NULL CHECK(status IN ('running', 'success', 'failed', 'timeout')), 824 summary TEXT, 825 full_log TEXT, 826 items_processed INTEGER DEFAULT 0, 827 items_failed INTEGER DEFAULT 0, 828 error_message TEXT, 829 created_at TIMESTAMPTZ DEFAULT NOW() 830 ); 831 832 CREATE INDEX IF NOT EXISTS idx_cron_logs_job ON ops.cron_job_logs(job_name); 833 CREATE INDEX IF NOT EXISTS idx_cron_logs_started ON ops.cron_job_logs(started_at); 834 CREATE INDEX IF NOT EXISTS idx_cron_logs_status ON ops.cron_job_logs(status); 835 CREATE INDEX IF NOT EXISTS idx_cron_logs_job_started ON ops.cron_job_logs(job_name, started_at); 836 837 -- Pipeline service control (singleton — only one row) 838 CREATE TABLE ops.pipeline_control ( 839 id INTEGER PRIMARY KEY CHECK (id = 1), -- Only one row ever exists 840 paused BOOLEAN DEFAULT FALSE, 841 paused_by TEXT, -- Which job requested pause 842 paused_at TIMESTAMPTZ, 843 current_stage TEXT, -- Which stage is currently running 844 last_cycle_at TIMESTAMPTZ, -- Last full cycle completion 845 created_at TIMESTAMPTZ DEFAULT NOW(), 846 updated_at TIMESTAMPTZ DEFAULT NOW(), 847 current_browser_loop_stage TEXT, 848 last_api_loop_at TIMESTAMPTZ, 849 last_browser_loop_at TIMESTAMPTZ, 850 last_outreach_loop_at TIMESTAMPTZ 851 ); 852 853 -- Runtime settings (dashboard-toggleable operational controls) 854 CREATE TABLE ops.settings ( 855 key TEXT PRIMARY KEY, 856 value TEXT NOT NULL, 857 description TEXT, 858 updated_at TIMESTAMPTZ DEFAULT NOW() 859 ); 860 861 -- Migration tracking (applied migrations log) 862 CREATE TABLE ops.migrations ( 863 id SERIAL PRIMARY KEY, 864 filename TEXT UNIQUE NOT NULL, 865 applied_at TIMESTAMPTZ DEFAULT NOW() 866 ); 867 868 869 -- ============================================================================= 870 -- tel SCHEMA — Telemetry Tables 871 -- ============================================================================= 872 873 -- Pipeline service performance metrics 874 CREATE TABLE tel.pipeline_metrics ( 875 id BIGSERIAL PRIMARY KEY, 876 stage_name TEXT NOT NULL, -- 'assets', 'scoring', 'rescoring', etc. 877 sites_processed INTEGER DEFAULT 0, 878 sites_succeeded INTEGER DEFAULT 0, 879 sites_failed INTEGER DEFAULT 0, 880 duration_ms INTEGER NOT NULL, 881 started_at TIMESTAMPTZ NOT NULL, 882 finished_at TIMESTAMPTZ NOT NULL, 883 created_at TIMESTAMPTZ DEFAULT NOW() 884 ); 885 886 CREATE INDEX IF NOT EXISTS idx_pipeline_metrics_stage ON tel.pipeline_metrics(stage_name, started_at); 887 CREATE INDEX IF NOT EXISTS idx_pipeline_metrics_started ON tel.pipeline_metrics(started_at); 888 889 -- System metrics tracking (for dashboard resource utilization charts) 890 CREATE TABLE tel.system_metrics ( 891 id BIGSERIAL PRIMARY KEY, 892 cpu_percent REAL NOT NULL, -- Average CPU utilization (0-100) 893 disk_read_mb REAL NOT NULL, -- Disk read MB/s 894 disk_write_mb REAL NOT NULL, -- Disk write MB/s 895 memory_percent REAL NOT NULL, -- Memory utilization (0-100) 896 recorded_at TIMESTAMPTZ DEFAULT NOW() 897 ); 898 899 CREATE INDEX IF NOT EXISTS idx_system_metrics_recorded_at ON tel.system_metrics(recorded_at); 900 901 -- System health checks (agent-driven health monitoring) 902 CREATE TABLE tel.system_health ( 903 id SERIAL PRIMARY KEY, 904 check_type TEXT NOT NULL, 905 status TEXT NOT NULL CHECK (status IN ('ok', 'warning', 'critical')), 906 details JSONB, -- JSON 907 action_taken TEXT, 908 created_at TIMESTAMPTZ DEFAULT NOW() 909 ); 910 911 CREATE INDEX IF NOT EXISTS idx_system_health_check_type ON tel.system_health(check_type); 912 CREATE INDEX IF NOT EXISTS idx_system_health_created_at ON tel.system_health(created_at); 913 CREATE INDEX IF NOT EXISTS idx_system_health_status ON tel.system_health(status); 914 915 916 -- ============================================================================= 917 -- ===== Agent System (tel schema) ===== 918 -- ============================================================================= 919 920 -- Agent tasks — work items assigned to autonomous agents 921 CREATE TABLE tel.agent_tasks ( 922 id SERIAL PRIMARY KEY, 923 task_type TEXT NOT NULL, 924 assigned_to TEXT NOT NULL, 925 created_by TEXT DEFAULT 'system', 926 parent_task_id INTEGER REFERENCES tel.agent_tasks(id) ON DELETE CASCADE, 927 priority INTEGER DEFAULT 5 CHECK(priority BETWEEN 1 AND 10), 928 status TEXT DEFAULT 'pending' CHECK(status IN ( 929 'pending', 930 'running', 931 'completed', 932 'failed', 933 'blocked', 934 'awaiting_po_approval', 935 'awaiting_architect_approval' 936 )), 937 context_json JSONB, 938 result_json JSONB, 939 error_message TEXT, 940 retry_count INTEGER DEFAULT 0, 941 reviewed_by TEXT, 942 approval_json JSONB, 943 created_at TIMESTAMPTZ DEFAULT NOW(), 944 started_at TIMESTAMPTZ, 945 completed_at TIMESTAMPTZ 946 ); 947 948 CREATE INDEX IF NOT EXISTS idx_agent_tasks_assigned_to ON tel.agent_tasks(assigned_to, status); 949 CREATE INDEX IF NOT EXISTS idx_agent_tasks_parent ON tel.agent_tasks(parent_task_id); 950 CREATE INDEX IF NOT EXISTS idx_agent_tasks_priority ON tel.agent_tasks(priority DESC, created_at ASC); 951 CREATE INDEX IF NOT EXISTS idx_agent_tasks_status ON tel.agent_tasks(status); 952 CREATE INDEX IF NOT EXISTS idx_agent_tasks_approval ON tel.agent_tasks(status, assigned_to) 953 WHERE status IN ('awaiting_po_approval', 'awaiting_architect_approval'); 954 955 -- Agent state — current status of each agent (singleton per agent) 956 CREATE TABLE tel.agent_state ( 957 agent_name TEXT PRIMARY KEY CHECK(agent_name IN ( 958 'developer', 'qa', 'security', 'architect', 'triage', 'monitor' 959 )), 960 last_active TIMESTAMPTZ DEFAULT NOW(), 961 current_task_id INTEGER REFERENCES tel.agent_tasks(id), 962 status TEXT DEFAULT 'idle' CHECK(status IN ('idle', 'working', 'blocked')), 963 metrics_json JSONB -- Success rate, avg task time, token usage, etc. 964 ); 965 966 -- Agent inter-agent messages (questions, handoffs, notifications) 967 CREATE TABLE tel.agent_messages ( 968 id SERIAL PRIMARY KEY, 969 task_id INTEGER REFERENCES tel.agent_tasks(id), 970 from_agent TEXT NOT NULL, 971 to_agent TEXT NOT NULL, 972 message_type TEXT CHECK(message_type IN ('question', 'answer', 'handoff', 'notification')), 973 content TEXT NOT NULL, 974 metadata_json JSONB, -- Additional context (severity, urgency, etc.) 975 created_at TIMESTAMPTZ DEFAULT NOW(), 976 read_at TIMESTAMPTZ 977 ); 978 979 CREATE INDEX IF NOT EXISTS idx_agent_messages_task ON tel.agent_messages(task_id); 980 CREATE INDEX IF NOT EXISTS idx_agent_messages_to ON tel.agent_messages(to_agent, read_at); 981 982 -- Agent execution logs (debug/info/warn/error per task) 983 CREATE TABLE tel.agent_logs ( 984 id BIGSERIAL PRIMARY KEY, 985 task_id INTEGER REFERENCES tel.agent_tasks(id), 986 agent_name TEXT NOT NULL, 987 log_level TEXT CHECK(log_level IN ('debug', 'info', 'warn', 'error')), 988 message TEXT NOT NULL, 989 data_json JSONB, -- Structured log data (files read, commands run, etc.) 990 created_at TIMESTAMPTZ DEFAULT NOW() 991 ); 992 993 CREATE INDEX IF NOT EXISTS idx_agent_logs_task ON tel.agent_logs(task_id); 994 CREATE INDEX IF NOT EXISTS idx_agent_logs_agent ON tel.agent_logs(agent_name, created_at); 995 996 -- Agent LLM usage (cost tracking per agent invocation) 997 CREATE TABLE tel.agent_llm_usage ( 998 id SERIAL PRIMARY KEY, 999 agent_name TEXT NOT NULL CHECK(agent_name IN ( 1000 'developer', 'qa', 'security', 'architect', 'triage', 'monitor' 1001 )), 1002 task_id INTEGER REFERENCES tel.agent_tasks(id), 1003 model TEXT NOT NULL, 1004 prompt_tokens INTEGER NOT NULL, 1005 completion_tokens INTEGER NOT NULL, 1006 cost_usd NUMERIC(10, 6) NOT NULL, 1007 created_at TIMESTAMPTZ DEFAULT NOW() 1008 ); 1009 1010 CREATE INDEX IF NOT EXISTS idx_agent_llm_usage_agent ON tel.agent_llm_usage(agent_name); 1011 CREATE INDEX IF NOT EXISTS idx_agent_llm_usage_task ON tel.agent_llm_usage(task_id); 1012 CREATE INDEX IF NOT EXISTS idx_agent_llm_usage_created ON tel.agent_llm_usage(created_at); 1013 CREATE INDEX IF NOT EXISTS idx_agent_llm_usage_cost ON tel.agent_llm_usage(cost_usd DESC); 1014 CREATE INDEX IF NOT EXISTS idx_agent_llm_usage_agent_date ON tel.agent_llm_usage(agent_name, CAST(created_at AT TIME ZONE 'UTC' AS date)); 1015 1016 -- Agent task outcomes (success/failure tracking for pattern learning) 1017 CREATE TABLE tel.agent_outcomes ( 1018 id SERIAL PRIMARY KEY, 1019 task_id INTEGER NOT NULL REFERENCES tel.agent_tasks(id) ON DELETE CASCADE, 1020 agent_name TEXT NOT NULL CHECK(agent_name IN ( 1021 'developer', 'qa', 'security', 'architect', 'triage', 'monitor' 1022 )), 1023 task_type TEXT NOT NULL, -- classify_error, fix_bug, verify_fix, etc. 1024 outcome TEXT NOT NULL CHECK(outcome IN ('success', 'failure')), 1025 context_json JSONB, -- Task-specific context (error_type, file_path, etc.) 1026 result_json JSONB, -- Task result details (what worked, what didn't) 1027 duration_ms INTEGER, -- Task execution time 1028 created_at TIMESTAMPTZ DEFAULT NOW() 1029 ); 1030 1031 CREATE INDEX IF NOT EXISTS idx_agent_outcomes_agent ON tel.agent_outcomes(agent_name, task_type); 1032 CREATE INDEX IF NOT EXISTS idx_agent_outcomes_task_type ON tel.agent_outcomes(task_type, outcome); 1033 CREATE INDEX IF NOT EXISTS idx_agent_outcomes_outcome ON tel.agent_outcomes(outcome, created_at); 1034 CREATE INDEX IF NOT EXISTS idx_agent_outcomes_created ON tel.agent_outcomes(created_at); 1035 1036 1037 -- ============================================================================= 1038 -- ===== Views ===== 1039 -- ============================================================================= 1040 1041 -- GDPR verification report by country 1042 CREATE OR REPLACE VIEW m333.gdpr_verification_report AS 1043 SELECT 1044 country_code, 1045 COUNT(*) as total_sites, 1046 SUM(CASE WHEN gdpr_verified = TRUE THEN 1 ELSE 0 END) as verified_count, 1047 SUM(CASE WHEN gdpr_verified = FALSE THEN 1 ELSE 0 END) as unverified_count, 1048 SUM(CASE WHEN gdpr_verified IS NULL THEN 1 ELSE 0 END) as unchecked_count, 1049 ROUND(100.0 * SUM(CASE WHEN gdpr_verified = TRUE THEN 1 ELSE 0 END) / COUNT(*), 2) as verification_rate, 1050 MIN(gdpr_verified_at) as first_check, 1051 MAX(gdpr_verified_at) as last_check 1052 FROM m333.sites 1053 WHERE country_code IN ('DE', 'FR', 'IT', 'ES', 'NL', 'BE', 'AT', 'SE', 'DK', 'NO', 'IE', 'UK', 'PL') 1054 GROUP BY country_code 1055 ORDER BY total_sites DESC; 1056 1057 -- Country pricing summary by tier 1058 CREATE OR REPLACE VIEW m333.v_country_pricing_summary AS 1059 SELECT 1060 pricing_tier, 1061 COUNT(*) as country_count, 1062 ROUND(AVG(price_usd) / 100.0, 2) as avg_price_usd, 1063 MIN(price_usd) / 100.0 as min_price_usd, 1064 MAX(price_usd) / 100.0 as max_price_usd, 1065 STRING_AGG(country_code, ',') as countries 1066 FROM m333.countries 1067 WHERE is_active = TRUE 1068 GROUP BY pricing_tier 1069 ORDER BY avg_price_usd DESC; 1070 1071 1072 -- ============================================================================= 1073 -- ===== Autovacuum Tuning for High-Write Tables ===== 1074 -- ============================================================================= 1075 1076 -- sites: high update frequency (status changes, scoring, enrichment) 1077 ALTER TABLE m333.sites SET ( 1078 autovacuum_vacuum_scale_factor = 0.05, 1079 autovacuum_analyze_scale_factor = 0.02 1080 ); 1081 1082 -- site_status: append-only audit log, very high insert rate 1083 ALTER TABLE m333.site_status SET ( 1084 autovacuum_vacuum_scale_factor = 0.1, 1085 autovacuum_analyze_scale_factor = 0.05 1086 ); 1087 1088 -- messages: frequent inserts and status updates 1089 ALTER TABLE m333.messages SET ( 1090 autovacuum_vacuum_scale_factor = 0.05, 1091 autovacuum_analyze_scale_factor = 0.02 1092 ); 1093 1094 -- llm_usage: append-only, high insert rate 1095 ALTER TABLE m333.llm_usage SET ( 1096 autovacuum_vacuum_scale_factor = 0.1, 1097 autovacuum_analyze_scale_factor = 0.05 1098 ); 1099 1100 -- pipeline_metrics: append-only telemetry 1101 ALTER TABLE tel.pipeline_metrics SET ( 1102 autovacuum_vacuum_scale_factor = 0.1, 1103 autovacuum_analyze_scale_factor = 0.05 1104 ); 1105 1106 -- system_metrics: append-only telemetry, very high insert rate 1107 ALTER TABLE tel.system_metrics SET ( 1108 autovacuum_vacuum_scale_factor = 0.1, 1109 autovacuum_analyze_scale_factor = 0.05 1110 ); 1111 1112 -- cron_job_logs: append-only operational logs 1113 ALTER TABLE ops.cron_job_logs SET ( 1114 autovacuum_vacuum_scale_factor = 0.1, 1115 autovacuum_analyze_scale_factor = 0.05 1116 ); 1117 1118 -- agent_logs: append-only debug/info logs 1119 ALTER TABLE tel.agent_logs SET ( 1120 autovacuum_vacuum_scale_factor = 0.1, 1121 autovacuum_analyze_scale_factor = 0.05 1122 );