Add a credential kind

This guide covers the complete checklist for adding a new credential kind backed by the Postgres AES-GCM envelope — the same pattern used by api_key_refs, git_credentials, claude_oauth_tokens, tracker_oauth_credentials, and env_vars.

If the secret material lives in OpenBao (a vault path or KV2 reference), it is an OpenBao-ref kind (resources, mailbox_credentials). That kind is out of scope here — see services::secret_scope::kind_backend and ADR 0038 D6.

Note

Credentials are Postgres-envelope or OpenBao-ref — never mix the two in one table. The uniform resolver in services::secret_scope governs Postgres-envelope kinds only.

Background: the three-tier model

Every Postgres-envelope credential row belongs to exactly one scope tier, enforced by an XOR CHECK (migration 156) and encoded in CredentialScope (persistence/db/models/credential_scope.rs):

Scope	workspace_id	organization_id	Wins when
Personal	NULL	NULL	most-specific by default
Workspace	set	NULL	overrides org
Organization	NULL	set	default-last; wins if locked = true

The resolution rule from services::secret_scope::resolve_uniform:

if org[name].locked  →  org wins (org-shared-wins short-circuit)
else                 →  personal > workspace > org  (most-specific-wins)

Resolution happens in SQL via ORDER BY CASE … LIMIT 1 — the database returns the single winner, never a full candidate list.

The locked flag (migration 164) is meaningful only on org rows. An org credential with locked = true overrides any workspace or personal credential of the same logical key, enabling billing/policy enforcement use-cases without a per-kind precedence rule.

Steps

1. Write the migration.

   Scan the current highest migration number first — the namespace is shared across parallel developers and worktrees. See Add a database migration for the lookup recipe.

   The table must include the three scope columns, the XOR CHECK, the locked flag, and org-aware RLS policies. Use api_key_refs + migrations 156 and 164 as the template:

   CREATE TABLE my_credentials (
       id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
       user_id         UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
       workspace_id    UUID REFERENCES workspaces(id) ON DELETE CASCADE,
       organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
       locked          BOOLEAN NOT NULL DEFAULT false,
       secret_data     TEXT NOT NULL,   -- will hold scenc:v1: ciphertext
       -- ... your kind-specific columns ...
       created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
   
       CONSTRAINT my_credentials_scope_xor_team_org
           CHECK (workspace_id IS NULL OR organization_id IS NULL)
   );
   
   -- Index for org-scoped resolution
   CREATE INDEX idx_my_credentials_org
       ON my_credentials (organization_id)
       WHERE organization_id IS NOT NULL;
   
   -- RLS: read = any member; mutate = workspace admin OR org owner/admin
   ALTER TABLE my_credentials ENABLE ROW LEVEL SECURITY;
   
   CREATE POLICY my_credentials_select ON my_credentials FOR SELECT USING (
       user_id = (select auth.uid())
       OR workspace_id IN (SELECT workspace_id FROM workspace_members WHERE user_id = (select auth.uid()))
       OR organization_id IN (SELECT organization_id FROM organization_members WHERE user_id = (select auth.uid()))
   );
   CREATE POLICY my_credentials_insert ON my_credentials FOR INSERT WITH CHECK (
       (organization_id IS NULL AND user_id = (select auth.uid()))
       OR organization_id IN (SELECT organization_id FROM organization_members WHERE user_id = (select auth.uid()) AND role IN ('owner', 'admin'))
   );
   CREATE POLICY my_credentials_update ON my_credentials FOR UPDATE USING (
       user_id = (select auth.uid())
       OR workspace_id IN (SELECT workspace_id FROM workspace_members WHERE user_id = (select auth.uid()) AND role IN ('owner', 'admin'))
       OR organization_id IN (SELECT organization_id FROM organization_members WHERE user_id = (select auth.uid()) AND role IN ('owner', 'admin'))
   );
   CREATE POLICY my_credentials_delete ON my_credentials FOR DELETE USING (
       user_id = (select auth.uid())
       OR workspace_id IN (SELECT workspace_id FROM workspace_members WHERE user_id = (select auth.uid()) AND role IN ('owner', 'admin'))
       OR organization_id IN (SELECT organization_id FROM organization_members WHERE user_id = (select auth.uid()) AND role IN ('owner', 'admin'))
   );

   Caution

   If the kind has no personal scope (like tracker_oauth_credentials), replace the XOR CHECK with num_nonnulls(workspace_id, organization_id) = 1, drop the user_id arm from all four policies, and document the exemption in the ADR.

