90 lines
3.3 KiB
SQL
90 lines
3.3 KiB
SQL
|
-- Revenue Cockpit: projects, goals, tracking, agent task links
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS revenue_cockpit_goals (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
vision_text TEXT NOT NULL DEFAULT '',
|
||
|
|
horizon_text TEXT DEFAULT '',
|
||
|
|
tagline TEXT DEFAULT '',
|
||
|
|
is_active BOOLEAN DEFAULT TRUE,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS revenue_projects (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
name VARCHAR(255) NOT NULL,
|
||
|
|
category VARCHAR(32) DEFAULT 'deal',
|
||
|
|
margin_month NUMERIC(14, 2),
|
||
|
|
margin_year NUMERIC(14, 2),
|
||
|
|
target_revenue NUMERIC(14, 2),
|
||
|
|
next_steps TEXT,
|
||
|
|
status VARCHAR(32) DEFAULT 'active',
|
||
|
|
priority VARCHAR(16) DEFAULT 'normal',
|
||
|
|
sort_order INTEGER DEFAULT 0,
|
||
|
|
cockpit_project_id INTEGER REFERENCES cockpit_projects(id) ON DELETE SET NULL,
|
||
|
|
metadata JSONB DEFAULT '{}'::jsonb,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_revenue_projects_status ON revenue_projects(status, sort_order);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_revenue_projects_category ON revenue_projects(category);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS revenue_objectives (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
project_id INTEGER NOT NULL REFERENCES revenue_projects(id) ON DELETE CASCADE,
|
||
|
|
title VARCHAR(255) NOT NULL,
|
||
|
|
description TEXT,
|
||
|
|
due_date DATE,
|
||
|
|
status VARCHAR(32) DEFAULT 'open',
|
||
|
|
priority VARCHAR(16) DEFAULT 'normal',
|
||
|
|
sort_order INTEGER DEFAULT 0,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_revenue_objectives_project ON revenue_objectives(project_id, status);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS revenue_import_runs (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
source_file VARCHAR(512) NOT NULL,
|
||
|
|
sheet_name VARCHAR(128) NOT NULL,
|
||
|
|
rows_imported INTEGER DEFAULT 0,
|
||
|
|
goals_imported BOOLEAN DEFAULT FALSE,
|
||
|
|
imported_by VARCHAR(64) DEFAULT 'ceo',
|
||
|
|
metadata JSONB DEFAULT '{}'::jsonb,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS revenue_change_log (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
entity_type VARCHAR(32) NOT NULL,
|
||
|
|
entity_id INTEGER NOT NULL,
|
||
|
|
field_name VARCHAR(64) NOT NULL,
|
||
|
|
old_value TEXT,
|
||
|
|
new_value TEXT,
|
||
|
|
changed_by VARCHAR(64) DEFAULT 'ceo',
|
||
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_revenue_change_log_entity ON revenue_change_log(entity_type, entity_id, created_at DESC);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS revenue_snapshots (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
snapshot_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
||
|
|
total_margin_year NUMERIC(14, 2) DEFAULT 0,
|
||
|
|
total_margin_month NUMERIC(14, 2) DEFAULT 0,
|
||
|
|
active_projects INTEGER DEFAULT 0,
|
||
|
|
open_objectives INTEGER DEFAULT 0,
|
||
|
|
open_agent_tasks INTEGER DEFAULT 0,
|
||
|
|
payload JSONB DEFAULT '{}'::jsonb,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
|
UNIQUE (snapshot_date)
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE agent_tasks ADD COLUMN IF NOT EXISTS revenue_project_id INTEGER REFERENCES revenue_projects(id) ON DELETE SET NULL;
|
||
|
|
ALTER TABLE agent_tasks ADD COLUMN IF NOT EXISTS revenue_objective_id INTEGER REFERENCES revenue_objectives(id) ON DELETE SET NULL;
|
||
|
|
ALTER TABLE agent_tasks ADD COLUMN IF NOT EXISTS source VARCHAR(32) DEFAULT 'manual';
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_agent_tasks_revenue ON agent_tasks(revenue_project_id, status);
|