deploy/postgres_schema.sql - master - azathd/mutiagent

deploy/postgres_schema.sql
96 строк3 KB
Your Name
init
15 май 2026, 19:11
15 май 2026, 19:11f0c1163
-- Idempotent Postgres schema (fallback when Alembic cannot run).
-- Safe to re-run. Stamps alembic at 20240516_full.

CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255),
    marzban_uuid VARCHAR(64),
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS user_profiles (
    id UUID PRIMARY KEY,
    user_id UUID REFERENCES users (id),
    baseline_bytes_hour BIGINT DEFAULT 0,
    last_seen_at TIMESTAMPTZ,
    meta JSONB DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS user_limits (
    id UUID PRIMARY KEY,
    user_id UUID UNIQUE REFERENCES users (id),
    daily_bytes BIGINT,
    monthly_bytes BIGINT,
    allowed_countries JSONB DEFAULT '[]'
);

CREATE TABLE IF NOT EXISTS incidents (
    id UUID PRIMARY KEY,
    title VARCHAR(512) NOT NULL,
    status VARCHAR(64) DEFAULT 'new',
    severity VARCHAR(32) NOT NULL,
    summary TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS actions_queue (
    id UUID PRIMARY KEY,
    incident_id UUID REFERENCES incidents (id),
    action_type VARCHAR(64) NOT NULL,
    target VARCHAR(255) NOT NULL,
    payload JSONB DEFAULT '{}',
    status VARCHAR(32) DEFAULT 'proposed',
    severity VARCHAR(32) DEFAULT 'low',
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS hitl_requests (
    id UUID PRIMARY KEY,
    action_id UUID REFERENCES actions_queue (id),
    status VARCHAR(32) DEFAULT 'pending',
    requested_by VARCHAR(128) DEFAULT 'orchestrator',
    resolved_by VARCHAR(128),
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS audit_log (
    id UUID PRIMARY KEY,
    action_id VARCHAR(64) NOT NULL UNIQUE,
    action_type VARCHAR(64) NOT NULL,
    actor VARCHAR(128) NOT NULL,
    target VARCHAR(255) NOT NULL,
    payload JSONB DEFAULT '{}',
    result JSONB DEFAULT '{}',
    prev_hash VARCHAR(64) DEFAULT '',
    entry_hash VARCHAR(64) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS reports (
    id UUID PRIMARY KEY,
    period VARCHAR(32) NOT NULL,
    object_key VARCHAR(512) NOT NULL,
    formats JSONB DEFAULT '[]',
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE OR REPLACE FUNCTION audit_log_deny_mutation() RETURNS trigger AS $$
BEGIN
    RAISE EXCEPTION 'audit_log is append-only';
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS audit_log_no_update ON audit_log;
CREATE TRIGGER audit_log_no_update
    BEFORE UPDATE OR DELETE ON audit_log
    FOR EACH ROW EXECUTE PROCEDURE audit_log_deny_mutation();

CREATE TABLE IF NOT EXISTS alembic_version (
    version_num VARCHAR(32) NOT NULL,
    CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
);

INSERT INTO alembic_version (version_num) VALUES ('20240516_full')
ON CONFLICT (version_num) DO NOTHING;