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
);
