Declared tables

A secret that belongs to somebody

A Stripe key per customer. A webhook signing secret per organization. A signing key per installation. Each is a credential your application mints on a request, for one owner, and has to find again by that owner — and remove, every row of it, the day the owner leaves.

A keyspace gives that owner one opaque string. You encode the owner into the key, and Pithy sees a name. A declared table gives the owner columns Pithy can see, query and delete by.

Declare the table

import { defineSecretTable } from "@pithy-sh/secrets/src/index";
import { z } from "zod";

export const CustomerSecrets = defineSecretTable({
  name: "customer",
  keys: {
    userId: z.string().describe("The user the secret belongs to."),
    organizationId: z.string().describe("The organization the user belongs to."),
  },
  secrets: {
    STRIPE_KEY: { valueType: "text", rotatable: true, rotateEveryDays: 90 },
    WEBHOOK_SIG: { valueType: "text", rotatable: true },
  },
});

That declares pithy_secrets_customer_secrets in the secrets D1, beside the system table. Its row is the system table’s — id, name, encryptedValue, iv, keyVersion, valueType, createdAt, updatedAt — plus a column per key. It is unique on the keys and the name, with an index per key.

Key and table names are camelCase with no two capitals together — customerApi, not customerAPI. Kysely’s CamelCasePlugin folds a run of capitals into one word and would read the name back as something else.

d1 is the only backend. A cf-secrets-store secret is refused when the table is declared: a Secrets Store binding is declared in wrangler.jsonc at build time, and a per-owner name does not exist then.

rotatable is a gate here, not metadata. rotate on a secret declared rotatable: false throws, and its value changes with put(owner, name, value, { replace: true }). rotateEveryDays on a table’s secret is an expectation and nothing more: the secrets manager’s cron rotates registry entries, never a table’s rows, and the at-rest key rotation is what runs over them.

Compose it in the Worker that owns it

Through the app capability’s SECRETS database:

import { secretTablesDatabase } from "@pithy-sh/secrets/src/index";
import { secretTableMigrations } from "./migrations/secrets";

const app = defineCapability({
  name: "app",
  requiredBindings: [],
  databases: {
    secrets: secretTablesDatabase({ tables: [CustomerSecrets], migrations: secretTableMigrations }),
  },
});

Leave migrations out until the first pithy migrate has written apps/<worker>/migrations/secrets/index.ts. One Worker owns a table’s migrations. Another Worker that reads the table needs the declaration alone.

The migration is generated, and so is every one after it

The declaration is the shape, so the migration is derived from it rather than transcribed. A hand-written table that left out encryptedValue, iv or keyVersion would be a table that silently never rotates.

pithy migrate diffs each declaration against the chain, writes the next migration into apps/<worker>/migrations/secrets/, and stops before applying anything. The file is yours to read and commit. Running pithy migrate again applies it. pithy migrate has the output and the exit code.

• A key or a secret name added writes an alter.
• A secret name removed writes an alter too. It leaves the table’s registration, and rows already holding it stay as they are.
• A key removed or renamed is refused by name, never generated. A migration that drops a column holding somebody’s key is a data loss you author on purpose.

A key added later holds "" on every row written before it. Address those rows with that key empty, beside the keys they were written with.

Read and write it from the accessor you already hold

The owner is an object. list and deleteWhere take any subset of the keys:

const t = (await secretsStore(env, registry)).table(CustomerSecrets);

await t.put({ userId, organizationId }, "STRIPE_KEY", value);   // create-only; { replace: true } discards
await t.get({ userId, organizationId }, "STRIPE_KEY");
await t.rotate({ userId, organizationId }, "STRIPE_KEY", next); // the prior version stays valid
await t.list({ organizationId });                               // every secret this organization holds
await t.deleteWhere({ organizationId });                        // tenant offboarding, one call, returns a count
await t.deleteWhere({ userId });                                // user deletion

Only declared secret names and only declared key fields compile. deleteWhere({}) and list({}) are refused: no owner is not an owner. list returns owners, names, key versions and timestamps, never a value.

put holds every key to its declared schema, "" included, so a z.string().uuid() key refuses an empty one. No new row is written that deleteWhere on that key could never reach.

The value path is the system table’s, unchanged

The same master key, the same AES-256-GCM envelope, the same { currentVersion, versions } value envelope, the same at-rest rotation.

Each row’s ciphertext is bound to its table, its owner and its secret name. A value moved into another owner’s row does not open, inside the primitive — the same guarantee a keyspace member gets from its bound name.

The rotation finds the table without a redeploy. The generated migration registers the table in pithy_secrets_tables, and the manager reads that registry on every at-rest pass. A table registered after the manager was deployed rotates on the next pass, with no re-provision. An old master key is pruned only once no row in the system table or any declared table is still sealed under it.

Every declared table is retained, like the system table. pithy migrate --rollback counts its rows before any down runs over them, and pithy secrets deprovision counts them before it deletes a vault.

The audit names the owner, never the value

put, rotate, delete and deleteWhere emit audit events when the call passes { audit }.

An operator sees the tables, and never a row

pithy secrets tables --env prod

pithy secrets tables lists each declared table, its keys, its secret names, how many rows hold each, and which master-key version sealed them. pithy doctor reports the same read as its Secret tables: block.

No command creates, reads or deletes a row. These are runtime data your application writes, and a customer’s plaintext does not belong on an operator’s screen or in a shell history.

When a declared table beats a keyspace

A keyspace is right when the key is the whole story — one signing key per connection id, with nothing else to ask. A declared table is right when the owner has a shape:

• The owner has more than one part. A user inside an organization has no single key prefix a scan could match, so one of the two axes is unqueryable as a keyspace whichever way you order them. In a table each is a column, and each is indexed.
• You will ask what an owner holds. list({ organizationId }) is one indexed query. A keyspace can only fetch a key you already know.
• The owner will leave. deleteWhere({ organizationId }) removes every row and says how many. With a keyspace, offboarding is a sweep you write yourself — the operation that most needs to be exactly right.

Keyspaces stay, unchanged, for the case with no owner worth typing. Nothing migrates a keyspace’s members into a table.