Package reference
Mirrors the package README (single source). Install @basaltkit/audit-sqlite v2.1.0 — npm · source.
<p align="center"> <a href="https://basaltkit-docs.pages.dev"> <img src="https://basaltkit-docs.pages.dev/social-card.png" alt="Basalt" width="440"> </a> </p>
@basaltkit/audit-sqlite
Durable, SQLite-backed implementation of the @basaltkit/auditAuditStore — the append-only audit trail — built on Node's built-in node:sqlite. Zero external dependencies.
Swap it in for the in-memory store and the trail survives a restart — no ORM, no migration tool, no service. The single-node reference backend; the production (Postgres/MySQL) counterpart is @basaltkit/audit-prisma.
pnpm add @basaltkit/audit-sqlite # peer: @basaltkit/audit> Requires Node 22.5+. Stable and flag-free on Node 24; on 22.x run with > --experimental-sqlite.
Use it
import { auditPlugin } from '@basaltkit/audit'
import { sqliteAuditStore } from '@basaltkit/audit-sqlite'
const a = sqliteAuditStore('./data/audit.db') // ':memory:' by default
const app = await createApp({
plugins: [auditPlugin({ store: a.store })],
}).boot()SqliteAuditStore is also exported and takes a DatabaseSync, so it can share a handle with the other *-sqlite stores. openAuditDatabase() and migrate() are exported too.
Verifiable trail and request context
The store supports everything @basaltkit/audit can record:
auditPlugin({ store: a.store, integrity: 'hash-chain', requestContext: true })- Hash chain —
seq,prev_hash,hashand achainkey ('t:<tenantId>'or'@system') are stored per row, with a unique index on(chain, seq): two processes appending to the same file cannot fork a chain — the loser getsAuditChainConflictErrorandAuditretries on the new head.audit.verify()/basalt audit:verifyread the chain back inseqorder. Thehashcolumn stores the self-describing hash as written (v2:hmac-sha256:<keyId>:<hex>), so key ids and key rotation need no schema change. - Request context —
ipanduser_agentcolumns. - Automatic migration —
migrate()(run byopenAuditDatabase()/sqliteAuditStore()) adds the new columns and the index to an existing database withALTER TABLE. Rows written before keep NULLs and are reported byverify()as unchained (legacy), never as broken. - Rows outside the chain —
readUnchained()finds every row of a tenant that is not in its chain: a row withchain IS NULLbut aseq, or achainname that is not the tenant's, is always reported byverify()(unverified,ok: false); a seq-less row only when it was written after the chain began.trail({ chainedOnly: true })leaves them out, andverifyAll()reports a chain name that maps to no tenant asunknown-chain.verifyAll()also visits tenants whose rows are all outside a chain, found with oneSELECT DISTINCT tenant_id(auditTenants()) rather than a read of the whole trail. - SQLite has no roles to
REVOKE UPDATE, DELETEfrom: protect the database file with filesystem permissions (only the app user can write it) and back it up; for a keyed chain seeintegrity: { mode: 'hash-chain', key }in the@basaltkit/auditREADME.
Erasing personal data
audit.redact() / audit.systemRedact() (see the @basaltkit/audit README) work on this store as they are:
migrate()adds three nullable columns:redaction(the erased set, as stable JSON),redacted_by(the id of theaudit:redactedattestation, also the optimistic-concurrency token) andnonce(the per-entry secret of a v3 hash, written withintegrity: { …, erasable: true }and cleared by a redaction). Existing rows keep NULLs.get(id)reads one row;redact(write)runs in oneBEGIN IMMEDIATEtransaction — the row is updated only while itshashandredacted_byare still the ones the redaction was computed from (AuditRedactionConflictErrorotherwise), and the attestation is inserted under the(chain, seq)index (AuditChainConflictError). Any error rolls both back.
Optional guard triggers
SQLite has no roles, so nothing stops a buggy UPDATE or DELETE on the table. These triggers do: they refuse every DELETE, and every UPDATE except an attested redaction (header and hash columns unchanged, redacted_by set, a nonce never restored). They guard against bugs, not attackers — whoever can open the file can drop them. Install them once, after migrate() (db.exec(sql)); do not install them if your retention policy deletes old rows.
<!-- audit-sqlite:guard-triggers -->
CREATE TRIGGER IF NOT EXISTS audit_entries_no_delete
BEFORE DELETE ON audit_entries
BEGIN
SELECT RAISE(ABORT, 'audit_entries is append-only');
END;
CREATE TRIGGER IF NOT EXISTS audit_entries_redaction_only
BEFORE UPDATE ON audit_entries
WHEN NEW.id IS NOT OLD.id OR NEW.source IS NOT OLD.source OR NEW.event IS NOT OLD.event
OR NEW.actor_id IS NOT OLD.actor_id OR NEW.tenant_id IS NOT OLD.tenant_id
OR NEW.request_id IS NOT OLD.request_id OR NEW.at IS NOT OLD.at
OR NEW.chain IS NOT OLD.chain OR NEW.seq IS NOT OLD.seq
OR NEW.prev_hash IS NOT OLD.prev_hash OR NEW.hash IS NOT OLD.hash
OR NEW.redacted_by IS NULL OR (OLD.nonce IS NULL AND NEW.nonce IS NOT NULL)
BEGIN
SELECT RAISE(ABORT, 'audit_entries: only an attested redaction may update a row');
END;<!-- /audit-sqlite:guard-triggers -->
Notes
- Append-only by contract — one
audit_entriestable, no delete; the only update is the attested erasure above. - Queries return newest-first with the same filters as the in-memory store:
tenantId,actorId,since,chainedOnly, and the event wildcard (auth:**).limitalways counts only pattern-matched rows. Every filter is type-checked (assertAuditQuery) even when the store is called directly: a non-stringtenantId/actorId/eventor a non-integerlimitthrows aTypeError. - The
payloadis stored as JSON text and round-trips unchanged. node:sqliteis synchronous; the methods stayasyncto honor the contract.- Query pushdown. Every exact filter — tenant, actor,
since, and an event name with no wildcard — plus thelimitgo into the database (take/LIMIT). Only a wildcard pattern still needs matching in code, and then rows are read in bounded 500-row pages that stop as soon as the limit is satisfied, so alimit: 50query never materialises the whole trail. A pattern containing.is deliberately not pushed down:patternMatchestreats.and:as interchangeable separators, so an equality would missa:bfora.b.
License
MIT