NAME
Langertha::Skeid::UsageStore::DBI - SQLite and PostgreSQL usage store
VERSION
version 0.003
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.
DBI is loaded at runtime rather than compile time: a Skeid that never configures a database backend must not require one to be installed.
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.
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.
dbh
The cached database handle, connecting on first use. Returns nothing when DBI is not installed or no DSN is configured.
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), 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, the cost ADR 0005 already accepts for this backend. 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". Never dies, never waits, never retries later: every queued event gets the one attempt a synchronous "store" would have given it.
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 the whole batch with one connect attempt, not one per event. 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.
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.
report
my $report = $store->report(\%filters);Aggregates in SQL: totals, per-key and per-model breakdowns, 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.