Database Schema
Use TABLES and BOTS constants from src/types/database.ts. Never hardcode table names.
TABLES & BOTS constants
Import: import { TABLES, BOTS } from "@/types/database". BOTS: SHARED, SUPPORT, SALES, ESTIMATE, EMAIL_MONITOR.
Core tables
- app_config — Config by bot/category/key; value_type: string, number, boolean, json, text_long.
- customers — id, email, name, phone, is_active, notes.
- customer_properties — customer_id, account_pin (tech portal), address, monitoring_status, etc.
Support tables
- support_knowledge_base — category, problem, solution, keywords[], embedding (vector).
- support_pending_knowledge — suggested KB entries; status: pending/approved/rejected.
- support_tickets — reference_number (TKT-XXXXXX), customer_id, status, priority, assigned_to, etc.
- support_ticket_notes — ticket_id, author_type (admin/tech/customer/system), content, is_internal.
- support_portal_tokens — customer email, token, expires_at (7-day).
- support_chat_sessions / support_chat_messages — Support chatbot sessions and messages.
Estimate tables
- est_categories — slug (PK), display_name, display_order.
- est_products — product_code, category_slug, unit_price, item_type, qbo_sku.
- est_flow_steps — step_key (PK), field_name, display_question, input_type, options (jsonb).
- est_flow_transitions — from_step_key, to_step_key, condition (expression).
- est_product_rules — condition, product_code, qty_expression.
- est_sessions / est_session_answers — Estimate chatbot state.
- est_estimates — estimate_number (EST-XXXXXXXX), line_items (jsonb), totals, qbo_sync.
Full column-level detail is in docs/PROJECT_REFERENCE_FOR_AI.md and src/types/database.ts. Run npm run db:schema-dump to dump schema to migrations.