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.contract_snapshots (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    contract_name text NOT NULL,
    contract_type text NOT NULL,
    version_label text,
    content_hash text,
    source_path text,
    snapshot jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
);

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_contract_snapshots_name ON creation_knowledge.contract_snapshots(contract_name, contract_type);

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'
    ]
    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();