Create-knowledge-find-decode/db/migrations/001_creation_knowledge_schema.sql

-- Creation Knowledge formal cloud schema.
-- Applied to database: creation_knowledge_prod
-- Schema: creation_knowledge

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE SCHEMA IF NOT EXISTS creation_knowledge;

CREATE TABLE IF NOT EXISTS creation_knowledge.schema_migrations (
    version text PRIMARY KEY,
    description text NOT NULL,
    applied_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION creation_knowledge.touch_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$;

CREATE TABLE IF NOT EXISTS creation_knowledge.query_batches (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name text NOT NULL,
    source_type text NOT NULL DEFAULT 'manual',
    generation_method text,
    target_platforms text[] NOT NULL DEFAULT ARRAY[]::text[],
    status text NOT NULL DEFAULT 'draft',
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_message text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.queries (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    batch_id uuid REFERENCES creation_knowledge.query_batches(id) ON DELETE CASCADE,
    query_text text NOT NULL,
    axes jsonb NOT NULL DEFAULT '{}'::jsonb,
    keep boolean,
    filter_reason text,
    status text NOT NULL DEFAULT 'draft',
    sort_order integer NOT NULL DEFAULT 0,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_message text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.acquisition_runs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    batch_id uuid REFERENCES creation_knowledge.query_batches(id),
    run_key text UNIQUE,
    status text NOT NULL DEFAULT 'pending',
    note text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_message text,
    started_at timestamptz,
    finished_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.acquisition_jobs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    run_id uuid NOT NULL REFERENCES creation_knowledge.acquisition_runs(id) ON DELETE CASCADE,
    query_id uuid REFERENCES creation_knowledge.queries(id),
    platform text NOT NULL,
    status text NOT NULL DEFAULT 'pending',
    search_limit integer,
    display_limit integer,
    attempt_count integer NOT NULL DEFAULT 0,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_message text,
    started_at timestamptz,
    finished_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (run_id, query_id, platform)
);

CREATE TABLE IF NOT EXISTS creation_knowledge.candidate_items (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    job_id uuid REFERENCES creation_knowledge.acquisition_jobs(id) ON DELETE SET NULL,
    query_id uuid REFERENCES creation_knowledge.queries(id) ON DELETE SET NULL,
    platform text NOT NULL,
    platform_item_id text,
    canonical_url text,
    content_type text,
    title text,
    author_name text,
    published_at timestamptz,
    raw_summary text,
    status text NOT NULL DEFAULT 'candidate',
    source_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_message text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.media_assets (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    item_id uuid NOT NULL REFERENCES creation_knowledge.candidate_items(id) ON DELETE CASCADE,
    media_type text NOT NULL,
    source_url text,
    oss_url text,
    cdn_url text,
    position integer NOT NULL DEFAULT 0,
    status text NOT NULL DEFAULT 'pending',
    source_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_message text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.item_classifications (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    item_id uuid NOT NULL REFERENCES creation_knowledge.candidate_items(id) ON DELETE CASCADE,
    is_creation_knowledge boolean,
    label text,
    confidence numeric(5,4),
    reason text,
    model_name text,
    prompt_version text,
    result_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    status text NOT NULL DEFAULT 'pending',
    error_message text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.decode_jobs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    item_id uuid NOT NULL REFERENCES creation_knowledge.candidate_items(id) ON DELETE CASCADE,
    status text NOT NULL DEFAULT 'pending',
    attempt_count integer NOT NULL DEFAULT 0,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_message text,
    started_at timestamptz,
    finished_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.decode_results (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    decode_job_id uuid REFERENCES creation_knowledge.decode_jobs(id) ON DELETE SET NULL,
    item_id uuid NOT NULL REFERENCES creation_knowledge.candidate_items(id) ON DELETE CASCADE,
    read_result jsonb NOT NULL DEFAULT '{}'::jsonb,
    gate_result jsonb NOT NULL DEFAULT '{}'::jsonb,
    framing_result jsonb NOT NULL DEFAULT '{}'::jsonb,
    status text NOT NULL DEFAULT 'draft',
    error_message text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.knowledge_particles (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    decode_result_id uuid REFERENCES creation_knowledge.decode_results(id) ON DELETE CASCADE,
    item_id uuid REFERENCES creation_knowledge.candidate_items(id) ON DELETE CASCADE,
    parent_particle_id uuid REFERENCES creation_knowledge.knowledge_particles(id) ON DELETE SET NULL,
    particle_type text NOT NULL CHECK (particle_type IN ('what', 'how', 'why')),
    title text NOT NULL,
    business_stage text,
    creation_stage text,
    content jsonb NOT NULL DEFAULT '{}'::jsonb,
    sort_order integer NOT NULL DEFAULT 0,
    status text NOT NULL DEFAULT 'draft',
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_message text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.scope_results (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    particle_id uuid REFERENCES creation_knowledge.knowledge_particles(id) ON DELETE CASCADE,
    item_id uuid REFERENCES creation_knowledge.candidate_items(id) ON DELETE CASCADE,
    scope_type text NOT NULL,
    scope_value text NOT NULL,
    is_reused boolean,
    matched_scope_id text,
    confidence numeric(5,4),
    evidence jsonb NOT NULL DEFAULT '{}'::jsonb,
    status text NOT NULL DEFAULT 'draft',
    error_message text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.payload_drafts (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    particle_id uuid REFERENCES creation_knowledge.knowledge_particles(id) ON DELETE CASCADE,
    item_id uuid REFERENCES creation_knowledge.candidate_items(id) ON DELETE CASCADE,
    payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    review_status text NOT NULL DEFAULT 'pending',
    ingest_ready boolean NOT NULL DEFAULT false,
    status text NOT NULL DEFAULT 'draft',
    error_message text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.ingest_records (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    payload_draft_id uuid REFERENCES creation_knowledge.payload_drafts(id) ON DELETE SET NULL,
    target_system text NOT NULL,
    target_id text,
    status text NOT NULL DEFAULT 'pending',
    attempt_count integer NOT NULL DEFAULT 0,
    response_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_message text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.pipeline_runs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    run_key text UNIQUE,
    batch_id uuid REFERENCES creation_knowledge.query_batches(id) ON DELETE SET NULL,
    status text NOT NULL DEFAULT 'pending',
    current_stage text,
    config jsonb NOT NULL DEFAULT '{}'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_message text,
    started_at timestamptz,
    finished_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.pipeline_jobs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    pipeline_run_id uuid NOT NULL REFERENCES creation_knowledge.pipeline_runs(id) ON DELETE CASCADE,
    stage text NOT NULL,
    status text NOT NULL DEFAULT 'pending',
    target_table text,
    target_id uuid,
    attempt_count integer NOT NULL DEFAULT 0,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_message text,
    started_at timestamptz,
    finished_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.pipeline_run_events (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    pipeline_run_id uuid REFERENCES creation_knowledge.pipeline_runs(id) ON DELETE CASCADE,
    pipeline_job_id uuid REFERENCES creation_knowledge.pipeline_jobs(id) ON DELETE SET NULL,
    stage text NOT NULL,
    event_type text NOT NULL,
    status text,
    severity text NOT NULL DEFAULT 'info',
    target_table text,
    target_id uuid,
    acquisition_run_id uuid REFERENCES creation_knowledge.acquisition_runs(id) ON DELETE SET NULL,
    acquisition_job_id uuid REFERENCES creation_knowledge.acquisition_jobs(id) ON DELETE SET NULL,
    query_id uuid REFERENCES creation_knowledge.queries(id) ON DELETE SET NULL,
    item_id uuid REFERENCES creation_knowledge.candidate_items(id) ON DELETE SET NULL,
    decode_job_id uuid REFERENCES creation_knowledge.decode_jobs(id) ON DELETE SET NULL,
    decode_result_id uuid REFERENCES creation_knowledge.decode_results(id) ON DELETE SET NULL,
    payload_draft_id uuid REFERENCES creation_knowledge.payload_drafts(id) ON DELETE SET NULL,
    ingest_record_id uuid REFERENCES creation_knowledge.ingest_records(id) ON DELETE SET NULL,
    platform text,
    attempt_index integer,
    message text,
    payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_message text,
    duration_ms integer,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.candidate_item_hits (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    pipeline_run_id uuid REFERENCES creation_knowledge.pipeline_runs(id) ON DELETE CASCADE,
    acquisition_run_id uuid REFERENCES creation_knowledge.acquisition_runs(id) ON DELETE CASCADE,
    acquisition_job_id uuid REFERENCES creation_knowledge.acquisition_jobs(id) ON DELETE SET NULL,
    query_id uuid REFERENCES creation_knowledge.queries(id) ON DELETE SET NULL,
    item_id uuid REFERENCES creation_knowledge.candidate_items(id) ON DELETE SET NULL,
    platform text NOT NULL,
    unique_key text,
    platform_item_id text,
    search_provider text,
    detail_provider text,
    attempt_index integer,
    page_index integer,
    page_rank integer,
    candidate_rank integer,
    source_cursor text,
    is_duplicate_hit boolean NOT NULL DEFAULT false,
    raw_candidate jsonb NOT NULL DEFAULT '{}'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.llm_call_traces (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    pipeline_run_id uuid REFERENCES creation_knowledge.pipeline_runs(id) ON DELETE CASCADE,
    pipeline_job_id uuid REFERENCES creation_knowledge.pipeline_jobs(id) ON DELETE SET NULL,
    run_event_id uuid REFERENCES creation_knowledge.pipeline_run_events(id) ON DELETE SET NULL,
    stage text NOT NULL,
    substage text,
    provider text,
    model_name text,
    endpoint text,
    prompt_name text,
    prompt_hash text,
    trace_context jsonb NOT NULL DEFAULT '{}'::jsonb,
    request_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    response_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    parsed_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    status text NOT NULL DEFAULT 'pending',
    error_message text,
    latency_ms integer,
    attempt_index integer,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS creation_knowledge.contract_artifacts (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    contract_name text NOT NULL,
    contract_type text NOT NULL,
    version_label text,
    content_hash text NOT NULL,
    source_path text,
    snapshot jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (contract_name, contract_type, source_path, content_hash)
);

CREATE TABLE IF NOT EXISTS creation_knowledge.run_contract_artifacts (
    pipeline_run_id uuid NOT NULL REFERENCES creation_knowledge.pipeline_runs(id) ON DELETE CASCADE,
    contract_artifact_id uuid NOT NULL REFERENCES creation_knowledge.contract_artifacts(id) ON DELETE CASCADE,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (pipeline_run_id, contract_artifact_id)
);

CREATE INDEX IF NOT EXISTS idx_queries_batch ON creation_knowledge.queries(batch_id);
CREATE INDEX IF NOT EXISTS idx_acquisition_runs_batch ON creation_knowledge.acquisition_runs(batch_id);
CREATE INDEX IF NOT EXISTS idx_acquisition_jobs_run ON creation_knowledge.acquisition_jobs(run_id);
CREATE INDEX IF NOT EXISTS idx_candidate_items_job ON creation_knowledge.candidate_items(job_id);
CREATE INDEX IF NOT EXISTS idx_candidate_items_platform_item ON creation_knowledge.candidate_items(platform, platform_item_id);
CREATE INDEX IF NOT EXISTS idx_media_assets_item ON creation_knowledge.media_assets(item_id);
CREATE INDEX IF NOT EXISTS idx_item_classifications_item ON creation_knowledge.item_classifications(item_id);
CREATE INDEX IF NOT EXISTS idx_decode_jobs_item ON creation_knowledge.decode_jobs(item_id);
CREATE INDEX IF NOT EXISTS idx_decode_results_item ON creation_knowledge.decode_results(item_id);
CREATE INDEX IF NOT EXISTS idx_knowledge_particles_item ON creation_knowledge.knowledge_particles(item_id);
CREATE INDEX IF NOT EXISTS idx_scope_results_particle ON creation_knowledge.scope_results(particle_id);
CREATE INDEX IF NOT EXISTS idx_payload_drafts_particle ON creation_knowledge.payload_drafts(particle_id);
CREATE INDEX IF NOT EXISTS idx_ingest_records_payload ON creation_knowledge.ingest_records(payload_draft_id);
CREATE INDEX IF NOT EXISTS idx_pipeline_runs_batch ON creation_knowledge.pipeline_runs(batch_id);
CREATE INDEX IF NOT EXISTS idx_pipeline_runs_status ON creation_knowledge.pipeline_runs(status, current_stage);
CREATE INDEX IF NOT EXISTS idx_pipeline_jobs_run ON creation_knowledge.pipeline_jobs(pipeline_run_id);
CREATE INDEX IF NOT EXISTS idx_pipeline_jobs_stage_status ON creation_knowledge.pipeline_jobs(stage, status);
CREATE INDEX IF NOT EXISTS idx_pipeline_run_events_run ON creation_knowledge.pipeline_run_events(pipeline_run_id, created_at);
CREATE INDEX IF NOT EXISTS idx_pipeline_run_events_acq_run ON creation_knowledge.pipeline_run_events(acquisition_run_id);
CREATE INDEX IF NOT EXISTS idx_pipeline_run_events_query ON creation_knowledge.pipeline_run_events(query_id);
CREATE INDEX IF NOT EXISTS idx_pipeline_run_events_item ON creation_knowledge.pipeline_run_events(item_id);
CREATE INDEX IF NOT EXISTS idx_candidate_item_hits_pipeline_run ON creation_knowledge.candidate_item_hits(pipeline_run_id);
CREATE INDEX IF NOT EXISTS idx_candidate_item_hits_acq_run ON creation_knowledge.candidate_item_hits(acquisition_run_id);
CREATE INDEX IF NOT EXISTS idx_candidate_item_hits_query ON creation_knowledge.candidate_item_hits(query_id);
CREATE INDEX IF NOT EXISTS idx_candidate_item_hits_item ON creation_knowledge.candidate_item_hits(item_id);
CREATE INDEX IF NOT EXISTS idx_candidate_item_hits_unique_key ON creation_knowledge.candidate_item_hits(unique_key);
CREATE INDEX IF NOT EXISTS idx_llm_call_traces_run_stage ON creation_knowledge.llm_call_traces(pipeline_run_id, stage, substage);
CREATE INDEX IF NOT EXISTS idx_llm_call_traces_item ON creation_knowledge.llm_call_traces((trace_context ->> 'item_id'));
CREATE INDEX IF NOT EXISTS idx_llm_call_traces_status ON creation_knowledge.llm_call_traces(status);
CREATE INDEX IF NOT EXISTS idx_contract_artifacts_name_hash ON creation_knowledge.contract_artifacts(contract_name, contract_type, source_path, content_hash);
CREATE INDEX IF NOT EXISTS idx_run_contract_artifacts_artifact ON creation_knowledge.run_contract_artifacts(contract_artifact_id);

DO $$
DECLARE
    table_name text;
BEGIN
    FOREACH table_name IN ARRAY ARRAY[
        'query_batches',
        'queries',
        'acquisition_runs',
        'acquisition_jobs',
        'candidate_items',
        'media_assets',
        'item_classifications',
        'decode_jobs',
        'decode_results',
        'knowledge_particles',
        'scope_results',
        'payload_drafts',
        'ingest_records',
        'pipeline_runs',
        'pipeline_jobs',
        'contract_artifacts'
    ]
    LOOP
        EXECUTE format('DROP TRIGGER IF EXISTS trg_%I_touch_updated_at ON creation_knowledge.%I', table_name, table_name);
        EXECUTE format(
            'CREATE TRIGGER trg_%I_touch_updated_at BEFORE UPDATE ON creation_knowledge.%I FOR EACH ROW EXECUTE FUNCTION creation_knowledge.touch_updated_at()',
            table_name,
            table_name
        );
    END LOOP;
END;
$$;

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'ck_app') THEN
        GRANT CONNECT ON DATABASE creation_knowledge_prod TO ck_app;
        GRANT USAGE ON SCHEMA creation_knowledge TO ck_app;
        GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA creation_knowledge TO ck_app;
        GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA creation_knowledge TO ck_app;
        ALTER DEFAULT PRIVILEGES IN SCHEMA creation_knowledge
            GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ck_app;
        ALTER DEFAULT PRIVILEGES IN SCHEMA creation_knowledge
            GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO ck_app;
        ALTER ROLE ck_app IN DATABASE creation_knowledge_prod
            SET search_path = creation_knowledge, public;
    END IF;
END;
$$;

INSERT INTO creation_knowledge.schema_migrations(version, description)
VALUES ('001_creation_knowledge_schema', 'formal creation knowledge cloud schema')
ON CONFLICT (version) DO UPDATE
SET description = EXCLUDED.description,
    applied_at = now();