RFC 0010: Unified Variable & Secret Resolution System

CREATE TYPE variable_scope as ENUM (
            'resource',
            'deployment',
            'deployment_job_agent'
);

CREATE TYPE variable_value_kind as ENUM (
            'literal',
            'ref',
            'secret_ref'
);

CREATE TABLE IF NOT EXISTS variable (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),

    scope variable_scope not null,

    resource_id uuid references resource(id) on delete cascade,
    deployment_id uuid references deployment(id) on delete cascade,
    deployment_job_agent_id uuid references deployment_job_agent(id) on delete cascade,

    key text not null,

    -- metadata
    is_sensitive boolean not null default false,
    description text,

    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),

    -- exactly one owner target must be set, and it must match scope
    CONSTRAINT variable_scope_target_check check (
        (
            scope = 'resource'
            and resource_id is not null
            and deployment_id is null
            and deployment_job_agent_id is null
        )
        or
        (
            scope = 'deployment'
            and deployment_id is not null
            and resource_id is null
            and deployment_job_agent_id is null
        )
        or
        (
            scope = 'deployment_job_agent'
            and deployment_job_agent_id is not null
            and resource_id is null
            and deployment_id is null
        )
    )
);

create unique index if not exists variable_resource_key_uniq
    on variable(resource_id, key)
    where resource_id is not null;

create unique index if not exists variable_deployment_key_uniq
    on variable(deployment_id, key)
    where deployment_id is not null;

create unique index if not exists variable_dja_key_uniq
    on variable(deployment_job_agent_id, key)
    where deployment_job_agent_id is not null;

create index if not exists variable_scope_idx
    on variable(scope);

create index if not exists variable_resource_lookup_idx
    on variable(resource_id, key)
    where resource_id is not null;

create index if not exists variable_deployment_lookup_idx
    on variable(deployment_id, key)
    where deployment_id is not null;

create index if not exists variable_dja_lookup_idx
    on variable(deployment_job_agent_id, key)
    where deployment_job_agent_id is not null;


CREATE TABLE IF NOT EXISTS variable_value (
    id uuid primary key default uuid_generate_v4(),

    variable_id uuid not null references variable(id) on delete cascade,

    resource_selector text,

    priority bigint not null default 0,

    kind variable_value_kind not null,

    literal_value jsonb,

    ref_key text,
    ref_path text[],

    secret_provider text,
    secret_key text,
    secret_path text[],

    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),

    CONSTRAINT variable_value_kind_shape_check check (
        (
            kind = 'literal'
            and literal_value is not null
            and ref_key is null
            and ref_path is null
            and secret_provider is null
            and secret_key is null
            and secret_path is null
        )
        or
        (
            kind = 'ref'
            and literal_value is null
            and ref_key is not null
            and secret_provider is null
            and secret_key is null
            and secret_path is null
        )
        or
        (
            kind = 'secret_ref'
            and literal_value is null
            and ref_key is null
            and ref_path is null
            and secret_provider is not null
            and secret_key is not null
        )
    )
);

create index if not exists variable_value_variable_priority_idx
    on variable_value(variable_id, priority desc, id);

create index if not exists variable_value_selector_idx
    on variable_value(variable_id, resource_selector, priority desc);

create index if not exists variable_value_kind_idx
    on variable_value(kind);

create unique index if not exists variable_value_resolution_uniq
    on variable_value (
        variable_id,
        coalesce(resource_selector, ''),
        priority
    );

{ "host": "db.internal", "port": 5432 }

{ "ref": "db.config", "path": ["host"] }

{
  "provider": "vault",
  "key": "kv/data/prod/db",
  "path": ["password"]
}