173 lines
6.9 KiB
SQL
173 lines
6.9 KiB
SQL
-- Integration Test Schema Setup
|
||
-- ================================
|
||
-- 為 CI 環境的臨時 PostgreSQL 建立測試所需的 schema
|
||
-- 使用: psql $TEST_DATABASE_URL -f setup_test_schema.sql
|
||
-- 2026-04-10 Claude Sonnet 4.6 Asia/Taipei
|
||
|
||
CREATE EXTENSION IF NOT EXISTS vector;
|
||
|
||
DO $$ BEGIN
|
||
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'incidentstatus') THEN
|
||
CREATE TYPE incidentstatus AS ENUM ('INVESTIGATING','MITIGATING','RESOLVED','CLOSED','ESCALATED');
|
||
END IF;
|
||
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'severity') THEN
|
||
CREATE TYPE severity AS ENUM ('P0','P1','P2','P3');
|
||
END IF;
|
||
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'approvalstatus') THEN
|
||
CREATE TYPE approvalstatus AS ENUM ('PENDING','APPROVED','REJECTED','EXPIRED','EXECUTION_SUCCESS','EXECUTION_FAILED');
|
||
END IF;
|
||
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'risklevel') THEN
|
||
CREATE TYPE risklevel AS ENUM ('LOW','MEDIUM','HIGH','CRITICAL');
|
||
END IF;
|
||
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'entrysource') THEN
|
||
CREATE TYPE entrysource AS ENUM ('AI_EXTRACTED','HUMAN');
|
||
END IF;
|
||
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'entrystatus') THEN
|
||
CREATE TYPE entrystatus AS ENUM ('DRAFT','REVIEW','APPROVED','ARCHIVED','published');
|
||
END IF;
|
||
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'entrytype') THEN
|
||
CREATE TYPE entrytype AS ENUM ('INCIDENT_CASE','RUNBOOK','BEST_PRACTICE','POSTMORTEM','auto_runbook','anti_pattern','AUTO_RUNBOOK','ANTI_PATTERN');
|
||
END IF;
|
||
END $$;
|
||
|
||
CREATE TABLE IF NOT EXISTS incidents (
|
||
incident_id VARCHAR(30) PRIMARY KEY,
|
||
status incidentstatus NOT NULL DEFAULT 'INVESTIGATING',
|
||
severity severity NOT NULL DEFAULT 'P2',
|
||
signals JSON DEFAULT '[]',
|
||
affected_services JSON DEFAULT '[]',
|
||
decision_chain JSON DEFAULT '[]',
|
||
proposal_ids JSON DEFAULT '[]',
|
||
outcome JSON DEFAULT '{}',
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
resolved_at TIMESTAMPTZ,
|
||
closed_at TIMESTAMPTZ,
|
||
ttl_days INTEGER DEFAULT 30,
|
||
vectorized BOOLEAN DEFAULT false
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS approval_records (
|
||
id VARCHAR(36) PRIMARY KEY,
|
||
action VARCHAR(500) NOT NULL,
|
||
description TEXT NOT NULL,
|
||
status approvalstatus NOT NULL DEFAULT 'PENDING',
|
||
risk_level risklevel NOT NULL,
|
||
required_signatures INTEGER DEFAULT 1,
|
||
current_signatures INTEGER DEFAULT 0,
|
||
signatures JSON DEFAULT '[]',
|
||
blast_radius JSON DEFAULT '{}',
|
||
dry_run_checks JSON DEFAULT '[]',
|
||
requested_by VARCHAR,
|
||
rejection_reason TEXT,
|
||
extra_metadata JSON DEFAULT '{}',
|
||
fingerprint VARCHAR,
|
||
hit_count INTEGER DEFAULT 1,
|
||
last_seen_at TIMESTAMPTZ,
|
||
approval_level VARCHAR DEFAULT 'standard',
|
||
approval_votes JSONB,
|
||
required_votes INTEGER DEFAULT 1,
|
||
incident_id VARCHAR,
|
||
telegram_message_id INTEGER,
|
||
telegram_chat_id BIGINT, -- ADR-093 2026-04-25: 支援群組負數 ID
|
||
matched_playbook_id VARCHAR(36),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
expires_at TIMESTAMPTZ,
|
||
resolved_at TIMESTAMPTZ
|
||
);
|
||
|
||
-- 2026-04-27 P2.1 DecisionFusion 欄位（對齊 p2_decision_fusion_columns.sql 已上 production）
|
||
-- IF NOT EXISTS 形式 idempotent，重跑安全
|
||
ALTER TABLE approval_records
|
||
ADD COLUMN IF NOT EXISTS composite_score REAL,
|
||
ADD COLUMN IF NOT EXISTS complexity_tier VARCHAR(16),
|
||
ADD COLUMN IF NOT EXISTS decision_fusion_details JSONB;
|
||
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'chk_complexity_tier') THEN
|
||
ALTER TABLE approval_records
|
||
ADD CONSTRAINT chk_complexity_tier CHECK (
|
||
complexity_tier IS NULL
|
||
OR complexity_tier IN ('low','medium','high','critical')
|
||
);
|
||
END IF;
|
||
END $$;
|
||
|
||
-- 2026-04-27 P3.2.2 — AI Provider 版本歷史表（對齊 p3_2_provider_version_history.sql）
|
||
CREATE TABLE IF NOT EXISTS ai_provider_version_history (
|
||
id SERIAL PRIMARY KEY,
|
||
provider VARCHAR(40) NOT NULL,
|
||
model VARCHAR(100) NOT NULL,
|
||
version VARCHAR(200),
|
||
digest VARCHAR(80),
|
||
captured_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
||
prev_version VARCHAR(200),
|
||
changed BOOLEAN NOT NULL DEFAULT FALSE
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS knowledge_entries (
|
||
id VARCHAR(36) PRIMARY KEY,
|
||
project_id VARCHAR(64) NOT NULL DEFAULT 'awoooi',
|
||
title VARCHAR NOT NULL,
|
||
content TEXT,
|
||
entry_type entrytype NOT NULL,
|
||
category VARCHAR,
|
||
tags JSON DEFAULT '[]',
|
||
source entrysource NOT NULL DEFAULT 'HUMAN',
|
||
status entrystatus NOT NULL DEFAULT 'DRAFT',
|
||
related_incident_id VARCHAR,
|
||
related_playbook_id VARCHAR,
|
||
symptoms_hash VARCHAR,
|
||
-- 2026-04-29 ogt + Claude Opus 4.7: M4 KMWriter 反查鏈 + 冪等補欄
|
||
-- 解 CD #1115-1118 全 failure：column "related_approval_id" does not exist
|
||
-- 對應 commit c22e5f33 KMWriter ORM 加的欄位
|
||
related_approval_id VARCHAR(64),
|
||
path_type VARCHAR(50),
|
||
view_count INTEGER DEFAULT 0,
|
||
created_by VARCHAR,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
-- 2026-05-05 AwoooP Phase 2 tenant isolation: align integration schema with KnowledgeEntryRecord.project_id.
|
||
ALTER TABLE knowledge_entries
|
||
ADD COLUMN IF NOT EXISTS project_id VARCHAR(64) NOT NULL DEFAULT 'awoooi';
|
||
CREATE INDEX IF NOT EXISTS ix_knowledge_entries_project_id
|
||
ON knowledge_entries(project_id);
|
||
-- M3 冪等 unique index (incident_id + path_type)
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uix_knowledge_incident_path
|
||
ON knowledge_entries(related_incident_id, path_type)
|
||
WHERE related_incident_id IS NOT NULL AND path_type IS NOT NULL;
|
||
-- M4 反查鏈 partial index
|
||
CREATE INDEX IF NOT EXISTS ix_knowledge_related_approval
|
||
ON knowledge_entries(related_approval_id)
|
||
WHERE related_approval_id IS NOT NULL;
|
||
|
||
CREATE TABLE IF NOT EXISTS rag_chunks (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
source TEXT NOT NULL,
|
||
source_id TEXT NOT NULL,
|
||
title TEXT,
|
||
chunk_text TEXT NOT NULL,
|
||
embedding vector(768),
|
||
metadata JSONB DEFAULT '{}',
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
-- adr091: aider_events schema (2026-04-22 @ Asia/Taipei, 補入 integration test schema)
|
||
CREATE TABLE IF NOT EXISTS aider_events (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
session_id TEXT NOT NULL,
|
||
ts TIMESTAMPTZ NOT NULL,
|
||
type TEXT NOT NULL,
|
||
host TEXT DEFAULT 'ogt-mac',
|
||
payload JSONB NOT NULL,
|
||
incident_id TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
||
);
|
||
CREATE INDEX IF NOT EXISTS aider_events_session_idx ON aider_events(session_id);
|
||
CREATE INDEX IF NOT EXISTS aider_events_type_ts_idx ON aider_events(type, ts DESC);
|
||
CREATE INDEX IF NOT EXISTS aider_events_ts_idx ON aider_events(ts DESC);
|
||
CREATE INDEX IF NOT EXISTS aider_events_payload_gin ON aider_events USING GIN (payload);
|