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. | Provider | Env prefix | CLI | Reference | Status | | ---------- | --------------- | -------- | ------------------------------------------------------------------------ | --------- | | MSSQL | `BW_MSSQL_*` | `sqlcmd` | [references/providers/mssql.md](references/providers/mssql.md) | **Ready** | | MySQL | `BW_MYSQL_*` | `mysql` | [references/providers/mysql.md](references/providers/mysql.md) | **Ready** | | PostgreSQL | `BW_POSTGRES_*` | `psql` | [references/providers/postgresql.md](references/providers/postgresql.md) | **Ready** | ## 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](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](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 | Reference | When to read | | -------------------------------------------------------------------------------- | ------------------------------------------------------------------------- | | [references/sources.md](references/sources.md) | Finding the source file for any table, enum, or canonical function | | [references/schema-discovery-queries.md](references/schema-discovery-queries.md) | Live introspection — list tables, describe columns, find FKs, view bodies | | [references/providers/mssql.md](references/providers/mssql.md) | MSSQL connection, sqlcmd invocation patterns, dialect notes |