manutej/luxor-claude-marketplace/postgresql

-- Core tables for customer support system
CREATE TABLE users (
    user_id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    full_name VARCHAR(255) NOT NULL,
    role VARCHAR(50) NOT NULL CHECK (role IN ('customer', 'agent', 'admin')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT true,
    metadata JSONB DEFAULT '{}'::jsonb
);
 
CREATE TABLE tickets (
    ticket_id BIGSERIAL PRIMARY KEY,
    ticket_number VARCHAR(50) NOT NULL UNIQUE,
    customer_id BIGINT NOT NULL REFERENCES users(user_id),
    assigned_agent_id BIGINT REFERENCES users(user_id),
    subject VARCHAR(500) NOT NULL,
    description TEXT NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'open'
        CHECK (status IN ('open', 'in_progress', 'waiting', 'resolved', 'closed')),
    priority VARCHAR(20) NOT NULL DEFAULT 'medium'
        CHECK (priority IN ('low', 'medium', 'high', 'critical')),
    category VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP WITH TIME ZONE,
    closed_at TIMESTAMP WITH TIME ZONE,
    first_response_at TIMESTAMP WITH TIME ZONE,
    tags TEXT[] DEFAULT '{}',
    custom_fields JSONB DEFAULT '{}'::jsonb,
    search_vector tsvector
);
 
CREATE TABLE ticket_comments (
    comment_id BIGSERIAL PRIMARY KEY,
    ticket_id BIGINT NOT NULL REFERENCES tickets(ticket_id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(user_id),
    comment_text TEXT NOT NULL,
    is_internal BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    attachments JSONB DEFAULT '[]'::jsonb,
    search_vector tsvector
);
 
CREATE TABLE ticket_history (
    history_id BIGSERIAL PRIMARY KEY,
    ticket_id BIGINT NOT NULL REFERENCES tickets(ticket_id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(user_id),
    field_name VARCHAR(100) NOT NULL,
    old_value TEXT,
    new_value TEXT,
    changed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE organizations (
    org_id BIGSERIAL PRIMARY KEY,
    org_name VARCHAR(255) NOT NULL UNIQUE,
    domain VARCHAR(255),
    plan_type VARCHAR(50) NOT NULL DEFAULT 'free',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    settings JSONB DEFAULT '{}'::jsonb
);
 
CREATE TABLE user_organizations (
    user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    org_id BIGINT NOT NULL REFERENCES organizations(org_id) ON DELETE CASCADE,
    role VARCHAR(50) NOT NULL DEFAULT 'member',
    joined_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, org_id)
);

CREATE TABLE audit_logs (
    log_id BIGSERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    record_id BIGINT NOT NULL,
    action VARCHAR(20) NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    user_id BIGINT REFERENCES users(user_id),
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    ip_address INET,
    user_agent TEXT
);
 
-- Create a partition for audit logs by month
CREATE TABLE audit_logs_y2025m01 PARTITION OF audit_logs
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- Index for finding tickets by status
CREATE INDEX idx_tickets_status ON tickets(status) WHERE status != 'closed';
 
-- Index for finding tickets by customer
CREATE INDEX idx_tickets_customer ON tickets(customer_id, created_at DESC);
 
-- Index for finding tickets by assigned agent
CREATE INDEX idx_tickets_agent ON tickets(assigned_agent_id, status)
    WHERE assigned_agent_id IS NOT NULL;
 
-- Composite index for ticket filtering
CREATE INDEX idx_tickets_status_priority_created
    ON tickets(status, priority, created_at DESC);
 
-- Index for email lookups
CREATE INDEX idx_users_email ON users(email) WHERE is_active = true;
 
-- Index for ticket number lookups
CREATE UNIQUE INDEX idx_tickets_ticket_number ON tickets(ticket_number);

-- Full-text search on ticket subject and description
CREATE INDEX idx_tickets_search ON tickets USING GIN(search_vector);
 
-- Update trigger for maintaining search vector
CREATE OR REPLACE FUNCTION tickets_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', COALESCE(NEW.subject, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'B') ||
        setweight(to_tsvector('english', COALESCE(array_to_string(NEW.tags, ' '), '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER tickets_search_update
    BEFORE INSERT OR UPDATE ON tickets
    FOR EACH ROW EXECUTE FUNCTION tickets_search_trigger();
 
-- Full-text search on comments
CREATE INDEX idx_comments_search ON ticket_comments USING GIN(search_vector);
 
CREATE OR REPLACE FUNCTION comments_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector := to_tsvector('english', COALESCE(NEW.comment_text, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER comments_search_update
    BEFORE INSERT OR UPDATE ON ticket_comments
    FOR EACH ROW EXECUTE FUNCTION comments_search_trigger();

-- Index for JSONB containment queries
CREATE INDEX idx_tickets_custom_fields ON tickets USING GIN(custom_fields);
 
-- Index for array tag searches
CREATE INDEX idx_tickets_tags ON tickets USING GIN(tags);
 
-- Index for specific JSONB keys
CREATE INDEX idx_tickets_custom_source
    ON tickets USING GIN((custom_fields -> 'source'));

-- Index only open and in-progress tickets
CREATE INDEX idx_tickets_active
    ON tickets(created_at DESC)
    WHERE status IN ('open', 'in_progress', 'waiting');
 
-- Index unassigned tickets
CREATE INDEX idx_tickets_unassigned
    ON tickets(priority DESC, created_at ASC)
    WHERE assigned_agent_id IS NULL AND status = 'open';
 
-- Index high-priority unresolved tickets
CREATE INDEX idx_tickets_urgent
    ON tickets(created_at ASC)
    WHERE priority IN ('high', 'critical') AND status != 'closed';

-- Search tickets by keyword
SELECT
    ticket_id,
    ticket_number,
    subject,
    ts_rank(search_vector, query) AS rank
FROM
    tickets,
    to_tsquery('english', 'login & problem') AS query
WHERE
    search_vector @@ query
ORDER BY
    rank DESC, created_at DESC
LIMIT 20;
 
-- Advanced search with phrase matching
SELECT
    ticket_id,
    ticket_number,
    subject,
    ts_headline('english', description, query, 'MaxWords=50, MinWords=25') AS excerpt
FROM
    tickets,
    websearch_to_tsquery('english', '"password reset" OR authentication') AS query
WHERE
    search_vector @@ query
ORDER BY
    ts_rank_cd(search_vector, query) DESC
LIMIT 10;

-- Search tickets and comments together
WITH search_results AS (
    SELECT
        'ticket' AS source,
        ticket_id,
        ticket_id AS ref_id,
        subject AS title,
        description AS content,
        created_at,
        ts_rank(search_vector, query) AS rank
    FROM
        tickets,
        to_tsquery('english', 'billing & invoice') AS query
    WHERE
        search_vector @@ query
 
    UNION ALL
 
    SELECT
        'comment' AS source,
        ticket_id,
        comment_id AS ref_id,
        'Comment' AS title,
        comment_text AS content,
        created_at,
        ts_rank(search_vector, query) AS rank
    FROM
        ticket_comments,
        to_tsquery('english', 'billing & invoice') AS query
    WHERE
        search_vector @@ query
)
SELECT * FROM search_results
ORDER BY rank DESC, created_at DESC
LIMIT 50;

-- Agent performance metrics with ranking
SELECT
    u.user_id,
    u.full_name,
    COUNT(t.ticket_id) AS tickets_resolved,
    AVG(EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600)::numeric(10,2) AS avg_resolution_hours,
    RANK() OVER (ORDER BY COUNT(t.ticket_id) DESC) AS volume_rank,
    RANK() OVER (ORDER BY AVG(EXTRACT(EPOCH FROM (t.resolved_at - t.created_at))) ASC) AS speed_rank
FROM
    users u
    JOIN tickets t ON u.user_id = t.assigned_agent_id
WHERE
    u.role = 'agent'
    AND t.resolved_at >= CURRENT_DATE - INTERVAL '30 days'
    AND t.status = 'resolved'
GROUP BY
    u.user_id, u.full_name
ORDER BY
    tickets_resolved DESC;
 
-- Daily ticket trends with moving average
SELECT
    date_trunc('day', created_at) AS ticket_date,
    COUNT(*) AS daily_tickets,
    AVG(COUNT(*)) OVER (ORDER BY date_trunc('day', created_at) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7day
FROM
    tickets
WHERE
    created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY
    date_trunc('day', created_at)
ORDER BY
    ticket_date;

-- Create materialized view for agent performance dashboard
CREATE MATERIALIZED VIEW mv_agent_performance AS
SELECT
    u.user_id,
    u.full_name,
    u.email,
    COUNT(DISTINCT t.ticket_id) AS total_tickets,
    COUNT(DISTINCT CASE WHEN t.status = 'resolved' THEN t.ticket_id END) AS resolved_tickets,
    COUNT(DISTINCT CASE WHEN t.status IN ('open', 'in_progress') THEN t.ticket_id END) AS active_tickets,
    AVG(EXTRACT(EPOCH FROM (COALESCE(t.first_response_at, CURRENT_TIMESTAMP) - t.created_at)) / 3600)::numeric(10,2) AS avg_first_response_hours,
    AVG(CASE
        WHEN t.resolved_at IS NOT NULL
        THEN EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600
    END)::numeric(10,2) AS avg_resolution_hours,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (t.resolved_at - t.created_at)) / 3600) AS median_resolution_hours
FROM
    users u
    LEFT JOIN tickets t ON u.user_id = t.assigned_agent_id
WHERE
    u.role = 'agent'
    AND u.is_active = true
    AND (t.created_at >= CURRENT_DATE - INTERVAL '30 days' OR t.ticket_id IS NULL)
GROUP BY
    u.user_id, u.full_name, u.email;
 
-- Create unique index on materialized view
CREATE UNIQUE INDEX idx_mv_agent_performance_user ON mv_agent_performance(user_id);
 
-- Refresh strategy (daily scheduled job)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_agent_performance;