> ## Documentation Index
> Fetch the complete documentation index at: https://docs.theauth.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Use the SQLite Backend

> storage/sqlite is a pure Go (modernc.org/sqlite, no cgo) adapter that runs on a sql.DB you already own.

`storage/sqlite` is a pure Go (`modernc.org/sqlite`, no cgo) adapter that runs on a `*sql.DB` you already own. It is its own Go module, so the SQLite driver is only pulled in when you import it.

```sh theme={"dark"}
go get github.com/glincker/theauth-go/storage/sqlite
```

## What it covers

| Capability | Status |
| - | - |
| Users, sessions, magic links, passwords (`CoreStorage`) | Implemented |
| OAuth accounts, WebAuthn, TOTP, audit log | Implemented |
| Session list, last seen, step-up, session links (`SessionManagementStorage`, `SessionLinkStorage`) | Implemented |
| API tokens and RFC 8628 device codes (`APITokenStorage`, `DeviceCodeStorage`) | Implemented |
| TOTP replay, user count, passkey rename, recovery codes (`TOTPReplayStorage`, `UserCountStorage`, `WebAuthnRenameStorage`, `RecoveryCodeStorage`) | Implemented |
| Shared login throttle (`Store.ThrottleStore`) | Implemented |
| Organizations, SAML, SCIM, RBAC | Not implemented, returns `ErrStorageMissingCapability` |
| OAuth authorization server storage | Not implemented |

Set it as `Config.CoreStorage`. Features that need a missing capability fail at `New` instead of at request time.

## Open the database

You open the database and own its pragmas. Foreign keys must be on for every connection, and `New` refuses a database where they are off (pass `AllowForeignKeysOff` to override, at the cost of `ON DELETE CASCADE`). Use WAL and a busy timeout when anything else writes to the file:

```go theme={"dark"}
db, err := sql.Open("sqlite",
    "file:app.db?_pragma=foreign_keys(1)&_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)&_txlock=immediate")
```

Run the first `Ping` serially at startup: switching a fresh file to WAL needs an exclusive lock that parallel first connections can lose with `SQLITE_BUSY`. `_txlock=immediate` makes every transaction take the write lock up front, which avoids `SQLITE_BUSY` on read-then-write upgrades under contention.

## Standalone: let the package migrate

```go theme={"dark"}
import sqlitestore "github.com/glincker/theauth-go/storage/sqlite"

if err := sqlitestore.Migrate(ctx, db); err != nil { ... }
store, err := sqlitestore.New(db)
```

`Migrate` records applied versions in `theauth_schema_migrations`. Each step runs under `BEGIN IMMEDIATE` together with its ledger row, so replicas starting at once serialize and a failed step leaves nothing half applied.

## Hosted: fold the SQL into your own migrator

`Migrations()` returns an `fs.FS` of numbered, forward-only `.sql` files (`0001_core.sql`, `0002_oauth_accounts.sql`, ...). Copy or embed them into your own numbered history, keeping their order, and do not call `Migrate`:

```go theme={"dark"}
files, _ := fs.Glob(sqlitestore.Migrations(), "*.sql")
```

If you use a custom prefix, `RenderMigrations(sqlitestore.WithTablePrefix("auth_"))` returns the same steps with the prefix applied. Never edit a step you have already shipped; later versions of this package only add files.

## Table prefix

Tables default to the `theauth_` prefix so they cannot collide with host tables. Pass the same `WithTablePrefix` option to `Migrate`, `RenderMigrations` and `New`. A prefix must match `[A-Za-z_][A-Za-z0-9_]*`.

## Expiry sweeps

Expired rows are not removed on read. Call `SweepExpired` from a host ticker:

```go theme={"dark"}
go func() {
    for range time.Tick(15 * time.Minute) {
        if _, err := store.SweepExpired(ctx, time.Now()); err != nil {
            slog.Error("auth sweep failed", "err", err)
        }
    }
}()
```

It deletes sessions, magic links and password reset tokens whose expiry has passed, in one transaction, and reports the counts.

## Importing existing data in one transaction

