| name | exploring-bitwarden-data |
| description | Read-only exploration of a local Bitwarden development database — answer business questions from live data, verify seeded fixtures, and introspect schema. Use whenever the user wants to query, count, look up, verify, or explore data in a local Bitwarden database ("how many orgs/users/ciphers", "show me collections", "check what the seeder created", "look up user X", "which orgs have feature Y"), even without the word SQL. Not for authoring stored procedures, migrations, or repository code (use writing-database-queries), and not for seeding or modifying data. |
| argument-hint | [mssql|mysql|postgresql] <question or SQL> |
| user-invocable | true |
| allowed-tools | Bash(which sqlcmd), Bash(sqlcmd:*), Bash(mysql:*), Bash(psql:*) |
Explore Bitwarden Database
Read-only access to a local Bitwarden database, across all three dev providers.
Read-only, defense in depth
- Database login is read-only at the server — mutations will fail regardless of what you send.
- 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).
- 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.
- 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).
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
