Reporting DB Changes

CREATE TABLE IF NOT EXISTS public.calls (
    call_id BIGSERIAL PRIMARY KEY,
    direction VARCHAR(20) NOT NULL,
    from_number VARCHAR(50),
    to_number VARCHAR(50),
    primary_agent_id BIGINT,
    group_id BIGINT,
    campaign_id BIGINT,
    start_time TIMESTAMP,
    answer_time TIMESTAMP,
    end_time TIMESTAMP,
    duration_seconds INT,
    talk_time_seconds INT,
    hold_time_seconds INT,
    acw_time_seconds INT,
    wait_time_seconds INT,
    ivr_language VARCHAR(50),
    ivr_input VARCHAR(255),
    was_transferred BOOLEAN DEFAULT FALSE,
    abandoned BOOLEAN DEFAULT FALSE,
    resolved BOOLEAN DEFAULT FALSE,
    sentiment VARCHAR(20),
    call_recording_url TEXT,
    disposition_id BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_primary_agent FOREIGN KEY (primary_agent_id) REFERENCES public.users(id),
    CONSTRAINT fk_group FOREIGN KEY (group_id) REFERENCES public.groups(id),
    CONSTRAINT fk_campaign FOREIGN KEY (campaign_id) REFERENCES public.campaigns(id),
    CONSTRAINT fk_disposition FOREIGN KEY (disposition_id) REFERENCES public.dispositions(id)
);

CREATE TABLE IF NOT EXISTS public.call_segments (
    segment_id BIGSERIAL PRIMARY KEY,
    call_id BIGINT NOT NULL,
    sequence_number INT NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    duration_seconds INT,
    agent_id BIGINT,
    group_id BIGINT,
    transferred_to_agent_id BIGINT,
    from_number VARCHAR(50),
    to_number VARCHAR(50),
    prompt_played VARCHAR(255),
    dtmf_input VARCHAR(50),
    talk_time_seconds INT,
    hold_time_seconds INT,
    acw_time_seconds INT,
    sentiment VARCHAR(20),
    recording_url TEXT,
    resolution_status VARCHAR(50),
    notes TEXT,
    
    CONSTRAINT fk_call FOREIGN KEY (call_id) REFERENCES public.calls(call_id) ON DELETE CASCADE,
    CONSTRAINT fk_agent FOREIGN KEY (agent_id) REFERENCES public.users(id),
    CONSTRAINT fk_group FOREIGN KEY (group_id) REFERENCES public.groups(id),
    CONSTRAINT fk_transferred_agent FOREIGN KEY (transferred_to_agent_id) REFERENCES public.users(id)
);

CREATE TABLE IF NOT EXISTS public.call_crm_links (
    id BIGSERIAL PRIMARY KEY,
    call_id BIGINT NOT NULL,
    module_name TEXT NOT NULL,
    record_id BIGINT NOT NULL,
    relation_type TEXT,
    created_after BOOLEAN DEFAULT FALSE,
    
    CONSTRAINT fk_call FOREIGN KEY (call_id) REFERENCES public.calls(call_id) ON DELETE CASCADE
);

Group Hierachy View

-- PostgreSQL Example
CREATE VIEW group_hierarchy AS
WITH RECURSIVE hierarchy AS (
  SELECT 
    id,
    name,
    parent_group_id,
    id AS root_group_id,
    name AS root_group_name,
    0 AS depth
  FROM groups
  WHERE parent_group_id IS NULL

  UNION ALL

  SELECT 
    g.id,
    g.name,
    g.parent_group_id,
    h.root_group_id,
    h.root_group_name,
    h.depth + 1
  FROM groups g
  INNER JOIN hierarchy h ON g.parent_group_id = h.id
)
SELECT * FROM hierarchy;

Call triggers

Event Name	Description
CALL_STARTED	Call initiated (by customer or agent)
RINGING	Destination phone is ringing
CALL_ANSWERED	Someone answers the call
HOLD_STARTED	Agent puts customer on hold
HOLD_ENDED	Agent resumes from hold
TRANSFER_INITIATED	Agent starts a transfer
TRANSFER_COMPLETED	Another agent picks up
CONFERENCE_JOINED	A 3rd party joins the call
CALL_ENDED	Call disconnected
ACW_STARTED	After Call Work begins
ACW_ENDED	Agent returns to idle/ready
SURVEY_STARTED	Post-call survey starts
SURVEY_COMPLETED	Survey response logged

Call segments

Segment Type	Start Event(s)	End Event(s)	Notes
RINGING	CALL_STARTED	CALL_ANSWERED	Before agent answers
ON_CALL	CALL_ANSWERED	HOLD_STARTED, TRANSFER_INITIATED, or CALL_ENDED	May repeat per agent
HOLD	HOLD_STARTED	HOLD_ENDED	Captures hold durations
TRANSFER	TRANSFER_INITIATED	TRANSFER_COMPLETED	Optional for traceability
ACW	ACW_STARTED	ACW_ENDED	After call wrap-up
SURVEY	SURVEY_STARTED	SURVEY_COMPLETED	Can include CSAT/NPS
BOT_HANDLING	e.g., IVR/Bot connected	Agent connected	Optional in hybrid flows