# Database Schema ## Connection - **Driver**: `pg` (node-postgres) - **Extension**: `pgvector` (for embeddings/semantic search) - **Pool**: max 10 connections, SSL enabled for cloud --- ## Tables by Domain ### Project Management | Table | Key Columns | Notes | |-------|-------------|-------| | `projects` | id, name, brand_id, status, created_at | Top-level client projects | | `analyses` | id, project_id, type, result, created_at | Agent analysis results | | `sandbox_tests` | id, name, config, result, created_at | LLM sandbox test cases | --- ### Brand & CRM | Table | Key Columns | Notes | |-------|-------------|-------| | `brands` | id, name, industry, voice, guidelines_url | Brand profiles | | `conversations` | id, brand_name, messages, created_at | Brand-level chat threads | | `crm_clients` | id, name, email, phone, company | Encrypted PII fields | | `crm_projects` | id, client_id, title, status, budget | Client project records | | `crm_project_attachments` | id, project_id, filename, url | File attachments | | `crm_feedback` | id, project_id, rating, notes | Client feedback | | `crm_gmail_tokens` | id, client_id, access_token, refresh_token | Gmail OAuth tokens | | `crm_contacts` | id, name, email, phone, company, linkedin | Contact records (PII encrypted) | | `crm_contact_project_links` | contact_id, project_id | M:M join table | --- ### Intelligence Module | Table | Key Columns | Notes | |-------|-------------|-------| | `intelligence_topics` | id, name, keywords, brand_id | Monitored topics | | `intelligence_sources` | id, topic_id, url, type | Data sources | | `intelligence_news` | id, topic_id, title, summary, url, published_at | News articles | | `intelligence_summaries` | id, topic_id, summary, generated_at | AI summaries | | `intelligence_edm_history` | id, topic_id, sent_at, recipient_count | EDM send log | --- ### Ziwei Astrology System | Table | Key Columns | Notes | |-------|-------------|-------| | `ziwei_palaces` | id, branch, name_zh, name_en | 12 Earthly Branch palaces | | `ziwei_stars` | id, name, category, element, type, meanings | 100+ stars | | `ziwei_rules` | id, rule_text, conditions, outcomes | Interpretation rules | | `ziwei_rule_evaluations` | id, chart_id, rule_id, result, created_at | Rule application history | | `ziwei_interpretation_rules` | id, rule_id, enhanced_text, llm_model | LLM-enhanced rules | | `ziwei_birth_charts` | id, user_id, birth_date, birth_time, gender, chart_json | Saved charts | | `ziwei_rule_feedback` | id, chart_id, rule_id, rating, notes | User feedback | | `ziwei_rule_statistics` | id, rule_id, accuracy_score, usage_count | Rule metrics | | `ziwei_enhanced_interpretations` | id, chart_id, text, model, created_at | AI-generated insights | | `ziwei_conversations` | id, chart_id, title, created_at | Discussion threads | | `ziwei_conversation_messages` | id, conversation_id, role, content | Individual messages | | `ziwei_compatibility_analyses` | id, chart_id_a, chart_id_b, result | Relationship analyses | | `ziwei_insights` | id, chart_id, category, insight, created_at | Personalized insights | --- ### Social Media | Table | Key Columns | Notes | |-------|-------------|-------| | `social_states` | id, brand_id, state_json | Campaign workflow state | | `social_campaigns` | id, brand_id, name, platform, status | Social campaigns | | `social_artefacts` | id, campaign_id, type, content | Generated content artefacts | | `social_content_posts` | id, campaign_id, text, media_url, scheduled_at | Posts | | `social_ad_campaigns` | id, brand_id, platform, budget, targeting | Paid ad campaigns | | `social_kpi_definitions` | id, brand_id, name, target_value | KPI definitions | | `social_content_drafts` | id, brand_id, content, status, task_id | Draft content | | `social_strategy` | id, brand_id, strategy_json, created_at | Strategy records | | `social_interactive_content` | id, brand_id, type, content | Polls, quizzes, etc. | | `social_calendar` | id, brand_id, date, post_id, platform | Content calendar | | `social_trend_research` | id, brand_id, trends_json, researched_at | Trend data | | `social_monitoring` | id, brand_id, mentions, sentiment, captured_at | Brand monitoring | | `social_community_management` | id, brand_id, data_json | Community data | --- ### Research Module | Table | Key Columns | Notes | |-------|-------------|-------| | `research_business` | id, brand_id, data_json, created_at | Business intelligence | | `research_competitors` | id, brand_id, competitor_name, analysis_json | Competitor data | | `research_audience` | id, brand_id, data_json | Audience research | | `research_audience_segments` | id, brand_id, segment_name, profile_json | Audience segments | | `research_products` | id, brand_id, product_name, analysis_json | Product research | | `brand_products_services` | id, brand_id, name, type, status | Products/services catalog | --- ### Tender Intelligence | Table | Key Columns | Notes | |-------|-------------|-------| | `tender_sources` | id, name, url, type, is_active | Tender data sources | | `tender_opportunities` | id, source_id, title, deadline, value, status | Tender listings | | `tender_evaluations` | id, tender_id, score, recommendation, created_at | AI evaluations | | `tender_digest_issues` | id, issue_number, content, sent_at | Digest issues | --- ### RecruitAI | Table | Key Columns | Notes | |-------|-------------|-------| | `recruitai_leads` | id, name, email, phone, skills, status | Recruitment leads (PII encrypted) | | `recruitai_lead_messages` | id, lead_id, role, content, created_at | Conversation history | | `recruitai_lead_skills` | id, lead_id, skill, level, assessed_at | Skill assessments | | `recruitai_followup_tasks` | id, lead_id, task_type, scheduled_at, done | Followup tasks | --- ### Print Finance | Table | Key Columns | Notes | |-------|-------------|-------| | `print_work_units` | id, job_id, type, quantity, unit_cost | Work units | | `print_revenue` | id, job_id, amount, client, invoice_ref | Revenue records | | `print_costs` | id, job_id, category_id, amount, description | Cost records | | `print_inventory` | id, material, quantity, unit, cost_per_unit | Inventory | | `print_material_log` | id, job_id, material_id, quantity_used, date | Material usage | | `print_machine_log` | id, job_id, machine, hours, date | Machine usage | | `print_labour_log` | id, job_id, worker, hours, rate, date | Labour records | | `print_job_costs` | id, job_id, total_material, total_machine, total_labour | Job cost summary | | `print_uploads` | id, filename, type, uploaded_at, processed | File uploads | | `print_cost_categories` | id, name, type | Cost categories | | `print_invoices` | id, job_id, client, amount, issued_at, pdf_url | Invoices | | `print_material_rates` | id, material, unit, rate, effective_date | Material rates | | `print_scenarios` | id, name, assumptions_json, result_json | Cost scenarios | | `print_settings` | id, key, value | Settings key-value store | --- ### Radiance (PR & Martech) | Table | Key Columns | Notes | |-------|-------------|-------| | `radiance_enquiries` | id, name, email, message, submitted_at | Contact form submissions | | `radiance_media_assets` | id, filename, url, type, tags | Media library | | `radiance_pr_gallery` | id, title, image_url, event_date, tags | PR gallery | | `radiance_news_clippings` | id, headline, source, url, published_at | News clippings | | `radiance_blog` | id, slug, title, body, status, published_at | Blog posts | | `radiance_case_studies` | id, slug, title, client, content, published_at | Case studies | --- ### TEDx | Table | Key Columns | Notes | |-------|-------------|-------| | `tedx_media_assets` | id, filename, public_url, cdn_url, type, created_at | Media with CDN fallback | --- ### QR Code System | Table | Key Columns | Notes | |-------|-------------|-------| | `qr_users` | id, email, credits, created_at | QR user accounts | | `qr_codes` | id, user_id, url, image_url, created_at | Generated QR codes | | `qr_topup_packages` | id, name, credits, price | Topup packages | | `qr_topup_transactions` | id, user_id, package_id, amount, created_at | Topup history | | `qr_nfc_orders` | id, user_id, quantity, status, created_at | NFC card orders | --- ### Admin | Table | Key Columns | Notes | |-------|-------------|-------| | `super_admin_users` | id, email, password_hash, last_login | Admin accounts | --- ## Vector / Embeddings pgvector is used for semantic search in the knowledge management layer: - Table: `knowledge_documents` (managed by `knowledge/embeddings/vector-store.ts`) - Columns: `id`, `content`, `embedding` (vector), `metadata`, `source`, `created_at` - Search: cosine similarity via `<=>` operator --- ## PII Encryption The following fields are encrypted at rest via `services/encryption.js`: - `crm_clients`: email, phone - `crm_contacts`: email, phone - `recruitai_leads`: email, phone