ozan
a81a450e7e
Merged into tinqs/studio: - cmd/tinqs-cli/ — tinqs-cli (Go binary, from bot/cli) - cmd/tea/ — Gitea CLI tool (from tinqs/cli-tea) - services/bot/ — Bot service (from tinqs-ltd/bot on git.arikigame.com) - services/admin/ — Admin panel (from tinqs/admin) - services/team-tool/ — Team Tool (from tinqs/team-tool) - services/proxy/ — tinqs-proxy (from bot/proxy) - web/landing/ — tinqs.com website (from tinqs/website) - web/docs/ — Platform docs (from tinqs/docs) - web/blog/ — Blog (placeholder) - runner/ — Ephemeral CI runner (from tinqs/runner) All source repos will be deleted after verification.
144 lines
4.2 KiB
SQL
144 lines
4.2 KiB
SQL
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
email TEXT UNIQUE NOT NULL,
|
|
display_name TEXT,
|
|
avatar_url TEXT,
|
|
role TEXT DEFAULT 'member' CHECK (role IN ('admin', 'member')),
|
|
gitea_username TEXT,
|
|
github_login TEXT,
|
|
password_hash TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
last_seen_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS organizations (
|
|
id SERIAL PRIMARY KEY,
|
|
slug TEXT UNIQUE NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
shared_password_hash TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS organization_members (
|
|
organization_id INT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
|
user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member')),
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
PRIMARY KEY (organization_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_org_members_user ON organization_members(user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_id INT REFERENCES users(id),
|
|
action TEXT NOT NULL,
|
|
target TEXT,
|
|
metadata JSONB,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_audit_log_user ON audit_log(user_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_log_action ON audit_log(action, created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS portal_pages (
|
|
id SERIAL PRIMARY KEY,
|
|
slug TEXT UNIQUE NOT NULL,
|
|
title TEXT NOT NULL,
|
|
content TEXT,
|
|
source_repo TEXT,
|
|
source_path TEXT,
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS infra_tokens (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
service TEXT NOT NULL,
|
|
location TEXT NOT NULL,
|
|
env_var_name TEXT,
|
|
expires_at TIMESTAMPTZ,
|
|
last_rotated_at TIMESTAMPTZ,
|
|
machines TEXT[] DEFAULT '{}',
|
|
notes TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS infra_services (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
url TEXT,
|
|
tailscale_ip TEXT,
|
|
health_endpoint TEXT,
|
|
last_check_at TIMESTAMPTZ,
|
|
last_status TEXT DEFAULT 'unknown' CHECK (last_status IN ('ok', 'error', 'timeout', 'unknown')),
|
|
cost_monthly TEXT,
|
|
provider TEXT,
|
|
notes TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_infra_tokens_service ON infra_tokens(service);
|
|
CREATE INDEX IF NOT EXISTS idx_infra_tokens_expires ON infra_tokens(expires_at);
|
|
CREATE INDEX IF NOT EXISTS idx_infra_services_status ON infra_services(last_status);
|
|
|
|
-- Gateway merge (see db/migrations/004_gateway.sql)
|
|
CREATE TABLE IF NOT EXISTS gateway_votes (
|
|
user_email TEXT NOT NULL,
|
|
slug TEXT NOT NULL,
|
|
item_id TEXT NOT NULL,
|
|
voted_at TIMESTAMPTZ DEFAULT NOW(),
|
|
PRIMARY KEY (user_email, slug, item_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS gateway_reads (
|
|
id SERIAL PRIMARY KEY,
|
|
user_email TEXT NOT NULL,
|
|
slug TEXT NOT NULL,
|
|
reaction TEXT NOT NULL,
|
|
read_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE (user_email, slug, reaction)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS gateway_agents (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id TEXT NOT NULL,
|
|
title TEXT NOT NULL DEFAULT 'New chat',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS gateway_agent_messages (
|
|
id TEXT PRIMARY KEY,
|
|
agent_id UUID NOT NULL REFERENCES gateway_agents(id) ON DELETE CASCADE,
|
|
role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
|
|
parts JSONB NOT NULL DEFAULT '[]',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_gateway_agent_messages_agent ON gateway_agent_messages(agent_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS sentinel_kv (
|
|
key TEXT PRIMARY KEY,
|
|
value JSONB NOT NULL,
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sentinel_telemetry (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
ts TIMESTAMPTZ NOT NULL,
|
|
model TEXT NOT NULL,
|
|
tier TEXT NOT NULL,
|
|
input_tokens INT NOT NULL,
|
|
output_tokens INT NOT NULL,
|
|
cost_usd DOUBLE PRECISION NOT NULL,
|
|
duration_ms INT NOT NULL,
|
|
action TEXT NOT NULL,
|
|
escalated BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_sentinel_telemetry_ts ON sentinel_telemetry(ts DESC);
|