2. Register the backend in secret_scope::kind_backend.

   Open server/src/services/secret_scope.rs and add your table name to the PostgresEnvelope arm:

   "api_key_refs"
   | "env_vars"
   | "git_credentials"
   | "claude_oauth_tokens"
   | "tracker_oauth_credentials"
   | "my_credentials"              // add this
       => Some(SecretBackend::PostgresEnvelope),

   The kind_backend tests will catch an unregistered or misclassified kind.

3. Add the query layer with scope-aware AAD.

   Create server/src/persistence/db/queries/my_credentials.rs. Choose a table-qualified AAD base string — "my_credentials.secret_data" — and use the protect_secret / reveal_secret helpers from queries::credential_secret:

   const SECRET_DATA_AAD: &str = "my_credentials.secret_data";
   
   pub async fn insert_my_credential(
       pool: &PgPool,
       user_id: Uuid,
       secret: &str,
       scope: CredentialScope,
   ) -> sqlx::Result<MyCredential> {
       let (workspace_id, org_id) = (scope.workspace_id(), scope.organization_id());
       let protected = protect_secret(secret, SECRET_DATA_AAD, workspace_id, org_id)?;
       sqlx::query_as::<_, MyCredential>(
           "INSERT INTO my_credentials (user_id, secret_data, workspace_id, organization_id)
            VALUES ($1, $2, $3, $4) RETURNING *",
       )
       .bind(user_id).bind(protected).bind(workspace_id).bind(org_id)
       .fetch_one(pool).await
       // reveal on the way out:
       .and_then(reveal_my_credential)
   }
   
   fn reveal_my_credential(mut row: MyCredential) -> sqlx::Result<MyCredential> {
       row.secret_data = reveal_secret(
           &row.secret_data,
           SECRET_DATA_AAD,
           row.workspace_id,
           row.organization_id,
       )?;
       Ok(row)
   }

   The scoped_aad function in app/db_secrets.rs derives the AAD as:

   • workspace row: my_credentials.secret_data.workspace.<workspace_id>
   • org row: my_credentials.secret_data.organization.<org_id>
   • personal row: my_credentials.secret_data.user

   AES-GCM authentication will fail if a ciphertext is moved to a row with a different tenant id — this is the cross-tenant relocation guard from ADR 0038 D7.

4. Write the single-winner resolver.

   For point-in-time resolution (fetch one credential by logical key), mirror the ORDER BY CASE … LIMIT 1 pattern. The ranks come from secret_scope::case_rank:

   SELECT *
   FROM my_credentials
   WHERE user_id = $user   -- personal candidates
      OR workspace_id = $ws
      OR organization_id = $org
   ORDER BY
       CASE
           WHEN organization_id IS NOT NULL AND locked THEN 0
           WHEN user_id = $user AND workspace_id IS NULL AND organization_id IS NULL THEN 1
           WHEN workspace_id IS NOT NULL THEN 2
           ELSE 3  -- unlocked org
       END
   LIMIT 1

   The secret_scope unit tests assert that resolve_uniform and case_rank agree, so any divergence between this SQL and the spec is caught in CI.

5. Gate writes by scope authority.

   • Personal / workspace rows: any authenticated user may write to their own personal rows; workspace rows require WorkspaceAction::ManageCredentials (or workspace admin role).
   • Organization rows: require OrgAction::ManageCredentials (org owner or admin). The management-API state-apply writer must stay workspace-clamped — it must never write an org-scoped row (ADR 0038 D3 anti-contamination).
   • locked toggle: org rows only; require org-admin authority. Add a query equivalent to set_api_key_locked in queries/credentials.rs.

6. Verify.

   cd server && cargo check
   cargo test arch
   cargo test secret_scope

   cargo test arch enforces the 500-line source-file cap and module-boundary rules. cargo test secret_scope runs the resolve_uniform / case_rank agreement test.

See also

• server/src/services/secret_scope.rs — canonical resolver spec
• server/src/app/db_secrets.rs — scoped_aad, protect_scoped, reveal_scoped
• server/src/persistence/db/models/credential_scope.rs — CredentialScope enum
• server/migrations/156_credential_org_scope.sql — XOR CHECK template
• server/migrations/164_credential_locked_and_org_rls.sql — locked + RLS template
• ADR 0038 — uniform three-tier scope rationale
• Add a database migration