NAME
Langertha::Skeid::UsageStore::DBI - SQLite and PostgreSQL usage store
VERSION
version 0.004
DESCRIPTION
Usage events in a real database — SQLite for a single box, PostgreSQL for a deployment. Both speak the same usage_events table, shipped as share/sql/usage_events.<backend>.sql.
By default every event costs a synchronous database round-trip -- and a commit -- on the request path, which is why Langertha::Skeid::UsageStore::JsonLog is the recommended default and this backend is a deliberate choice. See docs/adr/0004-usage-events-are-the-billing-unit.md.
flush_interval_ms turns on write-behind: while the event loop runs, "store" only queues the event and answers at once, and a Mojo::IOLoop timer writes the queue in one transaction. The request is answered without waiting for the database, and a burst of events costs one commit instead of one each. The flush itself is still a synchronous database call on the loop -- it runs once per interval instead of once per request, it is not off the loop. The price is a wider loss window: events still queued when the process dies without a flush are lost (ADR 0005, Update skeid k78). Off by default.
spool_dir keeps the events the database cannot take while it is down: they are appended to a local file instead of being reported lost, and written once the database answers again (see "spool_dir" and docs/adr/0027-usage-events-spool-while-the-database-is-down.md). With or without it, a failed connect is not repeated for every event: the next attempt waits for a backoff deadline ("Reconnect backoff"). Off by default.
DBI is loaded at runtime rather than compile time: a Skeid that never configures a database backend must not require one to be installed.
Reconnect backoff
After a connect fails, the store does not connect again before a deadline: 1 second after the first failure, doubling with every further failure up to 60 seconds. Until then every write fails at once with usage database unreachable (next connect attempt in ...) and the last connect error, without touching the network -- a database host that does not answer costs one blocking connect per deadline, not one per event or per flush (ADR 0005). The first connect that succeeds clears the backoff. The events of that window go to the spool when "spool_dir" is set, else to "on_lost" (or the caller of a synchronous "store") as before.
backend
sqlite or postgresql. Decides the schema file and whether an insert can report a row id.
dsn
Required. The DBI data source, as normalized by "normalize_config" in Langertha::Skeid::UsageStore (dbi:SQLite:dbname=... or dbi:Pg:...).
user
Database user (default empty).
password
Database password (default empty). Normalization reads it from password_env when the config names that instead; it is never written anywhere.
path
SQLite database file. Its parent directory is created on connect. Empty for PostgreSQL.
schema_file
Explicit schema path. Empty means "find the shipped one for this backend".
auto_migrate
Apply the schema when the store is prepared. On by default.
flush_interval_ms
Write-behind interval in milliseconds; 0 (the default) writes every event synchronously when "store" is called. Above 0, "store" queues the event while Mojo::IOLoop is running, and the first queued event arms a timer that calls "flush" after this many milliseconds. With no running loop there is nothing to protect and nothing to fire the timer, so "store" writes at once, after anything still queued.
on_lost
Optional code ref, called as ->($event, $error) for every queued event a "flush" could not write -- the request it describes was answered long ago, so there is no caller left to hand the failure to. Langertha::Skeid sets it to its own lost-event report. Without it the store warns one line naming the request id and the backend, never the DSN.
spool_dir
Directory for the usage spool; empty (the default) turns it off and leaves every failure to "on_lost" as before. Set, an event the store cannot write because the database is unreachable -- the connect failed, the handle died and the one reconnect failed, or the reconnect backoff is running -- is appended to a file in this directory instead of being reported lost: JSON Lines, one event per line, flushed and fsync'd before the write counts. A synchronous "store" then answers { ok => 1, spooled => 1 }, a "flush" counts it in spooled. An event the database refuses on a live connection (a constraint, a dropped table) is not spooled -- it would be refused again on every replay -- and stays an "on_lost" report. A spool write that fails reports the event lost, with both errors. The first event spooled after a connect warns once that the database is unreachable and where the events go.
A spool line holds the event's columns and nothing else: key ids, never a key, never the DSN or the password. Each process appends to its own file, usage-spool-<host>-<pid>.jsonl, under an exclusive flock, so the workers of one Skeid can share the directory. Several Skeid instances should each have their own; the spool is per instance.
The spool is replayed once the database takes writes again: after a write that succeeded ("store", "flush") and when the store is prepared, so a restart picks up what an earlier process left behind. A replay claims a file by renaming it, writes at most 500 of its events in one transaction and removes those lines only after the commit; the rest follows on the next loop tick (or with the next write when no loop runs). Replay is idempotent: an event whose request_id and created_at are already in the table is skipped, so a replay that is interrupted and repeated, or an event that did make it in before its connection dropped, never adds a second row. An event without a request_id cannot be recognized and is written again by a repeated replay -- at least once rather than lost. A line that does not parse is dropped with a warning; an event the database refuses on a live connection during the replay is reported to "on_lost" and dropped, the rest of its batch is written. Nothing replays while the database is unreachable, and a replay that fails half way leaves the spool file as it was (what it did commit is skipped next time).
A store prepared while the database is down does not die when it has a spool: it warns, starts with the backoff running and applies the schema on its first successful connect.
prepare
Connects if possible and applies the schema when auto_migrate is on: its CREATE TABLE statements, then any column an older table lacks (ALTER TABLE ... ADD COLUMN; nothing is ever dropped or rewritten), then the rest. Returns false without complaint when DBI is unavailable -- an unusable store degrades to "no usage recorded", it does not take the proxy down. Croaks when the schema file does not exist; a failing connect or statement dies -- except a failing connect of a store with a "spool_dir", which warns and returns true: its events go to the spool and the schema is applied on the first connect that succeeds. With a spool, what an earlier process left in it starts to be replayed here.
dbh
The cached database handle, connecting on first use. Returns nothing when DBI is not installed or no DSN is configured. Dies when the connect fails, and -- without trying -- while the "Reconnect backoff" a failed connect set is running.
disconnect
Writes what is still queued ("flush"), then drops the cached handle, so the next "dbh" connects anew. Called when the store is replaced on a reload -- the queued events belong to this store's destination, not the next one's -- and from Skeid's DEMOLISH.
shipped_schema_file
The schema file this backend would apply when schema_file is not set. Looks in the installed sharedir first, then walks up from this file for the repository's share/sql/ — the dev and dzil test case, where nothing is installed yet. Returns the first candidate that exists, or the first candidate at all, so the caller can report a useful path when none does.
store
my $res = $store->store($event);Inserts one event. Returns { ok => 1 } (plus id on SQLite), or { ok => 0, error => … } when there is no database handle or the insert fails -- a failure is reported, never thrown, because the request it describes has already been served. The proxy logs such an answer at error level as a lost usage event. A password= in the error text (a DSN that carries one) is masked.
With "flush_interval_ms" set and the event loop running, the event is queued instead and the answer is { ok => 1, queued => 1 }, without an id: the row does not exist yet. What becomes of it is "flush"'s to report.
A dropped connection is survived: when the insert fails and the handle turns out to be dead (not Active, or ping fails -- checked only after a failure, so a healthy write costs nothing extra), the store connects once more and retries that one event once. The new handle serves every later event. A reconnect that fails is reported like any other failure (the next event tries again once the "Reconnect backoff" has run out), and a failure on a live handle -- a dropped table, a constraint -- is reported without a reconnect. There is no loop and no wait: at most one extra synchronous connect per event, and while the database stays away at most one per backoff deadline, the cost ADR 0005 already accepts for this backend. With a "spool_dir", an event that could not be written for want of a connection is spooled and the answer is { ok => 1, spooled => 1 }; a write that succeeds starts a replay of the spool. The schema is not re-applied on a reconnect; "prepare" ran when the store was configured, and a lost connection does not lose the table. One corner remains: a connection that drops after the server committed the insert but before it answered is indistinguishable from one that dropped before, so that event can be written twice. A duplicate row carries the same request_id; a lost one leaves nothing to reconcile.
flush
my $res = $store->flush;Writes every queued event in one transaction and empties the queue. Returns { ok => 1, written => $n }, or { ok => 0, written => $n, lost => $m, error => … } when some could not be written; each of those is also handed to "on_lost". With a "spool_dir", events that went to the spool are counted in spooled and are not lost. Never dies, never waits, never retries later: every queued event gets the one attempt a synchronous "store" would have given it -- the spool's replay is a write of its own.
A failing batch is rolled back, so nothing is half written. When the handle survived the failure -- one event a constraint rejects, a dropped table -- the events are written one by one, so a single bad event loses only itself. When the handle died, it is reconnected once and the batch retried once, as for "store"; a reconnect that fails loses (or spools) the whole batch with one connect attempt, not one per event, and while the "Reconnect backoff" runs a flush makes no connect attempt at all. The duplicate corner of "store" applies to a batch: a connection that drops after the server committed but before it answered is retried as a whole. A flush that wrote something starts a replay of the spool.
Called by the flush timer, by "disconnect", by "report" (so a report counts what was queued) and by "store" when it writes synchronously. "flush_usage" in Langertha::Skeid calls it.
replay_spool
my $res = $store->replay_spool;Writes up to one batch (500 events) of the "spool_dir" to the database and removes what was committed. Returns { ok => 1, replayed => $n, consumed => $m, remaining => 0|1 } -- replayed rows written, consumed spool lines done with (written, already present, or dropped), remaining whether spool files are left -- or the same with ok => 0 and an error when the database failed; the spool is then left as it was. Never dies. The store calls it itself (see "spool_dir"); it is public for an operator's script and for tests.
report
my $report = $store->report(\%filters);Aggregates in SQL: totals, per-key and per-model breakdowns (tokens, cost, the audio_seconds the audio routes recorded, the documents of the rerank and documents routes and the pages of the documents routes), and the newest limit events. The same since / api_key_id / model filter set applies to every part of the report, so the breakdowns always add up to the totals shown next to them. limit defaults to 20. The shape is described in "The store contract" in Langertha::Skeid::UsageStore; a failure to connect or a failing query is { ok => 0, enabled => 0, error => … }. A dropped connection is reconnected once, exactly as for "store". Queued events are flushed first, so a report counts every event recorded before it was asked for.
SEE ALSO
Langertha::Skeid::UsageStore, Langertha::Skeid::UsageStore::JsonLog
SUPPORT
Issues
Please report bugs and feature requests on GitHub at https://github.com/Getty/langertha-skeid/issues.
IRC
Join #langertha on irc.perl.org or message Getty directly.
CONTRIBUTING
Contributions are welcome! Please fork the repository and submit a pull request.
AUTHOR
Torsten Raudssus <torsten@raudssus.de> https://raudssus.de/
COPYRIGHT AND LICENSE
This software is copyright (c) 2026 by Torsten Raudssus.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.