mycelium_schema_empty.sql

-- Portable Mycelium structural bootstrap.
-- Generated mechanically from the verified source dump; contains no graph records.
-- Structural schema is not a declaration of canonical semantic ontology.

--
-- PostgreSQL database dump
--


-- Dumped from database version 17.10 (Debian 17.10-1.pgdg13+1)
-- Dumped by pg_dump version 17.10 (Debian 17.10-1.pgdg13+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: pg_trgm; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;


--
-- Name: EXTENSION pg_trgm; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';


--
-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;


--
-- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';


--
-- Name: ref_type; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE public.ref_type AS ENUM (
    'node',
    'edge',
    'evidence'
);



--
-- Name: authorization_admissions_immutable(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.authorization_admissions_immutable() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        RAISE EXCEPTION 'authorization_admissions is append-only';
    END IF;

    IF OLD.consumed_at IS NOT NULL THEN
        RAISE EXCEPTION 'authorization % is already spent', OLD.authorization_id;
    END IF;

    IF NEW.consumed_at IS NULL THEN
        RAISE EXCEPTION 'the only permitted update is a single-use consume';
    END IF;

    IF NEW.authorization_id  IS DISTINCT FROM OLD.authorization_id
    OR NEW.envelope_sha256   IS DISTINCT FROM OLD.envelope_sha256
    OR NEW.nonce             IS DISTINCT FROM OLD.nonce
    OR NEW.task_id           IS DISTINCT FROM OLD.task_id
    OR NEW.spec_sha256       IS DISTINCT FROM OLD.spec_sha256
    OR NEW.gate_decision_id  IS DISTINCT FROM OLD.gate_decision_id
    OR NEW.gate_decision_sha256 IS DISTINCT FROM OLD.gate_decision_sha256
    OR NEW.addressed_agent   IS DISTINCT FROM OLD.addressed_agent
    OR NEW.lane              IS DISTINCT FROM OLD.lane
    OR NEW.branch            IS DISTINCT FROM OLD.branch
    OR NEW.base_sha          IS DISTINCT FROM OLD.base_sha
    OR NEW.issued_at         IS DISTINCT FROM OLD.issued_at
    OR NEW.expires_at        IS DISTINCT FROM OLD.expires_at
    OR NEW.envelope          IS DISTINCT FROM OLD.envelope
    OR NEW.admitted_by       IS DISTINCT FROM OLD.admitted_by
    OR NEW.admitted_at       IS DISTINCT FROM OLD.admitted_at THEN
        RAISE EXCEPTION 'an admitted envelope cannot be rewritten';
    END IF;

    RETURN NEW;
END;
$$;



--
-- Name: authorization_admissions_no_delete(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.authorization_admissions_no_delete() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    RAISE EXCEPTION 'authorization_admissions is append-only';
END;
$$;



--
-- Name: authorization_attempts_append_only(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.authorization_attempts_append_only() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    RAISE EXCEPTION 'authorization_attempts is append-only';
END;
$$;



--
-- Name: authorization_gate_decisions_append_only(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.authorization_gate_decisions_append_only() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    RAISE EXCEPTION 'authorization_gate_decisions is append-only; a decision '
                    'is superseded by a new row, never edited';
END;
$$;



--
-- Name: fn_guard_state_vector(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.fn_guard_state_vector() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    -- Check if a justifying event exists within the last 5 seconds
    IF NOT EXISTS (
        SELECT 1 FROM events
        WHERE node_id = NEW.id
        AND timestamp >= (CURRENT_TIMESTAMP - INTERVAL '5 seconds')
    ) THEN
        RAISE EXCEPTION 'Forbidden: state_vector update requires a preceding event log entry.';
    END IF;
    RETURN NEW;
END;
$$;



--
-- Name: fn_log_new_edge(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.fn_log_new_edge() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO events (edge_id, event_type, delta, origin_agent, scope)
    VALUES (NEW.id, 'PROPOSED',
            jsonb_build_object('source', NEW.source_id, 'target', NEW.target_id, 'relation', NEW.relation_type),
            'MYCELIUM_GROWTH', NEW.scope);
    RETURN NEW;
END;
$$;



--
-- Name: fn_log_node_change(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.fn_log_node_change() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    -- Only log if it's NOT a state_vector update (to avoid double logs)
    IF (OLD.state_vector = NEW.state_vector) THEN
        INSERT INTO events (node_id, event_type, delta, origin_agent, scope)
        VALUES (NEW.id, 'OVERRIDE',
                jsonb_build_object('old_status', OLD.status, 'new_status', NEW.status),
                'RAILWAY_SYNC', NEW.scope);
    END IF;
    RETURN NEW;
END;
$$;



--
-- Name: forbid_update_delete(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.forbid_update_delete() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  RAISE EXCEPTION 'Immutable table: updates/deletes forbidden on %', TG_TABLE_NAME;
END;
$$;



--
-- Name: reconcile_node(text); Type: PROCEDURE; Schema: public; Owner: postgres
--

CREATE PROCEDURE public.reconcile_node(IN target_id text)
    LANGUAGE plpgsql
    AS $$
DECLARE
    latest_vector JSONB;
BEGIN
    -- 1. Grab vector from latest event
    SELECT (delta->>'new_vector')::jsonb INTO latest_vector
    FROM events
    WHERE node_id = target_id
    ORDER BY timestamp DESC LIMIT 1;

    -- 2. Apply to node (this will pass the guard because the event exists)
    IF latest_vector IS NOT NULL THEN
        UPDATE nodes SET state_vector = latest_vector WHERE id = target_id;
    ELSE
        RAISE NOTICE 'No valid vector found in event history for node %', target_id;
    END IF;
END;
$$;



--
-- Name: reject_nursemaid_attempt_event_mutation(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.reject_nursemaid_attempt_event_mutation() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    RAISE EXCEPTION 'nursemaid_attempt_events is append-only';
END;
$$;



--
-- Name: reject_nursemaid_control_event_mutation(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.reject_nursemaid_control_event_mutation() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    RAISE EXCEPTION 'nursemaid_control_events is append-only';
END;
$$;



--
-- Name: reject_read_access_event_mutation(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.reject_read_access_event_mutation() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    RAISE EXCEPTION 'read_access_events is append-only';
END;
$$;



--
-- Name: reject_search_access_event_mutation(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.reject_search_access_event_mutation() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    RAISE EXCEPTION 'search_access_events is append-only';
END;
$$;



--
-- Name: update_breadcrumbs_search(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_breadcrumbs_search() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  NEW.search_vector := to_tsvector('english',
    COALESCE(NEW.id, '') || ' ' ||
    COALESCE(NEW.trail_id, '') || ' ' ||
    COALESCE(NEW.note, '') || ' ' ||
    COALESCE(NEW.label, '') || ' ' ||
    COALESCE(NEW.facet_ref, '') || ' ' ||
    COALESCE(NEW.agent_id, '')
  );
  RETURN NEW;
END;
$$;



--
-- Name: update_edges_search(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_edges_search() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  NEW.search_vector := to_tsvector('english',
    COALESCE(NEW.source_id, '') || ' ' ||
    COALESCE(NEW.target_id, '') || ' ' ||
    COALESCE(NEW.source_facet, '') || ' ' ||
    COALESCE(NEW.target_facet, '') || ' ' ||
    COALESCE(NEW.relation_type, '') || ' ' ||
    COALESCE(NEW.cause, '') || ' ' ||
    COALESCE(NEW.label, '') || ' ' ||
    COALESCE(NEW.facets::text, '') || ' ' ||
    COALESCE(NEW.origin_agent, '')
  );
  RETURN NEW;
END;
$$;



--
-- Name: update_evidence_search(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_evidence_search() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  NEW.search_vector := to_tsvector('english',
    COALESCE(NEW.id, '') || ' ' ||
    COALESCE(NEW.label, '') || ' ' ||
    COALESCE(NEW.type, '') || ' ' ||
    COALESCE(NEW.content, '') || ' ' ||
    COALESCE(NEW.facets::text, '') || ' ' ||
    COALESCE(NEW.origin_agent, '')
  );
  RETURN NEW;
END;
$$;



--
-- Name: update_nodes_search(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_nodes_search() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  NEW.search_vector := to_tsvector('english',
    COALESCE(NEW.id, '') || ' ' ||
    COALESCE(NEW.label, '') || ' ' ||
    COALESCE(NEW.type, '') || ' ' ||
    COALESCE(NEW.content::text, '') || ' ' ||
    COALESCE(NEW.facets::text, '') || ' ' ||
    COALESCE(NEW.origin_agent, '')
  );
  RETURN NEW;
END;
$$;



--
-- Name: update_registry_search(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_registry_search() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  NEW.search_vector := to_tsvector('english',
    COALESCE(NEW.id, '') || ' ' ||
    COALESCE(NEW.category, '') || ' ' ||
    COALESCE(NEW.key, '') || ' ' ||
    COALESCE(NEW.value::text, '') || ' ' ||
    COALESCE(NEW.origin_agent, '')
  );
  RETURN NEW;
END;
$$;



--
-- Name: update_trail_steps_search(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_trail_steps_search() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  NEW.search_vector := to_tsvector('english',
    COALESCE(NEW.trail_id, '') || ' ' ||
    COALESCE(NEW.ref_id, '') || ' ' ||
    COALESCE(NEW.note, '') || ' ' ||
    COALESCE(NEW.step_type, '') || ' ' ||
    COALESCE(NEW.facets::text, '') || ' ' ||
    COALESCE(NEW.origin_agent, '')
  );
  RETURN NEW;
END;
$$;



--
-- Name: update_trails_search(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.update_trails_search() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  NEW.search_vector := to_tsvector('english',
    COALESCE(NEW.id, '') || ' ' ||
    COALESCE(NEW.label, '') || ' ' ||
    COALESCE(NEW.agent_id, '') || ' ' ||
    COALESCE(NEW.facets::text, '')
  );
  RETURN NEW;
END;
$$;



SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: agent_availability; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.agent_availability (
    agent_id text NOT NULL,
    state text NOT NULL,
    lanes jsonb DEFAULT '[]'::jsonb NOT NULL,
    current_job_id text,
    heartbeat_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT agent_availability_state_check CHECK ((state = ANY (ARRAY['online'::text, 'busy'::text, 'offline'::text])))
);



--
-- Name: authorization_admissions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.authorization_admissions (
    authorization_id text NOT NULL,
    envelope_sha256 text NOT NULL,
    nonce text NOT NULL,
    task_id text NOT NULL,
    spec_sha256 text NOT NULL,
    gate_decision_id text NOT NULL,
    gate_decision_sha256 text NOT NULL,
    addressed_agent text NOT NULL,
    lane text NOT NULL,
    branch text NOT NULL,
    base_sha text NOT NULL,
    issued_at double precision NOT NULL,
    expires_at double precision NOT NULL,
    envelope jsonb NOT NULL,
    admitted_by text NOT NULL,
    admitted_at double precision NOT NULL,
    consumed_at double precision,
    consumed_by text,
    consumed_expectation jsonb,
    CONSTRAINT authorization_consumption_is_whole CHECK ((((consumed_at IS NULL) AND (consumed_by IS NULL) AND (consumed_expectation IS NULL)) OR ((consumed_at IS NOT NULL) AND (consumed_by IS NOT NULL) AND (consumed_expectation IS NOT NULL))))
);



--
-- Name: authorization_attempts; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.authorization_attempts (
    id bigint NOT NULL,
    attempt_kind text NOT NULL,
    authorization_id text,
    principal text NOT NULL,
    outcome text NOT NULL,
    refusal_code text,
    detail text,
    at double precision
);



--
-- Name: authorization_attempts_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.authorization_attempts_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



--
-- Name: authorization_attempts_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.authorization_attempts_id_seq OWNED BY public.authorization_attempts.id;


--
-- Name: authorization_gate_decisions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.authorization_gate_decisions (
    decision_id text NOT NULL,
    decision_sha256 text NOT NULL,
    decided_by text NOT NULL,
    status text NOT NULL,
    decided_at double precision NOT NULL,
    task_id text NOT NULL,
    spec_sha256 text NOT NULL,
    requested_action text NOT NULL,
    addressed_agent text NOT NULL,
    lane text NOT NULL,
    branch text NOT NULL,
    base_sha text NOT NULL,
    allowed_paths jsonb NOT NULL,
    risk_tier text NOT NULL,
    max_tokens integer NOT NULL,
    max_seconds integer NOT NULL,
    tests jsonb NOT NULL,
    stop_conditions jsonb NOT NULL,
    depends_on jsonb NOT NULL,
    expires_at double precision NOT NULL
);



--
-- Name: auto_approve_rules; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.auto_approve_rules (
    id integer NOT NULL,
    agent_id text NOT NULL,
    entity_type text NOT NULL,
    scope text DEFAULT 'default'::text,
    enabled boolean DEFAULT true,
    created_at timestamp with time zone DEFAULT now()
);



--
-- Name: auto_approve_rules_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.auto_approve_rules_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



--
-- Name: auto_approve_rules_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.auto_approve_rules_id_seq OWNED BY public.auto_approve_rules.id;


--
-- Name: breadcrumbs; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.breadcrumbs (
    id text DEFAULT ('bc_'::text || substr(md5((random())::text), 1, 12)) NOT NULL,
    trail_id text,
    step_idx integer,
    agent_id text,
    note text,
    created_at timestamp with time zone DEFAULT now(),
    source text,
    facet_ref text,
    search_vector tsvector,
    label text,
    status text DEFAULT 'proposed'::text,
    scope text DEFAULT 'project'::text,
    access_type text,
    intent text,
    entity_type text,
    entity_id text,
    read_event_id text,
    search_event_id text
);



--
-- Name: edges; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.edges (
    id text DEFAULT public.uuid_generate_v4() NOT NULL,
    source_id text NOT NULL,
    target_id text NOT NULL,
    relation_type text NOT NULL,
    natural_type text NOT NULL,
    evidence jsonb,
    status text DEFAULT 'proposed'::text,
    scope text NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    source_facet text,
    target_facet text,
    discovery_time timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    origin_agent text,
    cause text,
    trail_id text,
    evidence_id text,
    created_by text,
    facets jsonb DEFAULT '{}'::jsonb,
    source_type text DEFAULT 'node'::text,
    target_type text DEFAULT 'node'::text,
    search_vector tsvector,
    label text,
    superseded_by text
);



--
-- Name: entity_read_heat; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.entity_read_heat (
    entity_type text NOT NULL,
    entity_id text NOT NULL,
    read_count bigint DEFAULT 0 NOT NULL,
    last_read_at timestamp with time zone,
    CONSTRAINT entity_read_heat_read_count_check CHECK ((read_count >= 0))
);



--
-- Name: events; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.events (
    id integer NOT NULL,
    parent_event_id integer,
    node_id text,
    edge_id text,
    event_type text,
    delta jsonb NOT NULL,
    origin_agent text,
    scope text NOT NULL,
    "timestamp" timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    action text,
    facet_key text,
    subject_time timestamp with time zone,
    CONSTRAINT events_event_type_check CHECK (((event_type = 'FACET_WRITE'::text) OR (event_type = ANY (ARRAY['OBSERVED'::text, 'PROPOSED'::text, 'PROMOTED'::text, 'RETRACTED'::text, 'OVERRIDE'::text, 'REQUEST'::text, 'APPROVED'::text, 'REJECTED'::text, 'SUPERSEDED'::text, 'EDITED'::text, 'ACCESSED'::text]))))
);



--
-- Name: events_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.events_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;



--
-- Name: events_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.events_id_seq OWNED BY public.events.id;


--
-- Name: evidence; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.evidence (
    id text NOT NULL,
    type text NOT NULL,
    content text,
    opacity text DEFAULT 'visible'::text,
    source_chat_id text,
    seq_idx integer,
    created_at timestamp with time zone DEFAULT now(),
    facets jsonb DEFAULT '{}'::jsonb,
    search_vector tsvector,
    label text,
    origin_agent text,
    signature text,
    status text DEFAULT 'proposed'::text,
    scope text DEFAULT 'project'::text,
    instance_time timestamp with time zone,
    discovery_time timestamp with time zone,
    instance_time_null_reason text,
    discovery_time_null_reason text,
    trail_id text
);



--
-- Name: nodes; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.nodes (
    id text NOT NULL,
    type text NOT NULL,
    content text,
    state_vector jsonb NOT NULL,
    observations jsonb,
    derived jsonb,
    status text DEFAULT 'proposed'::text,
    scope text NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    facets jsonb DEFAULT '{}'::jsonb,
    heat integer DEFAULT 0,
    search_vector tsvector,
    label text,
    origin_agent text,
    CONSTRAINT nodes_scope_check CHECK ((scope = ANY (ARRAY['user'::text, 'project'::text, 'thread'::text, 'default'::text])))
);



--
-- Name: nursemaid_attempt_events; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.nursemaid_attempt_events (
    id text NOT NULL,
    job_id text NOT NULL,
    attempt_no integer NOT NULL,
    sequence integer NOT NULL,
    event_type text NOT NULL,
    occurred_at timestamp with time zone,
    recorded_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    payload jsonb DEFAULT '{}'::jsonb NOT NULL,
    CONSTRAINT nursemaid_attempt_events_attempt_no_check CHECK (((attempt_no >= 0) AND (attempt_no <= 3))),
    CONSTRAINT nursemaid_attempt_events_event_type_check CHECK ((event_type = ANY (ARRAY['claimed'::text, 'started'::text, 'lease_renewed'::text, 'checkpointed'::text, 'attempt_failed'::text, 'finalized'::text]))),
    CONSTRAINT nursemaid_attempt_events_sequence_check CHECK ((sequence >= 0))
);



--
-- Name: nursemaid_control; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.nursemaid_control (
    singleton boolean DEFAULT true NOT NULL,
    mode text DEFAULT 'killed'::text NOT NULL,
    enabled_until timestamp with time zone,
    kill_generation bigint DEFAULT 0 NOT NULL,
    max_global_concurrency integer DEFAULT 1 NOT NULL,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_by text DEFAULT 'migration'::text NOT NULL,
    reason text DEFAULT 'fail-closed bootstrap'::text NOT NULL,
    CONSTRAINT nursemaid_control_kill_generation_check CHECK ((kill_generation >= 0)),
    CONSTRAINT nursemaid_control_max_global_concurrency_check CHECK (((max_global_concurrency >= 1) AND (max_global_concurrency <= 16))),
    CONSTRAINT nursemaid_control_mode_check CHECK ((mode = ANY (ARRAY['enabled'::text, 'paused'::text, 'killed'::text]))),
    CONSTRAINT nursemaid_control_singleton_check CHECK (singleton)
);



--
-- Name: nursemaid_control_events; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.nursemaid_control_events (
    id text NOT NULL,
    action text NOT NULL,
    kill_generation bigint NOT NULL,
    occurred_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    actor text NOT NULL,
    reason text NOT NULL,
    enabled_until timestamp with time zone,
    payload jsonb DEFAULT '{}'::jsonb NOT NULL,
    CONSTRAINT nursemaid_control_events_action_check CHECK ((action = ANY (ARRAY['enable'::text, 'pause'::text, 'kill'::text, 'dead_man_expired'::text]))),
    CONSTRAINT nursemaid_control_events_kill_generation_check CHECK ((kill_generation >= 0))
);



--
-- Name: nursemaid_jobs; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.nursemaid_jobs (
    id text NOT NULL,
    schema_version text DEFAULT 'nursemaid-job/v1'::text NOT NULL,
    task_type text NOT NULL,
    task_schema_version text DEFAULT '1'::text NOT NULL,
    requested_by text NOT NULL,
    idempotency_key text NOT NULL,
    request_sha256 text NOT NULL,
    instruction text NOT NULL,
    source_refs jsonb NOT NULL,
    source_snapshot jsonb,
    source_snapshot_sha256 text,
    execution_status text DEFAULT 'queued'::text NOT NULL,
    governance_status text DEFAULT 'none'::text NOT NULL,
    execution_attempts integer DEFAULT 0 NOT NULL,
    claimed_by text,
    lease_expires_at timestamp with time zone,
    provider text,
    model text,
    model_revision text,
    result_payload jsonb,
    result_sha256 text,
    evidence_id text,
    failure_code text,
    failure_detail text,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    claimed_at timestamp with time zone,
    started_at timestamp with time zone,
    completed_at timestamp with time zone,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    first_started_at timestamp with time zone,
    latest_attempt_started_at timestamp with time zone,
    execution_receipt_id text,
    loop_id text,
    loop_sequence integer,
    control_generation bigint,
    provider_call_budget integer DEFAULT 2 NOT NULL,
    output_token_budget integer DEFAULT 8000 NOT NULL,
    input_chars bigint,
    trail_id text,
    CONSTRAINT nursemaid_jobs_execution_attempts_check CHECK (((execution_attempts >= 0) AND (execution_attempts <= 3))),
    CONSTRAINT nursemaid_jobs_execution_status_check CHECK ((execution_status = ANY (ARRAY['queued'::text, 'claimed'::text, 'running'::text, 'result_ready'::text, 'completed'::text, 'failed'::text]))),
    CONSTRAINT nursemaid_jobs_governance_status_check CHECK ((governance_status = ANY (ARRAY['none'::text, 'proposed'::text, 'accepted'::text, 'rejected'::text]))),
    CONSTRAINT nursemaid_jobs_schema_version_check CHECK ((schema_version = 'nursemaid-job/v1'::text)),
    CONSTRAINT nursemaid_jobs_task_schema_version_check CHECK ((task_schema_version = '1'::text)),
    CONSTRAINT nursemaid_jobs_task_type_check CHECK ((task_type = 'inkling.reflect/v1'::text))
);



--
-- Name: nursemaid_loops; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.nursemaid_loops (
    id text NOT NULL,
    requested_by text NOT NULL,
    idempotency_key text NOT NULL,
    status text DEFAULT 'active'::text NOT NULL,
    max_jobs integer NOT NULL,
    max_concurrency integer NOT NULL,
    max_wall_seconds integer NOT NULL,
    max_provider_calls integer NOT NULL,
    max_input_chars bigint NOT NULL,
    max_output_tokens bigint NOT NULL,
    no_progress_limit integer NOT NULL,
    jobs_created integer DEFAULT 0 NOT NULL,
    jobs_completed integer DEFAULT 0 NOT NULL,
    provider_calls_reserved integer DEFAULT 0 NOT NULL,
    input_chars_used bigint DEFAULT 0 NOT NULL,
    output_tokens_reserved bigint DEFAULT 0 NOT NULL,
    last_result_sha256 text,
    repeated_result_count integer DEFAULT 0 NOT NULL,
    termination_reason text,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    started_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    deadline_at timestamp with time zone NOT NULL,
    completed_at timestamp with time zone,
    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT nursemaid_loops_input_chars_used_check CHECK ((input_chars_used >= 0)),
    CONSTRAINT nursemaid_loops_jobs_completed_check CHECK ((jobs_completed >= 0)),
    CONSTRAINT nursemaid_loops_jobs_created_check CHECK ((jobs_created >= 0)),
    CONSTRAINT nursemaid_loops_max_concurrency_check CHECK (((max_concurrency >= 1) AND (max_concurrency <= 8))),
    CONSTRAINT nursemaid_loops_max_input_chars_check CHECK (((max_input_chars >= 1) AND (max_input_chars <= 10000000))),
    CONSTRAINT nursemaid_loops_max_jobs_check CHECK (((max_jobs >= 1) AND (max_jobs <= 100))),
    CONSTRAINT nursemaid_loops_max_output_tokens_check CHECK (((max_output_tokens >= 128) AND (max_output_tokens <= 800000))),
    CONSTRAINT nursemaid_loops_max_provider_calls_check CHECK (((max_provider_calls >= 1) AND (max_provider_calls <= 300))),
    CONSTRAINT nursemaid_loops_max_wall_seconds_check CHECK (((max_wall_seconds >= 60) AND (max_wall_seconds <= 86400))),
    CONSTRAINT nursemaid_loops_no_progress_limit_check CHECK (((no_progress_limit >= 1) AND (no_progress_limit <= 10))),
    CONSTRAINT nursemaid_loops_output_tokens_reserved_check CHECK ((output_tokens_reserved >= 0)),
    CONSTRAINT nursemaid_loops_provider_calls_reserved_check CHECK ((provider_calls_reserved >= 0)),
    CONSTRAINT nursemaid_loops_repeated_result_count_check CHECK ((repeated_result_count >= 0)),
    CONSTRAINT nursemaid_loops_status_check CHECK ((status = ANY (ARRAY['active'::text, 'paused'::text, 'killed'::text, 'completed'::text, 'budget_exhausted'::text, 'runaway'::text, 'failed'::text])))
);



--
-- Name: oauth_authorization_codes; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.oauth_authorization_codes (
    code_hash text NOT NULL,
    client_id text NOT NULL,
    redirect_uri text NOT NULL,
    scopes text[] DEFAULT '{}'::text[] NOT NULL,
    code_challenge text NOT NULL,
    code_challenge_method text DEFAULT 'S256'::text NOT NULL,
    state text,
    created_at timestamp with time zone DEFAULT now(),
    expires_at timestamp with time zone NOT NULL,
    used boolean DEFAULT false
);



--
-- Name: oauth_clients; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.oauth_clients (
    client_id text NOT NULL,
    client_secret_hash text,
    client_name text,
    redirect_uris text[] DEFAULT '{}'::text[] NOT NULL,
    grant_types text[] DEFAULT '{authorization_code}'::text[] NOT NULL,
    response_types text[] DEFAULT '{code}'::text[] NOT NULL,
    token_endpoint_auth_method text DEFAULT 'none'::text,
    created_at timestamp with time zone DEFAULT now()
);



--
-- Name: oauth_tokens; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.oauth_tokens (
    token_hash text NOT NULL,
    token_type text NOT NULL,
    client_id text NOT NULL,
    scopes text[] DEFAULT '{}'::text[] NOT NULL,
    principal_agent_id text DEFAULT 'oauth_user'::text NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    expires_at timestamp with time zone NOT NULL,
    revoked boolean DEFAULT false
);



--
-- Name: open_threads; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.open_threads (
    id text NOT NULL,
    content text NOT NULL,
    related_node_id text,
    tension_type text,
    resolved boolean DEFAULT false,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP
);



--
-- Name: parameter_requests; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.parameter_requests (
    id text DEFAULT public.uuid_generate_v4() NOT NULL,
    parameter text NOT NULL,
    scope text NOT NULL,
    requested_by text NOT NULL,
    current_value numeric,
    requested_value numeric,
    reason text,
    status text DEFAULT 'pending'::text,
    resolved_at timestamp with time zone,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT parameter_requests_status_check CHECK ((status = ANY (ARRAY['pending'::text, 'approved'::text, 'denied'::text])))
);



--
-- Name: proposals; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.proposals (
    id text NOT NULL,
    type text NOT NULL,
    payload jsonb NOT NULL,
    supports jsonb DEFAULT '{}'::jsonb,
    note text,
    status text DEFAULT 'proposed'::text NOT NULL,
    decided_reason text,
    created_by text NOT NULL,
    decided_by text,
    idempotency_key text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    decided_at timestamp with time zone,
    trail_id text
);



--
-- Name: quick_note_intakes; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.quick_note_intakes (
    id text NOT NULL,
    authored_by text NOT NULL,
    source_text text NOT NULL,
    source_sha256 text NOT NULL,
    status text DEFAULT 'captured'::text NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT quick_note_intakes_status_check CHECK ((status = ANY (ARRAY['captured'::text, 'partially_routed'::text, 'routed'::text])))
);



--
-- Name: quick_note_route_policies; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.quick_note_route_policies (
    id text NOT NULL,
    created_by text NOT NULL,
    addressed_to text NOT NULL,
    requested_action text NOT NULL,
    concerns_prefix text NOT NULL,
    risk_tier text NOT NULL,
    max_source_chars integer NOT NULL,
    heartbeat_ttl_seconds integer NOT NULL,
    expires_at timestamp with time zone NOT NULL,
    status text DEFAULT 'active'::text NOT NULL,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT quick_note_route_policies_risk_tier_check CHECK ((risk_tier = 'read_only'::text)),
    CONSTRAINT quick_note_route_policies_status_check CHECK ((status = ANY (ARRAY['active'::text, 'paused'::text, 'expired'::text])))
);



--
-- Name: quick_note_segments; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.quick_note_segments (
    id text NOT NULL,
    note_id text NOT NULL,
    segment_index integer NOT NULL,
    start_offset integer NOT NULL,
    end_offset integer NOT NULL,
    segment_sha256 text NOT NULL,
    route_status text DEFAULT 'unassigned'::text NOT NULL,
    routed_thread_id text,
    assigned_agent text,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT quick_note_segments_route_status_check CHECK ((route_status = ANY (ARRAY['unassigned'::text, 'routed'::text])))
);



--
-- Name: read_access_events; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.read_access_events (
    id text NOT NULL,
    agent_id text NOT NULL,
    access_type text NOT NULL,
    query text NOT NULL,
    intent text NOT NULL,
    entity_type text NOT NULL,
    entity_id text NOT NULL,
    occurred_at timestamp with time zone NOT NULL,
    outcome text NOT NULL,
    CONSTRAINT read_access_events_access_type_check CHECK ((access_type = 'entity_read'::text)),
    CONSTRAINT read_access_events_entity_type_check CHECK ((entity_type = ANY (ARRAY['node'::text, 'evidence'::text, 'trail'::text, 'edge'::text, 'breadcrumb'::text]))),
    CONSTRAINT read_access_events_outcome_check CHECK ((outcome = 'success'::text))
);



--
-- Name: registry; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.registry (
    id text NOT NULL,
    category text,
    key text,
    value jsonb,
    version integer DEFAULT 1,
    created_at timestamp with time zone DEFAULT now(),
    source text,
    active boolean DEFAULT true,
    search_vector tsvector,
    origin_agent text
);



--
-- Name: search_access_events; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.search_access_events (
    id text NOT NULL,
    agent_id text NOT NULL,
    query text NOT NULL,
    intent text NOT NULL,
    instrument text NOT NULL,
    mode text NOT NULL,
    filters jsonb DEFAULT '{}'::jsonb NOT NULL,
    result_refs jsonb DEFAULT '[]'::jsonb NOT NULL,
    returned_ref_count bigint DEFAULT 0 NOT NULL,
    total_hits bigint DEFAULT 0 NOT NULL,
    occurred_at timestamp with time zone NOT NULL,
    outcome text NOT NULL,
    CONSTRAINT search_access_events_outcome_check CHECK ((outcome = 'success'::text)),
    CONSTRAINT search_access_events_returned_ref_count_check CHECK ((returned_ref_count >= 0)),
    CONSTRAINT search_access_events_total_hits_check CHECK ((total_hits >= 0))
);



--
-- Name: threads; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.threads (
    id text NOT NULL,
    authored_by text NOT NULL,
    addressed_to text,
    replies_to text,
    concerns text,
    subject text,
    body text,
    evidence_id text,
    proposal_id text,
    trail_id text,
    requested_action text,
    claimed_by text,
    status text DEFAULT 'open'::text,
    resolution text,
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    claimed_at timestamp with time zone,
    resolved_at timestamp with time zone,
    scope text DEFAULT 'project'::text,
    resolved_by text,
    resolution_receipt_id text,
    message_kind text DEFAULT 'coordination'::text NOT NULL,
    CONSTRAINT threads_message_kind_check CHECK ((message_kind = ANY (ARRAY['task'::text, 'coordination'::text, 'ack'::text, 'progress'::text, 'ready'::text, 'blocked'::text, 'continuation'::text]))),
    CONSTRAINT threads_scope_check CHECK ((scope = ANY (ARRAY['user'::text, 'project'::text, 'thread'::text, 'default'::text]))),
    CONSTRAINT threads_status_check CHECK ((status = ANY (ARRAY['open'::text, 'claimed'::text, 'resolved'::text, 'expired'::text])))
);



--
-- Name: trail_steps; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.trail_steps (
    trail_id text NOT NULL,
    step_idx integer NOT NULL,
    ref_type public.ref_type NOT NULL,
    ref_id text NOT NULL,
    note text,
    step_type text DEFAULT 'produce'::text,
    facets jsonb DEFAULT '{}'::jsonb,
    search_vector tsvector,
    origin_agent text,
    status text DEFAULT 'proposed'::text,
    scope text DEFAULT 'project'::text
);



--
-- Name: trails; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.trails (
    id text NOT NULL,
    label text NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    agent_id text,
    session_id text,
    source_chat_id text,
    facets jsonb DEFAULT '{}'::jsonb,
    search_vector tsvector,
    status text DEFAULT 'proposed'::text,
    scope text DEFAULT 'project'::text
);



--
-- Name: authorization_attempts id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.authorization_attempts ALTER COLUMN id SET DEFAULT nextval('public.authorization_attempts_id_seq'::regclass);


--
-- Name: auto_approve_rules id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.auto_approve_rules ALTER COLUMN id SET DEFAULT nextval('public.auto_approve_rules_id_seq'::regclass);


--
-- Name: events id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.events ALTER COLUMN id SET DEFAULT nextval('public.events_id_seq'::regclass);


--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
--



--
-- Name: agent_availability agent_availability_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.agent_availability
    ADD CONSTRAINT agent_availability_pkey PRIMARY KEY (agent_id);


--
-- Name: authorization_admissions authorization_admissions_nonce_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.authorization_admissions
    ADD CONSTRAINT authorization_admissions_nonce_key UNIQUE (nonce);


--
-- Name: authorization_admissions authorization_admissions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.authorization_admissions
    ADD CONSTRAINT authorization_admissions_pkey PRIMARY KEY (authorization_id);


--
-- Name: authorization_attempts authorization_attempts_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.authorization_attempts
    ADD CONSTRAINT authorization_attempts_pkey PRIMARY KEY (id);


--
-- Name: authorization_gate_decisions authorization_gate_decisions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.authorization_gate_decisions
    ADD CONSTRAINT authorization_gate_decisions_pkey PRIMARY KEY (decision_id);


--
-- Name: auto_approve_rules auto_approve_rules_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.auto_approve_rules
    ADD CONSTRAINT auto_approve_rules_pkey PRIMARY KEY (id);


--
-- Name: breadcrumbs breadcrumbs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.breadcrumbs
    ADD CONSTRAINT breadcrumbs_pkey PRIMARY KEY (id);


--
-- Name: edges edges_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.edges
    ADD CONSTRAINT edges_pkey PRIMARY KEY (id);


--
-- Name: entity_read_heat entity_read_heat_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.entity_read_heat
    ADD CONSTRAINT entity_read_heat_pkey PRIMARY KEY (entity_type, entity_id);


--
-- Name: events events_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_pkey PRIMARY KEY (id);


--
-- Name: evidence evidence_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.evidence
    ADD CONSTRAINT evidence_pkey PRIMARY KEY (id);


--
-- Name: nodes nodes_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.nodes
    ADD CONSTRAINT nodes_pkey PRIMARY KEY (id);


--
-- Name: nursemaid_attempt_events nursemaid_attempt_events_job_id_attempt_no_sequence_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.nursemaid_attempt_events
    ADD CONSTRAINT nursemaid_attempt_events_job_id_attempt_no_sequence_key UNIQUE (job_id, attempt_no, sequence);


--
-- Name: nursemaid_attempt_events nursemaid_attempt_events_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.nursemaid_attempt_events
    ADD CONSTRAINT nursemaid_attempt_events_pkey PRIMARY KEY (id);


--
-- Name: nursemaid_control_events nursemaid_control_events_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.nursemaid_control_events
    ADD CONSTRAINT nursemaid_control_events_pkey PRIMARY KEY (id);


--
-- Name: nursemaid_control nursemaid_control_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.nursemaid_control
    ADD CONSTRAINT nursemaid_control_pkey PRIMARY KEY (singleton);


--
-- Name: nursemaid_jobs nursemaid_jobs_evidence_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.nursemaid_jobs
    ADD CONSTRAINT nursemaid_jobs_evidence_id_key UNIQUE (evidence_id);


--
-- Name: nursemaid_jobs nursemaid_jobs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.nursemaid_jobs
    ADD CONSTRAINT nursemaid_jobs_pkey PRIMARY KEY (id);


--
-- Name: nursemaid_jobs nursemaid_jobs_requested_by_idempotency_key_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.nursemaid_jobs
    ADD CONSTRAINT nursemaid_jobs_requested_by_idempotency_key_key UNIQUE (requested_by, idempotency_key);


--
-- Name: nursemaid_loops nursemaid_loops_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.nursemaid_loops
    ADD CONSTRAINT nursemaid_loops_pkey PRIMARY KEY (id);


--
-- Name: nursemaid_loops nursemaid_loops_requested_by_idempotency_key_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.nursemaid_loops
    ADD CONSTRAINT nursemaid_loops_requested_by_idempotency_key_key UNIQUE (requested_by, idempotency_key);


--
-- Name: oauth_authorization_codes oauth_authorization_codes_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.oauth_authorization_codes
    ADD CONSTRAINT oauth_authorization_codes_pkey PRIMARY KEY (code_hash);


--
-- Name: oauth_clients oauth_clients_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.oauth_clients
    ADD CONSTRAINT oauth_clients_pkey PRIMARY KEY (client_id);


--
-- Name: oauth_tokens oauth_tokens_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.oauth_tokens
    ADD CONSTRAINT oauth_tokens_pkey PRIMARY KEY (token_hash);


--
-- Name: open_threads open_threads_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.open_threads
    ADD CONSTRAINT open_threads_pkey PRIMARY KEY (id);


--
-- Name: parameter_requests parameter_requests_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.parameter_requests
    ADD CONSTRAINT parameter_requests_pkey PRIMARY KEY (id);


--
-- Name: proposals proposals_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.proposals
    ADD CONSTRAINT proposals_pkey PRIMARY KEY (id);


--
-- Name: quick_note_intakes quick_note_intakes_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.quick_note_intakes
    ADD CONSTRAINT quick_note_intakes_pkey PRIMARY KEY (id);


--
-- Name: quick_note_route_policies quick_note_route_policies_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.quick_note_route_policies
    ADD CONSTRAINT quick_note_route_policies_pkey PRIMARY KEY (id);


--
-- Name: quick_note_segments quick_note_segments_note_id_segment_index_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.quick_note_segments
    ADD CONSTRAINT quick_note_segments_note_id_segment_index_key UNIQUE (note_id, segment_index);


--
-- Name: quick_note_segments quick_note_segments_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.quick_note_segments
    ADD CONSTRAINT quick_note_segments_pkey PRIMARY KEY (id);


--
-- Name: read_access_events read_access_events_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.read_access_events
    ADD CONSTRAINT read_access_events_pkey PRIMARY KEY (id);


--
-- Name: registry registry_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.registry
    ADD CONSTRAINT registry_pkey PRIMARY KEY (id);


--
-- Name: search_access_events search_access_events_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.search_access_events
    ADD CONSTRAINT search_access_events_pkey PRIMARY KEY (id);


--
-- Name: threads threads_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.threads
    ADD CONSTRAINT threads_pkey PRIMARY KEY (id);


--
-- Name: trail_steps trail_steps_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.trail_steps
    ADD CONSTRAINT trail_steps_pkey PRIMARY KEY (trail_id, step_idx);


--
-- Name: trails trails_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.trails
    ADD CONSTRAINT trails_pkey PRIMARY KEY (id);


--
-- Name: authorization_admissions_agent_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX authorization_admissions_agent_idx ON public.authorization_admissions USING btree (addressed_agent, lane);


--
-- Name: authorization_attempts_authorization_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX authorization_attempts_authorization_idx ON public.authorization_attempts USING btree (authorization_id, id);


--
-- Name: edges_canonical_identity_unique; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX edges_canonical_identity_unique ON public.edges USING btree (source_id, target_id, relation_type, COALESCE(source_facet, ''::text), COALESCE(target_facet, ''::text)) WHERE (superseded_by IS NULL);


--
-- Name: idx_agent_availability_state; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_agent_availability_state ON public.agent_availability USING btree (state, heartbeat_at DESC);


--
-- Name: idx_breadcrumbs_read_event; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX idx_breadcrumbs_read_event ON public.breadcrumbs USING btree (read_event_id) WHERE (read_event_id IS NOT NULL);


--
-- Name: idx_breadcrumbs_search; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_breadcrumbs_search ON public.breadcrumbs USING gin (search_vector);


--
-- Name: idx_breadcrumbs_search_event; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX idx_breadcrumbs_search_event ON public.breadcrumbs USING btree (search_event_id) WHERE (search_event_id IS NOT NULL);


--
-- Name: idx_edges_facets; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_edges_facets ON public.edges USING gin (facets);


--
-- Name: idx_edges_lookup; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_edges_lookup ON public.edges USING btree (source_id, target_id);


--
-- Name: idx_edges_search; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_edges_search ON public.edges USING gin (search_vector);


--
-- Name: idx_edges_source_facet; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_edges_source_facet ON public.edges USING btree (source_id, source_facet);


--
-- Name: idx_edges_superseded_by; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_edges_superseded_by ON public.edges USING btree (superseded_by) WHERE (superseded_by IS NOT NULL);


--
-- Name: idx_edges_target_facet; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_edges_target_facet ON public.edges USING btree (target_id, target_facet);


--
-- Name: idx_events_facet; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_events_facet ON public.events USING btree (node_id, facet_key);


--
-- Name: idx_events_node_timestamp; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_events_node_timestamp ON public.events USING btree (node_id, "timestamp" DESC);


--
-- Name: idx_evidence_chat_seq; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_evidence_chat_seq ON public.evidence USING btree (source_chat_id, seq_idx);


--
-- Name: idx_evidence_created; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_evidence_created ON public.evidence USING btree (created_at DESC);


--
-- Name: idx_evidence_discovery_time; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_evidence_discovery_time ON public.evidence USING btree (discovery_time);


--
-- Name: idx_evidence_facets; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_evidence_facets ON public.evidence USING gin (facets);


--
-- Name: idx_evidence_instance_time; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_evidence_instance_time ON public.evidence USING btree (instance_time);


--
-- Name: idx_evidence_opacity_created; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_evidence_opacity_created ON public.evidence USING btree (opacity, created_at DESC);


--
-- Name: idx_evidence_search; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_evidence_search ON public.evidence USING gin (search_vector);


--
-- Name: idx_evidence_trail_id; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_evidence_trail_id ON public.evidence USING btree (trail_id);


--
-- Name: idx_evidence_type; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_evidence_type ON public.evidence USING btree (type);


--
-- Name: idx_evidence_type_created; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_evidence_type_created ON public.evidence USING btree (type, created_at DESC);


--
-- Name: idx_nodes_facets; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_nodes_facets ON public.nodes USING gin (facets);


--
-- Name: idx_nodes_heat; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_nodes_heat ON public.nodes USING btree (heat DESC);


--
-- Name: idx_nodes_scope; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_nodes_scope ON public.nodes USING btree (scope);


--
-- Name: idx_nodes_search; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_nodes_search ON public.nodes USING gin (search_vector);


--
-- Name: idx_nodes_type_created; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_nodes_type_created ON public.nodes USING btree (type, created_at DESC);


--
-- Name: idx_nursemaid_attempt_events_job; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_nursemaid_attempt_events_job ON public.nursemaid_attempt_events USING btree (job_id, attempt_no, sequence);


--
-- Name: idx_nursemaid_jobs_eligible; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_nursemaid_jobs_eligible ON public.nursemaid_jobs USING btree (execution_status, lease_expires_at, created_at);


--
-- Name: idx_nursemaid_jobs_execution_receipt; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX idx_nursemaid_jobs_execution_receipt ON public.nursemaid_jobs USING btree (execution_receipt_id) WHERE (execution_receipt_id IS NOT NULL);


--
-- Name: idx_nursemaid_jobs_loop; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_nursemaid_jobs_loop ON public.nursemaid_jobs USING btree (loop_id, execution_status, lease_expires_at);


--
-- Name: idx_nursemaid_jobs_requested; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_nursemaid_jobs_requested ON public.nursemaid_jobs USING btree (requested_by, created_at DESC);


--
-- Name: idx_nursemaid_jobs_trail_id; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_nursemaid_jobs_trail_id ON public.nursemaid_jobs USING btree (trail_id);


--
-- Name: idx_proposals_status_created; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_proposals_status_created ON public.proposals USING btree (status, created_at DESC);


--
-- Name: idx_proposals_type_created; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_proposals_type_created ON public.proposals USING btree (type, created_at DESC);


--
-- Name: idx_quick_note_segments_unassigned; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_quick_note_segments_unassigned ON public.quick_note_segments USING btree (note_id, segment_index) WHERE (route_status = 'unassigned'::text);


--
-- Name: idx_read_access_events_agent; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_read_access_events_agent ON public.read_access_events USING btree (agent_id, occurred_at DESC);


--
-- Name: idx_read_access_events_entity; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_read_access_events_entity ON public.read_access_events USING btree (entity_type, entity_id, occurred_at DESC);


--
-- Name: idx_registry_search; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_registry_search ON public.registry USING gin (search_vector);


--
-- Name: idx_search_access_events_agent; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_search_access_events_agent ON public.search_access_events USING btree (agent_id, occurred_at DESC);


--
-- Name: idx_search_access_events_instrument; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_search_access_events_instrument ON public.search_access_events USING btree (instrument, occurred_at DESC);


--
-- Name: idx_threads_actionable; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_threads_actionable ON public.threads USING btree (addressed_to, status, created_at DESC) WHERE (message_kind <> ALL (ARRAY['ack'::text, 'progress'::text]));


--
-- Name: idx_threads_addressed; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_threads_addressed ON public.threads USING btree (addressed_to, status);


--
-- Name: idx_threads_concerns; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_threads_concerns ON public.threads USING btree (concerns);


--
-- Name: idx_threads_created; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_threads_created ON public.threads USING btree (created_at DESC);


--
-- Name: idx_threads_resolved; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_threads_resolved ON public.threads USING btree (resolved_at DESC) WHERE (status = 'resolved'::text);


--
-- Name: idx_trail_steps_facets; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_trail_steps_facets ON public.trail_steps USING gin (facets);


--
-- Name: idx_trail_steps_ref; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_trail_steps_ref ON public.trail_steps USING btree (ref_type, ref_id);


--
-- Name: idx_trail_steps_search; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_trail_steps_search ON public.trail_steps USING gin (search_vector);


--
-- Name: idx_trails_agent_created; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_trails_agent_created ON public.trails USING btree (agent_id, created_at DESC);


--
-- Name: idx_trails_created; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_trails_created ON public.trails USING btree (created_at DESC);


--
-- Name: idx_trails_facets; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_trails_facets ON public.trails USING gin (facets);


--
-- Name: idx_trails_search; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_trails_search ON public.trails USING gin (search_vector);


--
-- Name: idx_trails_source_chat_created; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_trails_source_chat_created ON public.trails USING btree (source_chat_id, created_at DESC);


--
-- Name: uq_proposals_idempotency; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX uq_proposals_idempotency ON public.proposals USING btree (created_by, idempotency_key) WHERE (idempotency_key IS NOT NULL);


--
-- Name: authorization_admissions authorization_admissions_immutable_statement_trigger; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER authorization_admissions_immutable_statement_trigger BEFORE DELETE ON public.authorization_admissions FOR EACH STATEMENT EXECUTE FUNCTION public.authorization_admissions_no_delete();


--
-- Name: authorization_admissions authorization_admissions_immutable_trigger; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER authorization_admissions_immutable_trigger BEFORE DELETE OR UPDATE ON public.authorization_admissions FOR EACH ROW EXECUTE FUNCTION public.authorization_admissions_immutable();


--
-- Name: authorization_attempts authorization_attempts_append_only_statement_trigger; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER authorization_attempts_append_only_statement_trigger BEFORE DELETE OR UPDATE ON public.authorization_attempts FOR EACH STATEMENT EXECUTE FUNCTION public.authorization_attempts_append_only();


--
-- Name: authorization_attempts authorization_attempts_append_only_trigger; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER authorization_attempts_append_only_trigger BEFORE DELETE OR UPDATE ON public.authorization_attempts FOR EACH ROW EXECUTE FUNCTION public.authorization_attempts_append_only();


--
-- Name: authorization_gate_decisions authorization_gate_decisions_append_only_statement_trigger; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER authorization_gate_decisions_append_only_statement_trigger BEFORE DELETE OR UPDATE ON public.authorization_gate_decisions FOR EACH STATEMENT EXECUTE FUNCTION public.authorization_gate_decisions_append_only();


--
-- Name: authorization_gate_decisions authorization_gate_decisions_append_only_trigger; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER authorization_gate_decisions_append_only_trigger BEFORE DELETE OR UPDATE ON public.authorization_gate_decisions FOR EACH ROW EXECUTE FUNCTION public.authorization_gate_decisions_append_only();


--
-- Name: nursemaid_attempt_events nursemaid_attempt_events_append_only; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER nursemaid_attempt_events_append_only BEFORE DELETE OR UPDATE ON public.nursemaid_attempt_events FOR EACH ROW EXECUTE FUNCTION public.reject_nursemaid_attempt_event_mutation();


--
-- Name: nursemaid_control_events nursemaid_control_events_append_only; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER nursemaid_control_events_append_only BEFORE DELETE OR UPDATE ON public.nursemaid_control_events FOR EACH ROW EXECUTE FUNCTION public.reject_nursemaid_control_event_mutation();


--
-- Name: read_access_events read_access_events_append_only; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER read_access_events_append_only BEFORE DELETE OR UPDATE ON public.read_access_events FOR EACH ROW EXECUTE FUNCTION public.reject_read_access_event_mutation();


--
-- Name: search_access_events search_access_events_append_only; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER search_access_events_append_only BEFORE DELETE OR UPDATE ON public.search_access_events FOR EACH ROW EXECUTE FUNCTION public.reject_search_access_event_mutation();


--
-- Name: edges tr_audit_edges; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER tr_audit_edges AFTER INSERT ON public.edges FOR EACH ROW EXECUTE FUNCTION public.fn_log_new_edge();


--
-- Name: nodes tr_audit_nodes; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER tr_audit_nodes AFTER UPDATE ON public.nodes FOR EACH ROW EXECUTE FUNCTION public.fn_log_node_change();


--
-- Name: nodes tr_guard_nodes_vector; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER tr_guard_nodes_vector BEFORE UPDATE OF state_vector ON public.nodes FOR EACH ROW EXECUTE FUNCTION public.fn_guard_state_vector();


--
-- Name: breadcrumbs trg_breadcrumbs_search; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER trg_breadcrumbs_search BEFORE INSERT OR UPDATE ON public.breadcrumbs FOR EACH ROW EXECUTE FUNCTION public.update_breadcrumbs_search();


--
-- Name: edges trg_edges_search; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER trg_edges_search BEFORE INSERT OR UPDATE ON public.edges FOR EACH ROW EXECUTE FUNCTION public.update_edges_search();


--
-- Name: evidence trg_evidence_search; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER trg_evidence_search BEFORE INSERT OR UPDATE ON public.evidence FOR EACH ROW EXECUTE FUNCTION public.update_evidence_search();


--
-- Name: nodes trg_nodes_search; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER trg_nodes_search BEFORE INSERT OR UPDATE ON public.nodes FOR EACH ROW EXECUTE FUNCTION public.update_nodes_search();


--
-- Name: registry trg_registry_search; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER trg_registry_search BEFORE INSERT OR UPDATE ON public.registry FOR EACH ROW EXECUTE FUNCTION public.update_registry_search();


--
-- Name: trail_steps trg_trail_steps_search; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER trg_trail_steps_search BEFORE INSERT OR UPDATE ON public.trail_steps FOR EACH ROW EXECUTE FUNCTION public.update_trail_steps_search();


--
-- Name: trails trg_trails_search; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER trg_trails_search BEFORE INSERT OR UPDATE ON public.trails FOR EACH ROW EXECUTE FUNCTION public.update_trails_search();


--
-- Name: authorization_admissions authorization_admissions_gate_decision_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.authorization_admissions
    ADD CONSTRAINT authorization_admissions_gate_decision_id_fkey FOREIGN KEY (gate_decision_id) REFERENCES public.authorization_gate_decisions(decision_id);


--
-- Name: breadcrumbs breadcrumbs_trail_id_step_idx_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.breadcrumbs
    ADD CONSTRAINT breadcrumbs_trail_id_step_idx_fkey FOREIGN KEY (trail_id, step_idx) REFERENCES public.trail_steps(trail_id, step_idx);


--
-- Name: edges edges_superseded_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.edges
    ADD CONSTRAINT edges_superseded_by_fkey FOREIGN KEY (superseded_by) REFERENCES public.edges(id);


--
-- Name: events events_edge_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_edge_id_fkey FOREIGN KEY (edge_id) REFERENCES public.edges(id);


--
-- Name: events events_node_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_node_id_fkey FOREIGN KEY (node_id) REFERENCES public.nodes(id);


--
-- Name: events events_parent_event_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_parent_event_id_fkey FOREIGN KEY (parent_event_id) REFERENCES public.events(id);


--
-- Name: evidence evidence_trail_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.evidence
    ADD CONSTRAINT evidence_trail_id_fkey FOREIGN KEY (trail_id) REFERENCES public.trails(id) NOT VALID;


--
-- Name: nursemaid_attempt_events nursemaid_attempt_events_job_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.nursemaid_attempt_events
    ADD CONSTRAINT nursemaid_attempt_events_job_id_fkey FOREIGN KEY (job_id) REFERENCES public.nursemaid_jobs(id);


--
-- Name: nursemaid_jobs nursemaid_jobs_loop_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.nursemaid_jobs
    ADD CONSTRAINT nursemaid_jobs_loop_id_fkey FOREIGN KEY (loop_id) REFERENCES public.nursemaid_loops(id);


--
-- Name: nursemaid_jobs nursemaid_jobs_trail_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.nursemaid_jobs
    ADD CONSTRAINT nursemaid_jobs_trail_id_fkey FOREIGN KEY (trail_id) REFERENCES public.trails(id) NOT VALID;


--
-- Name: oauth_authorization_codes oauth_authorization_codes_client_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.oauth_authorization_codes
    ADD CONSTRAINT oauth_authorization_codes_client_id_fkey FOREIGN KEY (client_id) REFERENCES public.oauth_clients(client_id);


--
-- Name: oauth_tokens oauth_tokens_client_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.oauth_tokens
    ADD CONSTRAINT oauth_tokens_client_id_fkey FOREIGN KEY (client_id) REFERENCES public.oauth_clients(client_id);


--
-- Name: open_threads open_threads_related_node_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.open_threads
    ADD CONSTRAINT open_threads_related_node_id_fkey FOREIGN KEY (related_node_id) REFERENCES public.nodes(id);


--
-- Name: quick_note_segments quick_note_segments_note_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.quick_note_segments
    ADD CONSTRAINT quick_note_segments_note_id_fkey FOREIGN KEY (note_id) REFERENCES public.quick_note_intakes(id);


--
-- Name: trail_steps trail_steps_trail_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.trail_steps
    ADD CONSTRAINT trail_steps_trail_id_fkey FOREIGN KEY (trail_id) REFERENCES public.trails(id) ON DELETE CASCADE;


--
-- PostgreSQL database dump complete
--