AI Cloud Call Center SaaS: A Technical Architecture Case Study

				
					-- Conceptual SQL Schema — Illustrative Only. Not a documented production schema.

-- 1. Base tables with no dependencies
CREATE TABLE tenants (
    id              BIGINT PRIMARY KEY,
    business_name   VARCHAR(255) NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE credit_plans (
    id              BIGINT PRIMARY KEY,
    name            VARCHAR(255),
    credit_amount   INT,
    price_amount    DECIMAL(10,2)
);

-- 2. Tables that depend only on tenants
CREATE TABLE users (
    id              BIGINT PRIMARY KEY,
    tenant_id       BIGINT NOT NULL,
    role            VARCHAR(50) NOT NULL, -- 'super_admin' | 'business_customer' | 'agent'
    email           VARCHAR(255) NOT NULL, -- unique per tenant, not globally (see constraint below)
    auth_provider   VARCHAR(50),           -- 'google' | 'facebook'
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE call_flows (
    id              BIGINT PRIMARY KEY,
    tenant_id       BIGINT NOT NULL,
    name            VARCHAR(255),
    definition      JSON NOT NULL -- node/edge graph (Section 10); JSON type is illustrative, adapt to the selected SQL engine
);

CREATE TABLE contacts (
    id              BIGINT PRIMARY KEY,
    tenant_id       BIGINT NOT NULL,
    full_name       VARCHAR(255),
    phone_number    VARCHAR(32),
    email           VARCHAR(255),
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE phone_numbers (
    id              BIGINT PRIMARY KEY,
    tenant_id       BIGINT NOT NULL,
    number          VARCHAR(32) NOT NULL,
    provider        VARCHAR(50) -- 'twilio' | 'plivo'
);

CREATE TABLE credits (
    id              BIGINT PRIMARY KEY,
    tenant_id       BIGINT NOT NULL UNIQUE,
    balance         DECIMAL(12,2) NOT NULL DEFAULT 0
);

CREATE TABLE kyc_requests (
    id              BIGINT PRIMARY KEY,
    tenant_id       BIGINT NOT NULL,
    status          VARCHAR(50) DEFAULT 'pending', -- 'pending' | 'approved' | 'rejected'
    submitted_at    TIMESTAMP,
    reviewed_by     BIGINT,
    reviewed_at     TIMESTAMP
);

CREATE TABLE support_tickets (
    id              BIGINT PRIMARY KEY,
    tenant_id       BIGINT NOT NULL,
    raised_by       BIGINT NOT NULL,
    subject         VARCHAR(255),
    status          VARCHAR(50) DEFAULT 'open',
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 3. Tables that depend on users
CREATE TABLE agents_staff (
    id              BIGINT PRIMARY KEY,
    tenant_id       BIGINT NOT NULL,
    user_id         BIGINT NOT NULL,
    display_name    VARCHAR(255)
);

-- 4. Tables that depend on agents_staff / call_flows
CREATE TABLE ai_agents (
    id                  BIGINT PRIMARY KEY,
    tenant_id           BIGINT NOT NULL,
    name                VARCHAR(255) NOT NULL,
    persona             TEXT,
    voice_provider      VARCHAR(50) DEFAULT 'elevenlabs',
    telephony_channel   VARCHAR(50), -- 'twilio' | 'plivo' | 'sip'
    call_flow_id        BIGINT NOT NULL
);

-- 5. Tables that depend on contacts / ai_agents
CREATE TABLE calls (
    id              BIGINT PRIMARY KEY,
    tenant_id       BIGINT NOT NULL,
    ai_agent_id     BIGINT,
    contact_id      BIGINT NOT NULL,
    channel         VARCHAR(50), -- 'twilio' | 'plivo' | 'sip' | 'website_widget'
    direction       VARCHAR(20), -- 'inbound' | 'outbound'
    status          VARCHAR(50),
    started_at      TIMESTAMP,
    ended_at        TIMESTAMP
);

CREATE TABLE sms_messages (
    id              BIGINT PRIMARY KEY,
    tenant_id       BIGINT NOT NULL,
    contact_id      BIGINT NOT NULL,
    direction       VARCHAR(20),
    body            TEXT,
    sent_at         TIMESTAMP
);

-- 6. Tables that depend on calls
CREATE TABLE call_logs (
    id                  BIGINT PRIMARY KEY,
    call_id             BIGINT NOT NULL UNIQUE,
    duration_seconds    INT,
    outcome             VARCHAR(50)
);

CREATE TABLE call_recordings (
    id              BIGINT PRIMARY KEY,
    call_id         BIGINT NOT NULL,
    storage_url     VARCHAR(1024),
    recorded        BOOLEAN DEFAULT FALSE -- subject to tenant configuration, Section 21
);

CREATE TABLE call_notes (
    id                  BIGINT PRIMARY KEY,
    call_id             BIGINT NOT NULL,
    author_agent_id     BIGINT,
    note                TEXT,
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE leads (
    id                  BIGINT PRIMARY KEY,
    tenant_id           BIGINT NOT NULL,
    contact_id          BIGINT NOT NULL,
    qualification       VARCHAR(50), -- illustrative values, e.g. 'hot' | 'warm' | 'cold'
    source_call_id      BIGINT,
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tasks (
    id                  BIGINT PRIMARY KEY,
    tenant_id           BIGINT NOT NULL,
    lead_id             BIGINT NOT NULL,
    assigned_agent_id   BIGINT,
    status              VARCHAR(50) DEFAULT 'open',
    due_at              TIMESTAMP
);

-- 7. Usage / billing ledger tables (Section 20)
CREATE TABLE usage_ledger (
    id                  BIGINT PRIMARY KEY,
    tenant_id           BIGINT NOT NULL,
    usage_type          VARCHAR(50), -- 'call_minute' | 'sms'
    reference_id        BIGINT,
    quantity            DECIMAL(10,2),
    credits_deducted    DECIMAL(10,2),
    reconciliation_state VARCHAR(20) DEFAULT 'pending', -- 'pending' | 'applied' | 'flagged'
    occurred_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE billing (
    id              BIGINT PRIMARY KEY,
    tenant_id       BIGINT NOT NULL,
    credit_plan_id  BIGINT NOT NULL,
    amount_paid     DECIMAL(10,2),
    paid_at         TIMESTAMP
);

-- 8. Foreign keys added after every referenced table exists
ALTER TABLE users            ADD CONSTRAINT fk_users_tenant            FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE users            ADD CONSTRAINT uq_users_tenant_email      UNIQUE (tenant_id, email);
ALTER TABLE call_flows       ADD CONSTRAINT fk_callflows_tenant        FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE contacts         ADD CONSTRAINT fk_contacts_tenant         FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE phone_numbers    ADD CONSTRAINT fk_numbers_tenant          FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE credits          ADD CONSTRAINT fk_credits_tenant          FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE kyc_requests     ADD CONSTRAINT fk_kyc_tenant              FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE kyc_requests     ADD CONSTRAINT fk_kyc_reviewer            FOREIGN KEY (reviewed_by) REFERENCES users(id);
ALTER TABLE support_tickets  ADD CONSTRAINT fk_tickets_tenant          FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE support_tickets  ADD CONSTRAINT fk_tickets_user            FOREIGN KEY (raised_by) REFERENCES users(id);
ALTER TABLE agents_staff     ADD CONSTRAINT fk_staff_tenant            FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE agents_staff     ADD CONSTRAINT fk_staff_user              FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE ai_agents        ADD CONSTRAINT fk_aiagents_tenant         FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE ai_agents        ADD CONSTRAINT fk_aiagents_callflow       FOREIGN KEY (call_flow_id) REFERENCES call_flows(id);
ALTER TABLE calls            ADD CONSTRAINT fk_calls_tenant            FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE calls            ADD CONSTRAINT fk_calls_agent             FOREIGN KEY (ai_agent_id) REFERENCES ai_agents(id);
ALTER TABLE calls            ADD CONSTRAINT fk_calls_contact           FOREIGN KEY (contact_id) REFERENCES contacts(id);
ALTER TABLE sms_messages     ADD CONSTRAINT fk_sms_tenant              FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE sms_messages     ADD CONSTRAINT fk_sms_contact             FOREIGN KEY (contact_id) REFERENCES contacts(id);
ALTER TABLE call_logs        ADD CONSTRAINT fk_calllogs_call           FOREIGN KEY (call_id) REFERENCES calls(id);
ALTER TABLE call_recordings  ADD CONSTRAINT fk_recordings_call         FOREIGN KEY (call_id) REFERENCES calls(id);
ALTER TABLE call_notes       ADD CONSTRAINT fk_notes_call              FOREIGN KEY (call_id) REFERENCES calls(id);
ALTER TABLE call_notes       ADD CONSTRAINT fk_notes_agent             FOREIGN KEY (author_agent_id) REFERENCES agents_staff(id);
ALTER TABLE leads            ADD CONSTRAINT fk_leads_tenant            FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE leads            ADD CONSTRAINT fk_leads_contact           FOREIGN KEY (contact_id) REFERENCES contacts(id);
ALTER TABLE leads            ADD CONSTRAINT fk_leads_call              FOREIGN KEY (source_call_id) REFERENCES calls(id);
ALTER TABLE tasks            ADD CONSTRAINT fk_tasks_tenant            FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE tasks            ADD CONSTRAINT fk_tasks_lead              FOREIGN KEY (lead_id) REFERENCES leads(id);
ALTER TABLE tasks            ADD CONSTRAINT fk_tasks_agent             FOREIGN KEY (assigned_agent_id) REFERENCES agents_staff(id);
ALTER TABLE usage_ledger     ADD CONSTRAINT fk_usage_tenant            FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE billing          ADD CONSTRAINT fk_billing_tenant          FOREIGN KEY (tenant_id) REFERENCES tenants(id);
ALTER TABLE billing          ADD CONSTRAINT fk_billing_plan            FOREIGN KEY (credit_plan_id) REFERENCES credit_plans(id);