exploring-bitwarden-data

Explore Bitwarden Database

Read-only access to a local Bitwarden database, across all three dev providers.

Read-only, defense in depth

1. Database login is read-only at the server — mutations will fail regardless of what you send.
2. Allowed: SELECT, WITH (CTEs), and INFORMATION_SCHEMA / sys.* introspection.

Cross-provider rules

• Secrets. Never echo, log, cat, printenv, or hexdump any password env var. Set passwords inline on the command (e.g., SQLCMDPASSWORD="$BW_MSSQL_PASSWORD" sqlcmd ...); never export them.
• Result presentation. Format <20 rows as a markdown table; summarize larger sets as top-N + count. Always echo the SQL ran. Trim CLI footers ((N rows affected), Query OK) before presenting.
• Heredoc footgun. Use single-quoted heredoc tags (<<'SQL') — without quotes, bash expands $ inside the SQL before the database CLI sees it, breaking column references.

Provider selection

First arg picks the provider — mssql (default), mysql, or postgresql. Read the matching provider reference before composing SQL.

The repo is the schema's source of truth

Don't compose SQL from a generic mental model of how a vault schema "probably" looks — and don't expect this skill to inventory the schema for you. The repo already does, and it stays current when this file wouldn't:

• Tables and columns: SSDT schema under src/Sql/dbo/, or live introspection via references/schema-discovery-queries.md.
• Enum integer values and lifecycle semantics: the C# enum sources — their XML docs carry meaning no value table can (seat consumption, restore behavior, deprecations).
• Access-control logic: prefer the canonical functions over hand-rolled joins — [dbo].[UserCipherDetails](@UserId) for "what can user X see", [dbo].[UserCollectionDetails](@UserId) for collection permissions. They encode member status, org enablement, and direct-over-group grant precedence that is easy to rebuild subtly wrong.
• Where to look: references/sources.md maps every concept named in this skill to its source file.

Grounding rules

Semantics the schema itself cannot tell you — each of these flipped a real eval case that unaided Claude got wrong (evidence in evals/baseline-results.md; that is also the bar for adding a rule here).

1. Active member = OrganizationUser.Status = 2 (Confirmed). "Active" is genuinely ambiguous — the occupied-seat definition (Status IN (0,1,2), used by the seat-count procs) is a defensible rival reading, so state which one the question needs. Full lifecycle (including Staged and Revoked-with-restore) is documented in OrganizationUserStatusType.cs.
2. Archive state lives in Cipher.Archives — per-user JSON keyed by UPPERCASE user GUID — not in the ArchivedDate column. ArchivedDate exists on the table but the archive flow never writes it (Cipher_Archive does JSON_MODIFY on Archives); querying it returns zero forever while looking perfectly reasonable. Favorites and Folders use the same per-user JSON shape, so interpolate keys from a UNIQUEIDENTIFIER (SQL Server renders them uppercase; JSON keys are case-sensitive).
3. Organization.Enabled = 1 is the active flag. Organization.Status is the provider-management lifecycle (Pending/Created/Managed), and Plan is a display string — aggregate and filter on PlanType.

Reference library