Key-Value

-- Set runtime knobs for your app.
SET CONFIG acme.pagination.pageSize = 100;
SET CONFIG acme.search.maxResults  = 50;
SET CONFIG feature.new_dashboard   = 'on';

-- Reference inline from any SQL statement.
SELECT id, name FROM users
ORDER BY id LIMIT $config.acme.pagination.pageSize;

-- $config.<path> is resolved as a typed Value at parse time —
-- not a textual splice — so SQL injection through config writes
-- is structurally impossible.

-- Write under the red.secret.* namespace.
SET CONFIG red.secret.openai.api_key   = 'sk-...';
SET CONFIG red.secret.stripe.api_key   = 'sk_live_...';

-- Reference inline; the engine pulls the typed Value from vault.
INSERT INTO notes (body) VALUES ($1)
  WITH AUTO EMBED (body) USING openai;
-- The OpenAI provider reads $red.secret.openai.api_key internally —
-- never appears in your application code, logs, or query plans.

-- Sealed read protects the value from non-privileged principals.
SELECT $secret.red.secret.stripe.api_key;
-- → '***' unless the policy allows the principal to UNSEAL.

-- Create a KV collection with a TTL on every entry.
CREATE TABLE sessions KV (
  key      TEXT PRIMARY KEY,
  value    JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- It is a regular collection — the full SQL surface applies.
INSERT INTO sessions DOCUMENT (key, value)
VALUES ('abc-123', {"user_id": 42, "scope": "admin"})
WITH TTL 3600000;

SELECT value FROM sessions WHERE key = 'abc-123';

-- ON CONFLICT DO NOTHING gives you idempotent inserts.
INSERT INTO idempotency (key, value) VALUES ($1, '1')
ON CONFLICT (key) DO NOTHING
RETURNING key;
-- 0 rows back means the key already existed (replay).

-- Page-size knob in engine config.
SET CONFIG acme.pagination.pageSize = 25;

-- Iterate a KV namespace using the config knob.
SELECT key, value
FROM sessions
WHERE key LIKE 'org:42:%'
ORDER BY created_at DESC
LIMIT $config.acme.pagination.pageSize;

-- Combine with $secret for ops that need a vault value.
INSERT INTO webhook_events (id, payload, signature_check)
VALUES ($1, $2, hmac_sha256($2, $secret.red.secret.stripe.webhook_secret));