`sqlite.NewTx(tx)` binds a Store to a transaction you own, so a backfill of legacy users, API tokens, passkeys and TOTP secrets commits or rolls back as one unit. This works even when your `*sql.DB` has `SetMaxOpenConns(1)`, because the Store never asks the pool for a second connection. Methods that need several statements use SAVEPOINTs inside your transaction instead of starting their own.

```go theme={"dark"}
if err := sqlite.Migrate(ctx, db); err != nil { // before the transaction
    return err
}
tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return err
}
defer tx.Rollback() // no-op after Commit

st, err := sqlite.NewTx(tx)
if err != nil {
    return err
}
u, err := theauth.ImportUserTo(ctx, st, theauth.ImportedUser{
    Email: "ada@example.com", PasswordHash: legacyArgon2idHash, CreatedAt: legacyCreated,
})
if err != nil && !errors.Is(err, theauth.ErrImportDuplicate) {
    return err
}
_, err = theauth.ImportAPITokenTo(ctx, st, theauth.ImportedToken{
    OwnerID: u.ID, Name: "ci", Abilities: []string{"read"}, TokenHash: sha256OfRawToken[:],
})
if err == nil {
    err = theauth.ImportTOTPSecretTo(ctx, st, encryptionKey, theauth.ImportedTOTP{
        UserID: u.ID, Secret: "JBSWY3DPEHPK3PXP",
    })
}
if err == nil {
    _, err = theauth.ImportWebAuthnCredentialTo(ctx, st, theauth.ImportedWebAuthnCredential{
        UserID: u.ID, CredentialID: credID, PublicKey: cosePub, SignCount: 7, Transports: []string{"internal"},
    })
}
if err != nil {
    return err
}
return tx.Commit()
```

Rules for the pattern:

* Build a short-lived Store with `NewTx` and use the package-level `Import*To` helpers. Do not build a `TheAuth` on it: a `TheAuth` outlives the transaction. `(*TheAuth).ImportUser`, `ImportTOTPSecret`, `ImportWebAuthnCredential` and `ImportAPIToken` exist for the case where you import through a live instance.
* `ImportTOTPSecretTo` takes the plaintext base32 secret and encrypts it with the same key as enrollment, so pass `Config.EncryptionKey`. The secret is never logged.
* Recovery codes are not importable. Their hashes are salted by the library, so imported users must regenerate them.
* Password hashes are stored verbatim. Bcrypt hashes verify only when `PasswordPolicy.AllowLegacyBcrypt` is set.
* Every helper validates input and returns `ErrImportInvalid` (wrapped) for bad data and `ErrImportDuplicate` when the record exists, so a re-run can skip what is already imported.
* `Migrate` takes a `*sql.DB` and cannot run inside a transaction. `SweepExpired` works on a transaction-bound Store but belongs on a normal Store after the commit. Stop using the Store once the transaction ends.

## Shared login throttle

`store.ThrottleStore()` returns a `LoginThrottleStore` backed by the same database, so several processes share failure counters. It also implements `LoginThrottleCASStore`: the limiter re-reads and retries when another process changed an entry first, instead of overwriting it, so no failure is lost. Entries are not removed on read; call `ThrottleStore().SweepExpired(ctx, time.Now())` from the same ticker as `SweepExpired`.

```go theme={"dark"}
Config.LoginThrottle = &theauth.LoginThrottleConfig{Store: store.ThrottleStore()}
```

## Upgrading

Migrations `0006` to `0008` add session columns and tables (`last_seen_at`, `elevated_until`, `credential_id`, session links), API tokens, device codes, the TOTP step table and the throttle table. Existing sessions read back with a zero `LastSeenAt`. Run `Migrate` (or fold the new files into your migrator) before deploying.

## Behavior notes

* Email uniqueness is case-insensitive (`COLLATE NOCASE`, ASCII folding). A duplicate returns the driver's constraint error wrapped with context.
* IDs are stored as 26 character ULID text, timestamps as UTC unix microseconds.
* Consuming a magic link, reset token or session link, and claiming a device code, is one `UPDATE ... RETURNING`, so concurrent consumers produce exactly one winner. `AdvanceTOTPStep` is one conditional upsert.
* Audit rows carry no foreign keys: the log is append-only and survives deletion of the user it describes.
* `UpdateWebAuthnSignCount` returns `ErrReplayDetected` for a non-increasing count.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.