SysOps Agent
113 lines
4.2 KiB
SQL
113 lines
4.2 KiB
SQL
-- Foodlinkk Command Center — Fase 1 migrations
|
|
|
|
CREATE TABLE IF NOT EXISTS agent_events (
|
|
id SERIAL PRIMARY KEY,
|
|
agent_name VARCHAR(64) NOT NULL,
|
|
agent_type VARCHAR(32) DEFAULT 'openswarm',
|
|
event_type VARCHAR(64) NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
body TEXT,
|
|
metadata JSONB DEFAULT '{}',
|
|
status VARCHAR(32) DEFAULT 'completed',
|
|
related_table VARCHAR(64),
|
|
related_id INTEGER,
|
|
channel VARCHAR(32) DEFAULT 'dashboard',
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
completed_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_agent_events_created ON agent_events(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_agent_events_status ON agent_events(status);
|
|
CREATE INDEX IF NOT EXISTS idx_agent_events_agent ON agent_events(agent_name);
|
|
|
|
CREATE TABLE IF NOT EXISTS clients (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
contact VARCHAR(255),
|
|
email VARCHAR(255),
|
|
stage VARCHAR(64) DEFAULT 'intake',
|
|
sector VARCHAR(128),
|
|
mrr_estimate NUMERIC(12,2),
|
|
notes TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS deals (
|
|
id SERIAL PRIMARY KEY,
|
|
client_id INTEGER REFERENCES clients(id),
|
|
title VARCHAR(255) NOT NULL,
|
|
value NUMERIC(12,2) DEFAULT 0,
|
|
stage VARCHAR(64) DEFAULT 'lead',
|
|
agent_owner VARCHAR(64) DEFAULT 'herman',
|
|
next_action TEXT,
|
|
deadline DATE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS products (
|
|
id SERIAL PRIMARY KEY,
|
|
client_id INTEGER REFERENCES clients(id),
|
|
name VARCHAR(255) NOT NULL,
|
|
status VARCHAR(64) DEFAULT 'concept',
|
|
margin_pct NUMERIC(5,2),
|
|
moq INTEGER,
|
|
shelf_target VARCHAR(255),
|
|
launch_date DATE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS suppliers (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
country VARCHAR(64),
|
|
category VARCHAR(128),
|
|
moq INTEGER,
|
|
lead_time_days INTEGER,
|
|
rating NUMERIC(3,1),
|
|
contact TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS agent_tasks (
|
|
id SERIAL PRIMARY KEY,
|
|
agent_name VARCHAR(64) NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
status VARCHAR(32) DEFAULT 'pending',
|
|
priority VARCHAR(16) DEFAULT 'normal',
|
|
assigned_by VARCHAR(64) DEFAULT 'herman',
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
completed_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS daily_briefings (
|
|
id SERIAL PRIMARY KEY,
|
|
content TEXT NOT NULL,
|
|
generated_by VARCHAR(64) DEFAULT 'herman',
|
|
metadata JSONB DEFAULT '{}',
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
INSERT INTO clients (name, contact, stage, sector, mrr_estimate, notes)
|
|
SELECT 'Chai N Masala', 'Raj Patel', 'active', 'Spices & Tea', 4500, 'Launch partner — retail expansion'
|
|
WHERE NOT EXISTS (SELECT 1 FROM clients WHERE name = 'Chai N Masala');
|
|
|
|
INSERT INTO clients (name, contact, stage, sector, mrr_estimate, notes)
|
|
SELECT 'Van den Tweel', 'Inkoper', 'proposal', 'Retail', 12000, 'Category introduction pipeline'
|
|
WHERE NOT EXISTS (SELECT 1 FROM clients WHERE name = 'Van den Tweel');
|
|
|
|
INSERT INTO deals (client_id, title, value, stage, next_action, deadline)
|
|
SELECT c.id, 'Q3 spice range listing', 85000, 'proposal', 'Send updated margin sheet', CURRENT_DATE + 14
|
|
FROM clients c WHERE c.name = 'Chai N Masala'
|
|
AND NOT EXISTS (SELECT 1 FROM deals WHERE title = 'Q3 spice range listing');
|
|
|
|
INSERT INTO agent_events (agent_name, agent_type, event_type, title, body, status, channel)
|
|
SELECT 'herman', 'co_ceo', 'briefing', 'Command Center online', 'Foodlinkk AI Command Center Fase 1 gestart.', 'completed', 'dashboard'
|
|
WHERE NOT EXISTS (SELECT 1 FROM agent_events WHERE title = 'Command Center online');
|
|
|
|
INSERT INTO agent_events (agent_name, agent_type, event_type, title, body, status, channel)
|
|
SELECT 'marketing', 'marketing', 'draft', 'Instagram draft — sourcing dienst', 'Behind every great food brand is a supply chain that works. At Foodlinkk we connect producers to shelf. #foodinnovation #foodlinkk', 'needs_approval', 'dashboard'
|
|
WHERE NOT EXISTS (SELECT 1 FROM agent_events WHERE title = 'Instagram draft — sourcing dienst');
|