when items are written to the in-memory store, they are written into sqlite files.
on startup, the sqlite files are read into memory. no reads happen during normal operation.
the persistent state should be abstracted away entirely so things storing or querying don't know anything about underlying storage or LRU or anything. it should hide the in-memory live cache, as well as the full-read from disk on instantiation (which should happen once at startup).
when items are written to the in-memory store, they are written into sqlite files.
on startup, the sqlite files are read into memory. no reads happen during normal operation.
the persistent state should be abstracted away entirely so things storing or querying don't know anything about underlying storage or LRU or anything. it should hide the in-memory live cache, as well as the full-read from disk on instantiation (which should happen once at startup).
Question: does SQLite replace the zstd-compressed JSON files you asked for on #202 (comment), four minutes earlier?
Yes, SQLite replaces them (recommended): the state is written to SQLite files, and there is no JSON and no zstd step. At the table sizes proposed on 202, about 100,000 bans and 100,000 clients, the state is a few hundred MB. SQLite then writes only the rows that changed, not whole files every 15 minutes.
Both: SQLite files, zstd-compressed at rest.
Picked up for smallwebwaf's manager once you answer: one storage layer that hides both the in-memory copy and the database. It reads everything once at start and writes each change through, and nothing else knows how state is stored.
Model: opus-5-5
**Question:** does SQLite replace the zstd-compressed JSON files you asked for on https://git.eeqj.de/sneak/smallwebwaf/issues/202#issuecomment-170241, four minutes earlier?
- **Yes, SQLite replaces them** (recommended): the state is written to SQLite files, and there is no JSON and no zstd step. At the table sizes proposed on 202, about 100,000 bans and 100,000 clients, the state is a few hundred MB. SQLite then writes only the rows that changed, not whole files every 15 minutes.
- **Both:** SQLite files, zstd-compressed at rest.
Picked up for smallwebwaf's manager once you answer: one storage layer that hides both the in-memory copy and the database. It reads everything once at start and writes each change through, and nothing else knows how state is stored.
Model: opus-5-5
sneak
was assigned by clawbot2026-11-07 03:52:39 +01:00
Recorded: SQLite replaces the zstd-compressed JSON (sneak, #217 (comment)). There is no JSON state and no zstd step. Picked up for smallwebwaf's manager at its next run.
Model: opus-5-5
Recorded: SQLite replaces the zstd-compressed JSON (sneak, https://git.eeqj.de/sneak/smallwebwaf/issues/217#issuecomment-170347). There is no JSON state and no zstd step. Picked up for smallwebwaf's manager at its next run.
Model: opus-5-5
sneak
was unassigned by clawbot2026-11-07 05:21:37 +01:00
clawbot
self-assigned this 2026-11-07 05:21:37 +01:00
For the spec writer (an Opus run, by sneak's order #217 (comment)): write the detailed spec for this change as one comment on this issue, for sneak's approval. Nobody implements it before he approves. Write no code and open no PR.
Read the code on next first. The spec covers:
Every table's schema: the CREATE TABLE and index statements, with each column's type, constraints and meaning. Cover every kind of state the discriminator keeps today: bans, client records, and anything else it holds in memory or writes to disk.
The files: how many SQLite files, their names and location, and the settings that set them.
The exported API: the package's name, and every exported type, function and method with its Go signature and a one-line doc. Show how callers store and query through it. Nothing in it shows the database, the in-memory copy or the LRU.
Behaviour:
the one full read at start;
each change written through: batching, transactions, WAL;
eviction and the LRU, and what leaves the disk when;
schema versioning;
what happens when a file is corrupt, missing or unwritable.
The driver:modernc.org/sqlite, pure Go with no CGO, as sneak's Go package defaults require.
What goes away: today's state code and files, and the change each caller needs.
Tests: the ones the implementation will carry.
Model: opus-5-5
**For the spec writer** (an Opus run, by sneak's order https://git.eeqj.de/sneak/smallwebwaf/issues/217#issuecomment-170436): write the detailed spec for this change as one comment on this issue, for sneak's approval. Nobody implements it before he approves. Write no code and open no PR.
Read the code on `next` first. The spec covers:
- **Every table's schema:** the `CREATE TABLE` and index statements, with each column's type, constraints and meaning. Cover every kind of state the discriminator keeps today: bans, client records, and anything else it holds in memory or writes to disk.
- **The files:** how many SQLite files, their names and location, and the settings that set them.
- **The exported API:** the package's name, and every exported type, function and method with its Go signature and a one-line doc. Show how callers store and query through it. Nothing in it shows the database, the in-memory copy or the LRU.
- **Behaviour:**
- the one full read at start;
- each change written through: batching, transactions, WAL;
- eviction and the LRU, and what leaves the disk when;
- schema versioning;
- what happens when a file is corrupt, missing or unwritable.
- **The driver:** `modernc.org/sqlite`, pure Go with no CGO, as sneak's Go package defaults require.
- **What goes away:** today's state code and files, and the change each caller needs.
- **Tests:** the ones the implementation will carry.
Model: opus-5-5
Spec: state in SQLite, behind one store (for sneak's approval; nothing is built before he approves)
All state lives in one SQLite file, sww.db, in SWW_STATE_DIR.
A new package, internal/store, holds every kind of state in memory. Each table is read whole from sww.db once, at start, and never read again while discriminator is running.
Every change goes into memory at once and into sww.db within 10 seconds, in one transaction per write.
The parts that keep state (bans, ratelimit, lookup, reputation, alerts, anomaly) only get, put and delete values. They know nothing of the file, the memory copy, or how a full table drops values.
internal/state, the five JSON files, their hand edits while running, and two settings go away.
Read from next at beb503b.
Packages
internal/store: the API every part uses (below).
internal/db: opens sww.db, applies the schema, reads a table's rows, writes changes. Only internal/store imports it. It exists because REPO_POLICIES.md puts the schema files in internal/db/migrations/, embedded in the binary.
Driver: modernc.org/sqlite, pure Go, no CGO, through database/sql, with one open connection.
The file
One file: SWW_STATE_DIR/sww.db, with SQLite's sww.db-wal and sww.db-shm beside it while it is open.
Settings on open: journal_mode=WAL, synchronous=NORMAL. With these a crash or hard kill never damages the file; it loses at most the last write.
db.Open creates sww.db with mode 0600 before SQLite opens it, since it holds visitors' addresses. SQLite gives the -wal and -shm files the same mode.
SWW_STATE_DIR stays, default /var/lib/sww. The image's entrypoint already gives that directory to the sww user.
Schema
internal/db/migrations/000_migration.sql (only the tracking table, as REPO_POLICIES.md requires):
CREATETABLEIFNOTEXISTSmigrations(versionINTEGERPRIMARYKEY,-- the file's number: 1 for 001_schema.sql
applied_atTEXTNOTNULL-- when it was applied, RFC 3339 UTC
)STRICT;
internal/db/migrations/001_schema.sql. Every table has the same three columns:
key: what the row is about, such as a client's netblock. Each table's key is listed below.
value: the state, as JSON.
write_order: the table's count of puts when this value was last put. Rows are read back in this order, so a full table drops the same value after a restart as before it.
No other index: the only statements are the read at start, the upsert by key, and the delete by key.
Each table
The value is the part's existing Go type, encoded with its existing JSON tags. Those are the fields docs/state-files.md lists today. A key of several fields is the JSON array of them, which keeps it unambiguous and readable.
bans, kept by bans.Ledger.
Key: the netblock, such as 203.0.113.0/24.
Value: {"netblock", "last_request", "bans": [...]}: the netblock's bans oldest first, each with today's ban fields and notes, and when the netblock last sent a request.
No limit in the table. The ledger drops bans itself, as SPEC "Bans" says: past SWW_MAX_BANS of its own, it drops the earliest such ban of the netblock with the earliest last_request. Admin bans are never dropped and not counted.
clients, kept by ratelimit.Limiter.
Key: the client, 192.0.2.1/32 or an IPv6 group such as 2001:db8::/64.
Value: ratelimit.Client, as one line of clients.json holds it today.
At most SWW_MAX_TRACKED_CLIENTS.
geojs_answers, kept by lookup.GeoJS.
Key: the client. Value: lookup.Answer. At most 100,000.
lists, kept by reputation.Lists.
Key: the list's URL. Value: reputation.List (url, tried, fetched, lines).
One row per URL the settings name. Rows for a URL no setting names are deleted at start.
dnsbl_verdicts, kept by reputation.DNSBL.
Key: ["zen.spamhaus.org","192.0.2.1"], the zone and the address. Value: reputation.Verdict. At most 100,000.
Rows for a zone SWW_DNSBL_ZONES does not name are deleted at start.
abuseipdb_scores, kept by reputation.AbuseIPDB.
Key: the client. Value: reputation.Score. At most 100,000.
abuseipdb_budget, kept by reputation.AbuseIPDB.
One row, key today. Value: {"day", "spent"}, the checks spent on that UTC day.
alert_cooldowns, kept by alerts.Queue.
Key: the JSON array of event, netblock, file, source, scope, AS number and name. Value: alerts.Cooldown.
No limit: the end of each hour deletes the cooldowns that have run out, as today.
alert_hour, kept by alerts.Queue.
One row, key current. Value: alerts.Hour.
alerts_waiting, kept by alerts.Queue.
Key: the destination, webhook, slack or ntfy. Value: the alerts waiting for it, oldest first, at most 1,000 as today.
Rows for a destination that is not set are deleted at start.
anomaly_counters, kept by anomaly.Counters.
Key: the JSON array of scope, netblock, AS number and name. Value: anomaly.Counter. At most 20,000.
An answer, verdict or score found past its time is deleted when it is found. Otherwise, at start each value is used as it was written: the counters' buckets already start afresh when their time has passed.
Exported API
packagestore// Store holds sww's state in memory and writes each change to sww.db.typeStorestruct{/* unexported */}// Open opens sww.db in dir (SWW_STATE_DIR), creating it on a first start.// A file or directory that cannot be written, or a file that is not an// SQLite database, is an error naming the file.funcOpen(dirstring)(*Store,error)// Run writes the changes made since the last write, every 10 seconds,// until ctx is done. After each write it calls written with that write's// error, nil when it succeeded. A failed write's changes are written next time.func(s*Store)Run(ctxcontext.Context,writtenfunc(errerror))// Close writes the changes not yet written and closes sww.db. Nothing// uses the store or its tables after Close.func(s*Store)Close()error// Table is one kind of state: values of type V by key. It is safe for// concurrent use.typeTable[Vany]struct{/* unexported */}// NewTable returns the table name, holding what sww.db holds for it, read// now. With limit above 0 it holds at most limit values: past that, the value// put longest ago is dropped. A row that cannot be read into a V is an error// naming the table and the key.funcNewTable[Vany](s*Store,namestring,limitint)(*Table[V],error)// Get returns the value under key, and whether there is one. A slice or map// in it is shared with the table: change a copy, then Put it.func(t*Table[V])Get(keystring)(V,bool)// Put sets the value under key.func(t*Table[V])Put(keystring,valueV)// Delete removes the value under key, if there is one.func(t*Table[V])Delete(keystring)// Len returns how many values the table holds.func(t*Table[V])Len()int// All yields every key and value, the value put longest ago first. The loop// must not Put or Delete in the same table.func(t*Table[V])All()iter.Seq2[string,V]
packagedb// DB is sww.db. Only internal/store uses it.typeDBstruct{/* unexported */}// Change is one row to write: Value under Key in Table, or, with Delete// set, the row's removal.typeChangestruct{TablestringKeystringValue[]byteWriteOrderint64Deletebool}// Open opens the SQLite file at path, creating it with mode 0600 when it is// missing, and applies, in one write transaction, the migrations not yet applied.funcOpen(pathstring)(*DB,error)// Read calls row for each row of table, lowest write order first, and// returns row's first error.func(d*DB)Read(tablestring,rowfunc(keystring,value[]byte,writeOrderint64)error)error// Write writes changes in one transaction: all of them or none.func(d*DB)Write(changes[]Change)error// Close closes sww.db.func(d*DB)Close()error
Table names are constants in the parts, never input, since SQL cannot take a table name as a parameter.
How a part uses a table, here ratelimit counting a request under the Limiter's own lock:
// in ratelimit.Newclients,err:=store.NewTable[Client](s,"clients",maxClients)// counting a requestkey:=client.String()c,_:=l.clients.Get(key)c.Client=clientc.Minute.Add(now,time.Minute,1)l.clients.Put(key,c)
Each table belongs to one part. The part's own lock makes a get, change and put one step. The table's lock only keeps the table safe while the store writes it.
Behaviour
The read at start
sww discriminator calls store.Open, then builds the parts. Each part's constructor calls NewTable for its tables, and each table reads its rows once, oldest write_order first.
A part works out what it derives from its rows once, at start: the ledger its count of bans and the netblock lengths banned, the lists the netblocks in each copy.
Rows beyond a table's limit, after the limit was lowered, are dropped oldest first, and deleted at the first write.
Serving starts after every table is read. Nothing reads sww.db again until the next start.
Changes written
Put and Delete change memory at once and note the key. No request waits for the disk.
Every 10 seconds the store writes every noted key in one transaction: an upsert (INSERT ... ON CONFLICT(key) DO UPDATE) of the current value, or a DELETE for a value deleted or dropped. A key changed many times in those 10 seconds is written once.
Values are encoded to JSON outside the table's lock.
Close writes what is left. An orderly stop loses nothing; a hard kill loses at most the last 10 seconds.
The 10 seconds is a constant in internal/store, not a setting. It replaces SWW_STATE_WRITE_DELAY (10 seconds for bans) and SWW_STATE_COUNTER_INTERVAL (15 minutes for everything else).
Limits and dropping
A table made with a limit drops the value put longest ago once it holds more. The value leaves memory at once and sww.db at the next write.
Get does not change which value is dropped; only Put does. Every part already puts a client, counter or answer each time it is used, so the order matches today's.
The limits are today's: SWW_MAX_TRACKED_CLIENTS clients, 100,000 GeoJS answers, 100,000 DNSBL verdicts, 100,000 AbuseIPDB scores, 20,000 anomaly counters, at most 1,000 alerts waiting per destination. hashicorp/golang-lru/v2 moves from the parts into the store.
Schema versions
Before 1.0, 001_schema.sql is edited in place, and no 002 is added, as REPO_POLICIES.md says. db.Open applies each file not yet in migrations, in file order.
The runner is the few lines that the policy's layout implies, with no library. Goose and golang-migrate each create their own tracking table, which the policy's 000_migration.sql does instead.
A file that is missing, damaged or cannot be written
Missing: db.Open creates it with the schema. That is a first start.
Not a database, or damaged so that the read at start fails: discriminator stops with exit status 1 and an error naming the file, as a broken state file does today. Starting empty would forgive every repeat offender. To start afresh, an admin removes sww.db, sww.db-wal and sww.db-shm.
A row whose value does not decode: the same, naming the table and the key.
Cannot be written at start: db.Open always takes the write lock (BEGIN IMMEDIATE) to apply the migrations, so an unwritable file or directory stops the start with the error.
A write that fails while running (disk full, permissions, damage): the transaction rolls back and the keys stay noted. written gets the error. The process log has it, the failure metric counts it, and a file_error alert names sww.db, with today's cooldown per file. The next write retries. The state in memory stays correct, and requests are served as before. Meanwhile memory grows by the keys changed or dropped.
Removing sww.db while discriminator is running goes unnoticed: the state is lost at the next start.
To change state by hand: stop discriminator, edit sww.db with any SQLite tool, start it. An edit made while it is running is not read, and a later change to the same row overwrites it.
The catch: disk writes
SQLite writes only the values that changed. But the counters change with nearly every request, so on a busy site this writes more than today, not less.
Each value changed between two writes costs about 8 KiB of disk writes: its 4 KiB page, once in the WAL and once in the file.
A request changes about three values: its client, its GeoJS answer and its anomaly counters.
With 100 clients sending requests in each 10 seconds, that is about 20 GiB a day. With 10, about 2 GiB.
Today the whole files are written every 15 minutes, about 2.5 GiB a day for bans and clients at the defaults, whatever the traffic.
The CrowdSec decision list is fetched every minute, and its copy changes each time, so its row is rewritten every minute. A 2 MiB list costs about 6 GiB a day, against about 0.2 GiB today.
The lever is the 10 seconds. A longer time lowers the writes and raises what a hard kill loses.
What goes away
internal/state and its tests: the five JSON files, Load, Run, WriteAll, Watch, taking in edits, .bad files, the checksums, the version check.
No code reads the JSON files any more.
Hand edits while running. SPEC "Persistent state" and "Bans" designed them: an admin adds, keeps, lifts or forgets a ban by editing bans.json, and discriminator takes the edit in.
While running, adding and lifting stay, through POST /_sww/bans and DELETE /_sww/bans/....
Keeping a ban (setting its cause to admin) and forgetting one (deleting its entry) are possible only while discriminator is stopped, with a SQLite tool. Endpoints for them would be a separate issue.
Settings: SWW_STATE_WRITE_DELAY and SWW_STATE_COUNTER_INTERVAL. That settles both for the settings audit at #202.
Metrics: sww_state_file_edits_taken_in_total and sww_state_file_edits_set_aside_total go. The file label leaves the write, failure, last-write and size metrics. Size becomes the size of sww.db plus its WAL.
Offences.TokenRefused, kept only so that old clients.json files load.
The change each caller needs:
bans: New(s *store.Store, rules Rules) (*Ledger, error). The simplelru field becomes the bans table.
Every change puts the netblock's row: a ban made, lifted or made permanent, a refused request counted, a lookup added. So does each request from a netblock that has a row, which updates last_request.
dropOne scans the table for the earliest last_request among netblocks holding a ban not made by an admin.
Load, LoadEdit, Changed and markChanged go.
Snapshot stays for GET /_sww/bans.
ratelimit: New(s *store.Store, limits Limits, maxClients int) (*Limiter, error). Load and Snapshot go. Client gets, Requests and Len read the table, and counting puts.
lookup: Params.Store. New returns an error. Load and Snapshot go. Using an answer puts it with its new used time, as today.
reputation: Params.Store, DNSBLParams.Store and AbuseIPDBParams.Store. New, NewDNSBL and NewAbuseIPDB return an error. Load and Snapshot go. Fetch failure counts are not stored, as today.
alerts: Params.Store. New returns an error. Load and Snapshot go. Each destination's queue is the alerts_waiting row: add and remove get it, change a copy and put it.
anomaly: Params.Store. New returns an error. Load and Snapshot go.
discriminator: Params.Store. New returns (*Server, error). BanEntry and BanEntries, the ban endpoints' answer form, move here from state. The Server fields that only handed parts to state.Load go.
metrics: StateFileWritten(size int, err error), with no file name. The two edit methods go.
config: the two settings go.
sww:
It opens the store before the alert queue and the server; an error exits 1 with "cannot use the state".
It runs store.Run with a written that updates the metrics and logs and alerts a failure.
It calls store.Close at an orderly stop where WriteAll is today.
Docs:
docs/state-files.md is rewritten as the tables above, keeping today's field lists.
SPEC.md gets a new "Persistent state", the "State files" settings, the editing paragraph of "Bans", and the size and backup lines of "State".
README.md loses the settings rows and gets a new "State files" section.
docs/settings.md and docs/metrics.md follow, as does the GET /_sww/clients/... line in docs/admin-endpoints.md.
go.mod: adds modernc.org/sqlite. fsnotify stays, for the rule files and the lookup database.
Tests
internal/db:
Open creates sww.db with mode 0600 and every table, and records migrations 0 and 1; a second Open applies nothing.
Open fails on a read-only file, a read-only directory, and a file that is not a database, naming the file.
Write applies upserts and deletes together, and applies none when one fails.
Read yields rows lowest write order first.
internal/store:
Put, Get and Delete survive Close and a new Open.
A table at its limit drops the value put longest ago, from memory at once and from sww.db at the next write.
Get leaves the drop order alone. The drop order survives a reopen.
A lower limit at reopen drops the oldest rows.
A value got from a table is unchanged by a later Put of a new value under its key.
A row that does not decode fails NewTable, naming the table and the key.
A failed write calls written with the error, keeps its changes, and writes them at the next write. The test drops a table through a second connection, then restores it.
Each part, replacing today's snapshot_test.go files: its state survives a reopen of the store.
bans: admin bans are never dropped, and after a reopen the drop takes the netblock with the earliest last request.
reputation: a list's copy is parsed at start; rows for URLs and zones no setting names are deleted.
alerts: alerts waiting are sent after a reopen.
internal/sww:
An orderly stop and start keeps a ban, a client's counts and an alert waiting.
A state directory that cannot be written stops the start with exit status 1.
Disclosures
Rule this cuts against: state well under about 100 MiB is kept as hand-editable JSON, and SQLite for it is a review defect. At today's defaults the state is a few tens of MiB. Carried out on sneak's order on this issue.
Judgement call: one file, though the issue says "files". One transaction then covers every table, and there is one file to back up or remove. Splitting before 1.0 costs a path per table group in store.Open.
Judgement call: a key and a JSON value per row, not a column per field. Values are only written and read whole, never queried, and every part's type already has its JSON form. A column per field would add a mapping for each of eleven tables, about 40 columns for a client alone. Reversing it before 1.0 means editing 001_schema.sql and writing those mappings.
Judgement call: changes are written every 10 seconds, not as each happens, so that no request waits for the disk.
Judgement call: the bans table has no limit, since the ledger's dropping counts bans, not netblocks, and spares admin bans. At SWW_MAX_BANS 5,000 each drop scans 5,000 rows. At 100,000, if the settings audit moves it there, it scans 100,000, estimated at about a millisecond per new ban when full.
Not checked: the disk-write figures are worked out from SQLite's 4 KiB page and today's value sizes. Nothing was measured.
Model: opus-5-5
**Spec: state in SQLite, behind one store** (for sneak's approval; nothing is built before he approves)
- All state lives in one SQLite file, `sww.db`, in `SWW_STATE_DIR`.
- A new package, `internal/store`, holds every kind of state in memory. Each table is read whole from `sww.db` once, at start, and never read again while `discriminator` is running.
- Every change goes into memory at once and into `sww.db` within 10 seconds, in one transaction per write.
- The parts that keep state (`bans`, `ratelimit`, `lookup`, `reputation`, `alerts`, `anomaly`) only get, put and delete values. They know nothing of the file, the memory copy, or how a full table drops values.
- `internal/state`, the five JSON files, their hand edits while running, and two settings go away.
Read from `next` at `beb503b`.
## Packages
- `internal/store`: the API every part uses (below).
- `internal/db`: opens `sww.db`, applies the schema, reads a table's rows, writes changes. Only `internal/store` imports it. It exists because `REPO_POLICIES.md` puts the schema files in `internal/db/migrations/`, embedded in the binary.
- Driver: `modernc.org/sqlite`, pure Go, no CGO, through `database/sql`, with one open connection.
## The file
- One file: `SWW_STATE_DIR/sww.db`, with SQLite's `sww.db-wal` and `sww.db-shm` beside it while it is open.
- Settings on open: `journal_mode=WAL`, `synchronous=NORMAL`. With these a crash or hard kill never damages the file; it loses at most the last write.
- `db.Open` creates `sww.db` with mode `0600` before SQLite opens it, since it holds visitors' addresses. SQLite gives the `-wal` and `-shm` files the same mode.
- `SWW_STATE_DIR` stays, default `/var/lib/sww`. The image's entrypoint already gives that directory to the `sww` user.
## Schema
`internal/db/migrations/000_migration.sql` (only the tracking table, as `REPO_POLICIES.md` requires):
```sql
CREATE TABLE IF NOT EXISTS migrations (
version INTEGER PRIMARY KEY, -- the file's number: 1 for 001_schema.sql
applied_at TEXT NOT NULL -- when it was applied, RFC 3339 UTC
) STRICT;
```
`internal/db/migrations/001_schema.sql`. Every table has the same three columns:
- `key`: what the row is about, such as a client's netblock. Each table's key is listed below.
- `value`: the state, as JSON.
- `write_order`: the table's count of puts when this value was last put. Rows are read back in this order, so a full table drops the same value after a restart as before it.
```sql
CREATE TABLE bans (key TEXT PRIMARY KEY, value TEXT NOT NULL, write_order INTEGER NOT NULL) STRICT;
CREATE TABLE clients (key TEXT PRIMARY KEY, value TEXT NOT NULL, write_order INTEGER NOT NULL) STRICT;
CREATE TABLE geojs_answers (key TEXT PRIMARY KEY, value TEXT NOT NULL, write_order INTEGER NOT NULL) STRICT;
CREATE TABLE lists (key TEXT PRIMARY KEY, value TEXT NOT NULL, write_order INTEGER NOT NULL) STRICT;
CREATE TABLE dnsbl_verdicts (key TEXT PRIMARY KEY, value TEXT NOT NULL, write_order INTEGER NOT NULL) STRICT;
CREATE TABLE abuseipdb_scores (key TEXT PRIMARY KEY, value TEXT NOT NULL, write_order INTEGER NOT NULL) STRICT;
CREATE TABLE abuseipdb_budget (key TEXT PRIMARY KEY, value TEXT NOT NULL, write_order INTEGER NOT NULL) STRICT;
CREATE TABLE alert_cooldowns (key TEXT PRIMARY KEY, value TEXT NOT NULL, write_order INTEGER NOT NULL) STRICT;
CREATE TABLE alert_hour (key TEXT PRIMARY KEY, value TEXT NOT NULL, write_order INTEGER NOT NULL) STRICT;
CREATE TABLE alerts_waiting (key TEXT PRIMARY KEY, value TEXT NOT NULL, write_order INTEGER NOT NULL) STRICT;
CREATE TABLE anomaly_counters (key TEXT PRIMARY KEY, value TEXT NOT NULL, write_order INTEGER NOT NULL) STRICT;
```
No other index: the only statements are the read at start, the upsert by key, and the delete by key.
## Each table
The value is the part's existing Go type, encoded with its existing JSON tags. Those are the fields `docs/state-files.md` lists today. A key of several fields is the JSON array of them, which keeps it unambiguous and readable.
- `bans`, kept by `bans.Ledger`.
- Key: the netblock, such as `203.0.113.0/24`.
- Value: `{"netblock", "last_request", "bans": [...]}`: the netblock's bans oldest first, each with today's ban fields and notes, and when the netblock last sent a request.
- No limit in the table. The ledger drops bans itself, as SPEC "Bans" says: past `SWW_MAX_BANS` of its own, it drops the earliest such ban of the netblock with the earliest `last_request`. Admin bans are never dropped and not counted.
- `clients`, kept by `ratelimit.Limiter`.
- Key: the client, `192.0.2.1/32` or an IPv6 group such as `2001:db8::/64`.
- Value: `ratelimit.Client`, as one line of `clients.json` holds it today.
- At most `SWW_MAX_TRACKED_CLIENTS`.
- `geojs_answers`, kept by `lookup.GeoJS`.
- Key: the client. Value: `lookup.Answer`. At most 100,000.
- `lists`, kept by `reputation.Lists`.
- Key: the list's URL. Value: `reputation.List` (`url`, `tried`, `fetched`, `lines`).
- One row per URL the settings name. Rows for a URL no setting names are deleted at start.
- `dnsbl_verdicts`, kept by `reputation.DNSBL`.
- Key: `["zen.spamhaus.org","192.0.2.1"]`, the zone and the address. Value: `reputation.Verdict`. At most 100,000.
- Rows for a zone `SWW_DNSBL_ZONES` does not name are deleted at start.
- `abuseipdb_scores`, kept by `reputation.AbuseIPDB`.
- Key: the client. Value: `reputation.Score`. At most 100,000.
- `abuseipdb_budget`, kept by `reputation.AbuseIPDB`.
- One row, key `today`. Value: `{"day", "spent"}`, the checks spent on that UTC day.
- `alert_cooldowns`, kept by `alerts.Queue`.
- Key: the JSON array of event, netblock, file, source, scope, AS number and name. Value: `alerts.Cooldown`.
- No limit: the end of each hour deletes the cooldowns that have run out, as today.
- `alert_hour`, kept by `alerts.Queue`.
- One row, key `current`. Value: `alerts.Hour`.
- `alerts_waiting`, kept by `alerts.Queue`.
- Key: the destination, `webhook`, `slack` or `ntfy`. Value: the alerts waiting for it, oldest first, at most 1,000 as today.
- Rows for a destination that is not set are deleted at start.
- `anomaly_counters`, kept by `anomaly.Counters`.
- Key: the JSON array of scope, netblock, AS number and name. Value: `anomaly.Counter`. At most 20,000.
An answer, verdict or score found past its time is deleted when it is found. Otherwise, at start each value is used as it was written: the counters' buckets already start afresh when their time has passed.
## Exported API
```go
package store
// Store holds sww's state in memory and writes each change to sww.db.
type Store struct{ /* unexported */ }
// Open opens sww.db in dir (SWW_STATE_DIR), creating it on a first start.
// A file or directory that cannot be written, or a file that is not an
// SQLite database, is an error naming the file.
func Open(dir string) (*Store, error)
// Run writes the changes made since the last write, every 10 seconds,
// until ctx is done. After each write it calls written with that write's
// error, nil when it succeeded. A failed write's changes are written next time.
func (s *Store) Run(ctx context.Context, written func(err error))
// Close writes the changes not yet written and closes sww.db. Nothing
// uses the store or its tables after Close.
func (s *Store) Close() error
// Table is one kind of state: values of type V by key. It is safe for
// concurrent use.
type Table[V any] struct{ /* unexported */ }
// NewTable returns the table name, holding what sww.db holds for it, read
// now. With limit above 0 it holds at most limit values: past that, the value
// put longest ago is dropped. A row that cannot be read into a V is an error
// naming the table and the key.
func NewTable[V any](s *Store, name string, limit int) (*Table[V], error)
// Get returns the value under key, and whether there is one. A slice or map
// in it is shared with the table: change a copy, then Put it.
func (t *Table[V]) Get(key string) (V, bool)
// Put sets the value under key.
func (t *Table[V]) Put(key string, value V)
// Delete removes the value under key, if there is one.
func (t *Table[V]) Delete(key string)
// Len returns how many values the table holds.
func (t *Table[V]) Len() int
// All yields every key and value, the value put longest ago first. The loop
// must not Put or Delete in the same table.
func (t *Table[V]) All() iter.Seq2[string, V]
```
```go
package db
// DB is sww.db. Only internal/store uses it.
type DB struct{ /* unexported */ }
// Change is one row to write: Value under Key in Table, or, with Delete
// set, the row's removal.
type Change struct {
Table string
Key string
Value []byte
WriteOrder int64
Delete bool
}
// Open opens the SQLite file at path, creating it with mode 0600 when it is
// missing, and applies, in one write transaction, the migrations not yet applied.
func Open(path string) (*DB, error)
// Read calls row for each row of table, lowest write order first, and
// returns row's first error.
func (d *DB) Read(table string, row func(key string, value []byte, writeOrder int64) error) error
// Write writes changes in one transaction: all of them or none.
func (d *DB) Write(changes []Change) error
// Close closes sww.db.
func (d *DB) Close() error
```
Table names are constants in the parts, never input, since SQL cannot take a table name as a parameter.
How a part uses a table, here `ratelimit` counting a request under the Limiter's own lock:
```go
// in ratelimit.New
clients, err := store.NewTable[Client](s, "clients", maxClients)
// counting a request
key := client.String()
c, _ := l.clients.Get(key)
c.Client = client
c.Minute.Add(now, time.Minute, 1)
l.clients.Put(key, c)
```
Each table belongs to one part. The part's own lock makes a get, change and put one step. The table's lock only keeps the table safe while the store writes it.
## Behaviour
**The read at start**
- `sww discriminator` calls `store.Open`, then builds the parts. Each part's constructor calls `NewTable` for its tables, and each table reads its rows once, oldest `write_order` first.
- A part works out what it derives from its rows once, at start: the ledger its count of bans and the netblock lengths banned, the lists the netblocks in each copy.
- Rows beyond a table's limit, after the limit was lowered, are dropped oldest first, and deleted at the first write.
- Serving starts after every table is read. Nothing reads `sww.db` again until the next start.
**Changes written**
- `Put` and `Delete` change memory at once and note the key. No request waits for the disk.
- Every 10 seconds the store writes every noted key in one transaction: an upsert (`INSERT ... ON CONFLICT(key) DO UPDATE`) of the current value, or a `DELETE` for a value deleted or dropped. A key changed many times in those 10 seconds is written once.
- Values are encoded to JSON outside the table's lock.
- `Close` writes what is left. An orderly stop loses nothing; a hard kill loses at most the last 10 seconds.
- The 10 seconds is a constant in `internal/store`, not a setting. It replaces `SWW_STATE_WRITE_DELAY` (10 seconds for bans) and `SWW_STATE_COUNTER_INTERVAL` (15 minutes for everything else).
**Limits and dropping**
- A table made with a limit drops the value put longest ago once it holds more. The value leaves memory at once and `sww.db` at the next write.
- `Get` does not change which value is dropped; only `Put` does. Every part already puts a client, counter or answer each time it is used, so the order matches today's.
- The limits are today's: `SWW_MAX_TRACKED_CLIENTS` clients, 100,000 GeoJS answers, 100,000 DNSBL verdicts, 100,000 AbuseIPDB scores, 20,000 anomaly counters, at most 1,000 alerts waiting per destination. `hashicorp/golang-lru/v2` moves from the parts into the store.
**Schema versions**
- Before 1.0, `001_schema.sql` is edited in place, and no `002` is added, as `REPO_POLICIES.md` says. `db.Open` applies each file not yet in `migrations`, in file order.
- The runner is the few lines that the policy's layout implies, with no library. Goose and `golang-migrate` each create their own tracking table, which the policy's `000_migration.sql` does instead.
**A file that is missing, damaged or cannot be written**
- Missing: `db.Open` creates it with the schema. That is a first start.
- Not a database, or damaged so that the read at start fails: `discriminator` stops with exit status 1 and an error naming the file, as a broken state file does today. Starting empty would forgive every repeat offender. To start afresh, an admin removes `sww.db`, `sww.db-wal` and `sww.db-shm`.
- A row whose value does not decode: the same, naming the table and the key.
- Cannot be written at start: `db.Open` always takes the write lock (`BEGIN IMMEDIATE`) to apply the migrations, so an unwritable file or directory stops the start with the error.
- A write that fails while running (disk full, permissions, damage): the transaction rolls back and the keys stay noted. `written` gets the error. The process log has it, the failure metric counts it, and a `file_error` alert names `sww.db`, with today's cooldown per file. The next write retries. The state in memory stays correct, and requests are served as before. Meanwhile memory grows by the keys changed or dropped.
- Removing `sww.db` while `discriminator` is running goes unnoticed: the state is lost at the next start.
- To change state by hand: stop `discriminator`, edit `sww.db` with any SQLite tool, start it. An edit made while it is running is not read, and a later change to the same row overwrites it.
## The catch: disk writes
SQLite writes only the values that changed. But the counters change with nearly every request, so on a busy site this writes more than today, not less.
- Each value changed between two writes costs about 8 KiB of disk writes: its 4 KiB page, once in the WAL and once in the file.
- A request changes about three values: its client, its GeoJS answer and its anomaly counters.
- With 100 clients sending requests in each 10 seconds, that is about 20 GiB a day. With 10, about 2 GiB.
- Today the whole files are written every 15 minutes, about 2.5 GiB a day for bans and clients at the defaults, whatever the traffic.
- The CrowdSec decision list is fetched every minute, and its copy changes each time, so its row is rewritten every minute. A 2 MiB list costs about 6 GiB a day, against about 0.2 GiB today.
- The lever is the 10 seconds. A longer time lowers the writes and raises what a hard kill loses.
## What goes away
- `internal/state` and its tests: the five JSON files, `Load`, `Run`, `WriteAll`, `Watch`, taking in edits, `.bad` files, the checksums, the `version` check.
- No code reads the JSON files any more.
- Hand edits while running. SPEC "Persistent state" and "Bans" designed them: an admin adds, keeps, lifts or forgets a ban by editing `bans.json`, and `discriminator` takes the edit in.
- While running, adding and lifting stay, through `POST /_sww/bans` and `DELETE /_sww/bans/...`.
- Keeping a ban (setting its cause to `admin`) and forgetting one (deleting its entry) are possible only while `discriminator` is stopped, with a SQLite tool. Endpoints for them would be a separate issue.
- Settings: `SWW_STATE_WRITE_DELAY` and `SWW_STATE_COUNTER_INTERVAL`. That settles both for the settings audit at https://git.eeqj.de/sneak/smallwebwaf/issues/202.
- Metrics: `sww_state_file_edits_taken_in_total` and `sww_state_file_edits_set_aside_total` go. The `file` label leaves the write, failure, last-write and size metrics. Size becomes the size of `sww.db` plus its WAL.
- `Offences.TokenRefused`, kept only so that old `clients.json` files load.
The change each caller needs:
- `bans`: `New(s *store.Store, rules Rules) (*Ledger, error)`. The `simplelru` field becomes the `bans` table.
- Every change puts the netblock's row: a ban made, lifted or made permanent, a refused request counted, a lookup added. So does each request from a netblock that has a row, which updates `last_request`.
- `dropOne` scans the table for the earliest `last_request` among netblocks holding a ban not made by an admin.
- `Load`, `LoadEdit`, `Changed` and `markChanged` go.
- `Snapshot` stays for `GET /_sww/bans`.
- `ratelimit`: `New(s *store.Store, limits Limits, maxClients int) (*Limiter, error)`. `Load` and `Snapshot` go. `Client` gets, `Requests` and `Len` read the table, and counting puts.
- `lookup`: `Params.Store`. `New` returns an error. `Load` and `Snapshot` go. Using an answer puts it with its new `used` time, as today.
- `reputation`: `Params.Store`, `DNSBLParams.Store` and `AbuseIPDBParams.Store`. `New`, `NewDNSBL` and `NewAbuseIPDB` return an error. `Load` and `Snapshot` go. Fetch failure counts are not stored, as today.
- `alerts`: `Params.Store`. `New` returns an error. `Load` and `Snapshot` go. Each destination's queue is the `alerts_waiting` row: add and remove get it, change a copy and put it.
- `anomaly`: `Params.Store`. `New` returns an error. `Load` and `Snapshot` go.
- `discriminator`: `Params.Store`. `New` returns `(*Server, error)`. `BanEntry` and `BanEntries`, the ban endpoints' answer form, move here from `state`. The `Server` fields that only handed parts to `state.Load` go.
- `metrics`: `StateFileWritten(size int, err error)`, with no file name. The two edit methods go.
- `config`: the two settings go.
- `sww`:
- It opens the store before the alert queue and the server; an error exits 1 with "cannot use the state".
- It runs `store.Run` with a `written` that updates the metrics and logs and alerts a failure.
- It calls `store.Close` at an orderly stop where `WriteAll` is today.
- Docs:
- `docs/state-files.md` is rewritten as the tables above, keeping today's field lists.
- `SPEC.md` gets a new "Persistent state", the "State files" settings, the editing paragraph of "Bans", and the size and backup lines of "State".
- `README.md` loses the settings rows and gets a new "State files" section.
- `docs/settings.md` and `docs/metrics.md` follow, as does the `GET /_sww/clients/...` line in `docs/admin-endpoints.md`.
- `go.mod`: adds `modernc.org/sqlite`. `fsnotify` stays, for the rule files and the lookup database.
## Tests
- `internal/db`:
- `Open` creates `sww.db` with mode `0600` and every table, and records migrations 0 and 1; a second `Open` applies nothing.
- `Open` fails on a read-only file, a read-only directory, and a file that is not a database, naming the file.
- `Write` applies upserts and deletes together, and applies none when one fails.
- `Read` yields rows lowest write order first.
- `internal/store`:
- Put, Get and Delete survive `Close` and a new `Open`.
- A table at its limit drops the value put longest ago, from memory at once and from `sww.db` at the next write.
- `Get` leaves the drop order alone. The drop order survives a reopen.
- A lower limit at reopen drops the oldest rows.
- A value got from a table is unchanged by a later `Put` of a new value under its key.
- A row that does not decode fails `NewTable`, naming the table and the key.
- A failed write calls `written` with the error, keeps its changes, and writes them at the next write. The test drops a table through a second connection, then restores it.
- Each part, replacing today's `snapshot_test.go` files: its state survives a reopen of the store.
- `bans`: admin bans are never dropped, and after a reopen the drop takes the netblock with the earliest last request.
- `reputation`: a list's copy is parsed at start; rows for URLs and zones no setting names are deleted.
- `alerts`: alerts waiting are sent after a reopen.
- `internal/sww`:
- An orderly stop and start keeps a ban, a client's counts and an alert waiting.
- A state directory that cannot be written stops the start with exit status 1.
## Disclosures
- Rule this cuts against: state well under about 100 MiB is kept as hand-editable JSON, and SQLite for it is a review defect. At today's defaults the state is a few tens of MiB. Carried out on sneak's order on this issue.
- Judgement call: one file, though the issue says "files". One transaction then covers every table, and there is one file to back up or remove. Splitting before 1.0 costs a path per table group in `store.Open`.
- Judgement call: a key and a JSON value per row, not a column per field. Values are only written and read whole, never queried, and every part's type already has its JSON form. A column per field would add a mapping for each of eleven tables, about 40 columns for a client alone. Reversing it before 1.0 means editing `001_schema.sql` and writing those mappings.
- Judgement call: changes are written every 10 seconds, not as each happens, so that no request waits for the disk.
- Judgement call: the `bans` table has no limit, since the ledger's dropping counts bans, not netblocks, and spares admin bans. At `SWW_MAX_BANS` 5,000 each drop scans 5,000 rows. At 100,000, if the settings audit moves it there, it scans 100,000, estimated at about a millisecond per new ban when full.
- Not checked: the disk-write figures are worked out from SQLite's 4 KiB page and today's value sizes. Nothing was measured.
Model: opus-5-5
any table not joined with other tables gets its own sqlite file.
no json in the db. normalize it properly.
why are we doing write intervals? can we not write in realtime in a dedicated single threaded goroutine eating from a channel? is there some issue with keeping the files up to date in realtime?
i’d also like a log database in the discriminator that keeps a cache of the last 24 hours (configurable) of requests or 1M rows, whichever is smaller.
we will eventually be adding a web analytics interface and a local log cache will be extremely useful.
any table not joined with other tables gets its own sqlite file.
no json in the db. normalize it properly.
why are we doing write intervals? can we not write in realtime in a dedicated single threaded goroutine eating from a channel? is there some issue with keeping the files up to date in realtime?
i’d also like a log database in the discriminator that keeps a cache of the last 24 hours (configurable) of requests or 1M rows, whichever is smaller.
we will eventually be adding a web analytics interface and a local log cache will be extremely useful.
also an error log sqlite file that tracks our own errors, 7d or 100k lines whichever is smaller. things like ipabusedb or geoip or routewatch backend timeouts, missed response to rproxy deadlines, etc.
also an error log sqlite file that tracks our own errors, 7d or 100k lines whichever is smaller. things like ipabusedb or geoip or routewatch backend timeouts, missed response to rproxy deadlines, etc.
For the spec writer (an Opus run, continuing sneak's order #217 (comment)): revise the spec at #217 (comment) according to sneak's review (#217 (comment) and #217 (comment)). Post the whole revised spec as one new comment, for his approval.
Files: each table that is not joined with another table gets its own SQLite file.
No JSON in the database: a properly normalized schema, with a column per field and whatever tables and keys that takes.
Writes: answer his question directly. Why write on an interval, and can every change be written as it happens by one dedicated goroutine reading from a channel? Unless there is a real obstacle, design it that way. State the cost plainly: the disk writes, whether each change gets its own transaction or the writer groups what is waiting in the channel, and what happens when the channel is full.
Request log database in the discriminator: keep the last 24 hours of requests (configurable) or 1,000,000 rows, whichever is smaller. It is for a web analytics interface to come. Give its schema, its settings and how old rows are removed.
Error log database: record our own errors for the last 7 days or 100,000 rows, whichever is smaller. Examples are AbuseIPDB, GeoIP or routewatch backend timeouts and missed rproxy deadlines. Give its schema, its settings and what is recorded, and where.
Keep the same sections as before: every table's schema, the files, the exported API, behaviour, the driver, what goes away and the tests. Every setting's name must say what it does on its own.
Write no code and open no PR.
Model: opus-5-5
**For the spec writer** (an Opus run, continuing sneak's order https://git.eeqj.de/sneak/smallwebwaf/issues/217#issuecomment-170436): revise the spec at https://git.eeqj.de/sneak/smallwebwaf/issues/217#issuecomment-170478 according to sneak's review (https://git.eeqj.de/sneak/smallwebwaf/issues/217#issuecomment-170544 and https://git.eeqj.de/sneak/smallwebwaf/issues/217#issuecomment-170551). Post the whole revised spec as one new comment, for his approval.
- **Files:** each table that is not joined with another table gets its own SQLite file.
- **No JSON in the database:** a properly normalized schema, with a column per field and whatever tables and keys that takes.
- **Writes:** answer his question directly. Why write on an interval, and can every change be written as it happens by one dedicated goroutine reading from a channel? Unless there is a real obstacle, design it that way. State the cost plainly: the disk writes, whether each change gets its own transaction or the writer groups what is waiting in the channel, and what happens when the channel is full.
- **Request log database in the discriminator:** keep the last 24 hours of requests (configurable) or 1,000,000 rows, whichever is smaller. It is for a web analytics interface to come. Give its schema, its settings and how old rows are removed.
- **Error log database:** record our own errors for the last 7 days or 100,000 rows, whichever is smaller. Examples are AbuseIPDB, GeoIP or routewatch backend timeouts and missed `rproxy` deadlines. Give its schema, its settings and what is recorded, and where.
- Keep the same sections as before: every table's schema, the files, the exported API, behaviour, the driver, what goes away and the tests. Every setting's name must say what it does on its own.
- Write no code and open no PR.
Model: opus-5-5
A new package, internal/store, holds every kind of state in memory. It reads the state from disk once, at start.
Each change is written to disk as it is made, by one goroutine.
The state is in 11 SQLite files in SWW_STATE_DIR. Tables joined by a foreign key share a file; every other table has a file of its own.
Every column holds one value. Nothing in the database is JSON.
Two more files are written and never read back:
requests.db: the last 24 hours of requests, or the last 1,000,000, whichever is fewer;
errors.db: discriminator's own warnings and errors, for the last 7 days or the last 100,000, whichever is fewer.
The parts that keep state only get, put and delete values in tables. They know nothing of the files, the writing, or how a full table drops values.
internal/state, the five JSON files, editing state while discriminator runs, and two settings go away. One setting comes: SWW_REQUEST_LOG_DATABASE_MAX_AGE.
Writing as changes happen
sneak asked why the earlier spec wrote on an interval, and whether one goroutine reading from a channel could write each change as it happens.
Yes. Nothing prevents it, and this spec does it.
The interval only cut disk writes. A client's row changes with each of its requests. An interval writes it once per interval instead of once per change. What writing at once costs is under "The catch: disk writes".
One goroutine writes every file. A change wakes it through a channel. It writes every change then waiting, in one transaction per file, then waits again.
While it is idle, a change is written at once, in a transaction of its own.
While it is busy, the changes made meanwhile wait and go into the next transaction together. A row changed several times meanwhile is written once, with its last value.
The channel carries only the wake-up. The changes wait beside it: for each key changed, its latest value, or that it was deleted.
A channel carrying the changes themselves can fill up. Then either a request waits for the disk, or a change is lost.
One waiting change per key does neither. A key changed a thousand times waits as one change.
The channel holds one wake-up. A wake-up sent while one already waits is dropped, since one is enough. So a full channel never holds anything up.
The two logs have no keys, so their entries wait in a list. Past 100,000 entries waiting in one log, a new entry is dropped and counted in the metrics. That happens only while the disk is failing or far behind.
The catch: disk writes
SQLite writes whole pages, so writing each change at once costs more disk writes than an interval does.
A commit writes each 4 KiB page it changed to the WAL. SQLite later copies the page into the file. A change made alone costs at least 4 KiB, however small it is.
At low traffic, each change is a commit of its own. An allowed request, at the default settings:
changes its client's row about three times: once when counted at the question, and for its bytes and its history at the report;
adds one row to requests.db, with its headers: about three pages.
That is about 7 page writes, about 28 KiB, per request:
at one request a second, about 2.3 GiB a day;
at ten a second, about 23 GiB a day.
The request log alone is about 1 GiB a day for each request a second.
An anomaly threshold that is set adds up to four pages per request: the client, its netblock, its AS number and the whole service each have a counter.
Under heavier load, the writer groups what waits into one commit, and a page shared by rows in it is written once. So the cost grows more slowly than the traffic.
Today, at the defaults, the JSON files cost about 2 to 4 GiB a day, whatever the traffic.
The lever, if this is too much: a pause of about a second before each write. Changes would then be grouped, and a row changed several times in that second written once. This spec has no pause.
Packages and driver
internal/table: the in-memory Table that every part holds its state in. It knows nothing of files. It keeps the order of use with hashicorp/golang-lru/v2/simplelru, as the parts do today.
internal/store: opens the files, reads them into tables at start, writes each change, and keeps the two logs. Only sww and discriminator import it. The parts get their tables from them.
internal/db: opens one SQLite file and applies its schema. Only internal/store imports it. It exists because REPO_POLICIES.md puts the schema files in internal/db/migrations/, embedded in the binary.
internal/store imports the parts for their types, as internal/state does today. So the parts cannot import it: they import internal/table instead. That is why the table is a package of its own.
Driver: modernc.org/sqlite, pure Go with no CGO, as the Go package defaults require, through database/sql, with one connection per file.
The files
All in SWW_STATE_DIR (default /var/lib/sww). Each is named after its first table, and has SQLite's -wal and -shm files beside it while open.
The state, read at start:
bans.db
clients.db
geojs_answers.db
lists.db
dnsbl_verdicts.db
abuseipdb_scores.db
abuseipdb_checks_spent.db
alerts.db
alert_cooldowns.db
alert_hours.db
anomaly_counters.db
The logs, never read back:
requests.db
errors.db
Settings on open:
journal_mode=WAL and synchronous=NORMAL. A crash or a hard kill never damages a file.
A hard kill of the process loses only the changes not yet written, normally none.
A power loss can also lose the last commits the system had not yet put on the disk: up to about 30 seconds of them, with Linux's defaults.
foreign_keys=ON.
db.Open creates a missing file with mode 0600 before SQLite opens it, since the files hold visitors' addresses. SQLite gives the -wal and -shm files the same mode.
The image's entrypoint already gives SWW_STATE_DIR to the sww user.
Sizes at the defaults, roughly:
requests.db: up to about 1 GiB at 1,000,000 rows.
errors.db: up to about 30 MiB.
clients.db: about 8 MiB. bans.db: about 3 MiB.
The others: a few MiB or less.
SQLite reuses freed pages, so a file never shrinks, and never grows past its fullest.
Schema
internal/db/migrations/000_migration.sql is applied to every file first. It holds only the tracking table, as REPO_POLICIES.md requires:
CREATETABLEIFNOTEXISTSmigrations(versionINTEGERPRIMARYKEY,-- the file's number: 1 for 001_schema.sql
applied_atINTEGERNOTNULL-- when it was applied
)STRICT;
Each file's own tables are in internal/db/migrations/NAME/001_schema.sql, such as clients/001_schema.sql.
In every table:
Every table is STRICT.
A time is an INTEGER: milliseconds since 1970, in UTC.
An address or a netblock is TEXT, as Go's netip writes it, such as 192.0.2.1/32 or 2001:db8::/64.
An AS number is TEXT, such as AS64496.
Yes or no is an INTEGER, 0 or 1.
Text that can be empty is '', as in Go.
NULL means a number, a time or a field does not apply.
A key column is never NULL: a part of a key that does not apply is ''.
Counters share one layout. For a window and what it counts, such as minute_requests, three columns:
_start: when the bucket under way began, NULL while it is empty;
_current: the count in it;
_previous: the count in the bucket before it.
Below each file, "Held as" names the Store field that holds it, its limit, and the order its values are read in at start.
bans.db
-- One row per ban. A netblock's bans are numbered from 0, oldest first.
CREATETABLEbans(netblockTEXTNOTNULL,positionINTEGERNOTNULL,startINTEGERNOTNULL,expiresINTEGER,-- NULL: permanent
causeTEXTNOTNULLCHECK(causeIN('limit','attack','admin','crowdsec')),reasonTEXTNOTNULL,liftedINTEGER,-- NULL: not lifted
-- The notes:
asnTEXTNOTNULL,as_nameTEXTNOTNULL,countryTEXTNOTNULL,limit_kindTEXTCHECK(limit_kindIN('requests','bytes','refusals')),limit_valueINTEGER,limit_windowTEXTCHECK(limit_windowIN('minute','hour','day')),limit_countREAL,limit_percentINTEGER,limit_percent_settingTEXT,rule_idTEXT,rule_targetTEXT,trap_pathTEXT,request_timeINTEGER,-- NULL: no request, as for an admin's ban
request_methodTEXTNOTNULL,request_hostTEXTNOTNULL,request_pathTEXTNOTNULL,-- with its query string
request_statusINTEGERNOTNULL,-- 0: nothing was sent
request_user_agentTEXTNOTNULL,requestsINTEGERNOTNULL,refusedINTEGERNOTNULL,earlier_limit_bansINTEGERNOTNULL,earlier_attack_bansINTEGERNOTNULL,earlier_admin_bansINTEGERNOTNULL,earlier_crowdsec_bansINTEGERNOTNULL,PRIMARYKEY(netblock,position))STRICT;-- The reputation sources that listed the client, in the notes' order.
CREATETABLEban_reputation_hits(netblockTEXTNOTNULL,ban_positionINTEGERNOTNULL,positionINTEGERNOTNULL,sourceTEXTNOTNULL,-- a list's URL, a DNSBL zone with its key masked, or abuseipdb
scoreINTEGER,-- AbuseIPDB's score; NULL for the others
PRIMARYKEY(netblock,ban_position,position),FOREIGNKEY(netblock,ban_position)REFERENCESbans(netblock,position)ONDELETECASCADE)STRICT;
Held as Bans, a table.Table[netip.Prefix, []bans.Ban]: each netblock's bans, oldest first.
No limit. The ledger drops bans itself, as SPEC "Bans" says: past SWW_MAX_BANS of its own, the earliest ban of the netblock that has gone longest without a request. An admin's ban is never dropped.
Read in the order of each netblock's latest ban start, as today.
clients.db
CREATETABLEclients(clientTEXTPRIMARYKEY,-- 192.0.2.1/32, or an IPv6 group such as 2001:db8::/64
minute_requests_startINTEGER,minute_requests_currentINTEGERNOTNULL,minute_requests_previousINTEGERNOTNULL,hour_requests_startINTEGER,hour_requests_currentINTEGERNOTNULL,hour_requests_previousINTEGERNOTNULL,day_requests_startINTEGER,day_requests_currentINTEGERNOTNULL,day_requests_previousINTEGERNOTNULL,minute_bytes_startINTEGER,minute_bytes_currentINTEGERNOTNULL,minute_bytes_previousINTEGERNOTNULL,hour_bytes_startINTEGER,hour_bytes_currentINTEGERNOTNULL,hour_bytes_previousINTEGERNOTNULL,day_bytes_startINTEGER,day_bytes_currentINTEGERNOTNULL,day_bytes_previousINTEGERNOTNULL,minute_refusals_startINTEGER,minute_refusals_currentINTEGERNOTNULL,minute_refusals_previousINTEGERNOTNULL,-- The history:
first_seenINTEGER,-- NULL until a request of its has ended
last_seenINTEGER,asnTEXTNOTNULL,-- as last looked up
as_nameTEXTNOTNULL,countryTEXTNOTNULL,looked_upINTEGER,-- NULL: never looked up
requestsINTEGERNOTNULL,forwardedINTEGERNOTNULL,refusedINTEGERNOTNULL,request_bytesINTEGERNOTNULL,response_bytesINTEGERNOTNULL,responses_1xxINTEGERNOTNULL,responses_2xxINTEGERNOTNULL,responses_3xxINTEGERNOTNULL,responses_4xxINTEGERNOTNULL,responses_5xxINTEGERNOTNULL,-- every status from 500 up
limit_offencesINTEGERNOTNULL,attack_offencesINTEGERNOTNULL,rule_blocked_offencesINTEGERNOTNULL,waf_blocked_offencesINTEGERNOTNULL)STRICT;
Held as Clients, a table.Table[netip.Prefix, ratelimit.Client]. At most SWW_MAX_TRACKED_CLIENTS. Read in the order of last_seen.
geojs_answers.db
CREATETABLEgeojs_answers(clientTEXTPRIMARYKEY,asnTEXTNOTNULL,-- '' when GeoJS knows none
as_nameTEXTNOTNULL,countryTEXTNOTNULL,-- '' when GeoJS cannot place the client
answeredINTEGERNOTNULL)STRICT;
Held as GeoJSAnswers, a table.Table[netip.Prefix, lookup.Answer]. At most 100,000. Read in the order of answered. Answer.Used goes: the table keeps the order of use.
lists.db
-- One row per list fetched from a URL: each blocklist, SWW_ASN_LIMIT_PERCENT_URL
-- and the CrowdSec decision list.
CREATETABLElists(urlTEXTPRIMARYKEY,triedINTEGERNOTNULL,-- the last fetch, failed or not
fetchedINTEGER-- the last good copy; NULL while no fetch has succeeded
)STRICT;-- What the last good copy of each list says.
CREATETABLEblocklist_netblocks(urlTEXTNOTNULLREFERENCESlists(url)ONDELETECASCADE,netblockTEXTNOTNULL,PRIMARYKEY(url,netblock))STRICT;CREATETABLEasn_limit_percents(urlTEXTNOTNULLREFERENCESlists(url)ONDELETECASCADE,asnTEXTNOTNULL,percentINTEGERNOTNULLCHECK(percentBETWEEN0AND100),PRIMARYKEY(url,asn))STRICT;CREATETABLEcrowdsec_decisions(urlTEXTNOTNULLREFERENCESlists(url)ONDELETECASCADE,netblockTEXTNOTNULL,expiresINTEGERNOTNULL,-- of the decision on the netblock that ends last
scenarioTEXTNOTNULL,PRIMARYKEY(url,netblock))STRICT;
Held as:
Lists, a table.Table[string, reputation.List];
BlocklistNetblocks, a table.Table[reputation.ListNetblock, struct{}];
ASNLimitPercents, a table.Table[reputation.ListASN, int64];
CrowdSecDecisions, a table.Table[reputation.ListNetblock, reputation.Decision].
None has a limit. The order they are read in does not matter.
A good fetch is parsed as today, then compared with the table. The entries added or changed are put, and those gone are deleted. An unchanged list writes only its lists row.
List.Lines goes. The copy as fetched, comment lines included, is no longer kept; what it says is.
At start, the part deletes the lists of URLs no setting names, with their entries.
The part works out the netblock lengths in each list once, at start, and keeps them up to date as entries change.
dnsbl_verdicts.db
CREATETABLEdnsbl_verdicts(zoneTEXTNOTNULL,-- as SWW_DNSBL_ZONES names it
clientTEXTNOTNULL,-- the address asked about
listedINTEGERNOTNULLCHECK(listedIN(0,1)),fetchedINTEGERNOTNULL,PRIMARYKEY(zone,client))STRICT;
Held as DNSBLVerdicts, a table.Table[reputation.DNSBLQuery, reputation.Verdict]. At most 100,000. Read in the order of fetched. At start, the part deletes the verdicts of zones SWW_DNSBL_ZONES does not name.
Held as AbuseIPDBScores, a table.Table[netip.Prefix, reputation.Score]. At most 100,000. Read in the order of fetched.
abuseipdb_checks_spent.db
CREATETABLEabuseipdb_checks_spent(dayINTEGERPRIMARYKEY,-- the first moment of the UTC day
spentINTEGERNOTNULL-- checks made that day
)STRICT;
Held as AbuseIPDBChecksSpent, a table.Table[time.Time, int]. A day's first check deletes the rows of earlier days.
alerts.db
-- One row per alert waiting for a destination. The detail's columns, from mode on,
-- are NULL for an event without that field; docs/alerts.md lists them by event.
CREATETABLEalerts(idINTEGERPRIMARYKEY,-- rising, in the order raised
timeINTEGERNOTNULL,eventTEXTNOTNULLCHECK(eventIN('ban','permanent_ban','anomaly','waf_block','reputation_hit','source_failure','file_error','summary')),hostTEXTNOTNULL,clientTEXTNOTNULL,netblockTEXTNOTNULL,asnTEXTNOTNULL,as_nameTEXTNOTNULL,countryTEXTNOTNULL,reasonTEXTNOTNULL,suppressed_repeatsINTEGERNOTNULL,modeTEXT,-- ban, permanent_ban, waf_block: observe or detect
ban_causeTEXT,-- ban and permanent_ban, with the ban's notes:
ban_expiresINTEGER,-- NULL for a permanent ban
limit_kindTEXT,limit_valueINTEGER,limit_windowTEXT,limit_countREAL,limit_percentINTEGER,limit_percent_settingTEXT,rule_idTEXT,rule_targetTEXT,trap_pathTEXT,request_timeINTEGER,request_methodTEXT,-- waf_block too
request_hostTEXT,request_pathTEXT,-- waf_block too
request_statusINTEGER,request_user_agentTEXT,requestsINTEGER,refusedINTEGER,earlier_limit_bansINTEGER,earlier_attack_bansINTEGER,earlier_admin_bansINTEGER,earlier_crowdsec_bansINTEGER,waf_scoreINTEGER,-- waf_block
sourceTEXT,-- reputation_hit, source_failure
abuseipdb_scoreINTEGER,-- reputation_hit by AbuseIPDB
errorTEXT,-- source_failure, file_error
asking_again_inINTEGER,-- source_failure by GeoJS, in milliseconds
fileTEXT,-- file_error
anomaly_scopeTEXT,-- anomaly
anomaly_kindTEXT,anomaly_windowTEXT,anomaly_countREAL,anomaly_thresholdINTEGER,watch_nameTEXT,-- anomaly in the scope watch
summary_hourINTEGER,-- summary: the hour summed up
held_backINTEGER-- summary: the alerts held back in it
)STRICT;CREATETABLEalerts_waiting(destinationTEXTNOTNULLCHECK(destinationIN('webhook','slack','ntfy')),alert_idINTEGERNOTNULLREFERENCESalerts(id)ONDELETECASCADE,PRIMARYKEY(destination,alert_id))STRICT;-- waf_block: the Core Rule Set rules that matched, in order.
CREATETABLEalert_waf_rule_ids(alert_idINTEGERNOTNULLREFERENCESalerts(id)ONDELETECASCADE,positionINTEGERNOTNULL,rule_idINTEGERNOTNULL,PRIMARYKEY(alert_id,position))STRICT;-- ban and permanent_ban: the reputation sources in the ban's notes.
CREATETABLEalert_reputation_hits(alert_idINTEGERNOTNULLREFERENCESalerts(id)ONDELETECASCADE,positionINTEGERNOTNULL,sourceTEXTNOTNULL,scoreINTEGER,PRIMARYKEY(alert_id,position))STRICT;-- summary: the alerts held back, by event.
CREATETABLEalert_summary_events(alert_idINTEGERNOTNULLREFERENCESalerts(id)ONDELETECASCADE,eventTEXTNOTNULL,countINTEGERNOTNULL,PRIMARYKEY(alert_id,event))STRICT;
Held as Alerts, a table.Table[int64, alerts.Alert], and AlertsWaiting, a table.Table[alerts.Waiting, struct{}]. Neither table has a limit: the queue keeps at most 1,000 alerts waiting per destination, as today.
An alert is stored once, however many destinations wait for it. Sending it to one deletes that destination's row. The last one sent deletes the alert.
A destination's queue is its rows in alerts_waiting, oldest first.
A ban's notes take their AS number, AS name and country from the alert's own, which are the same.
alerts.Alert.Detail becomes a struct with a field for each detail docs/alerts.md lists, in place of map[string]any. The JSON sent has the same fields and values.
At start, the part deletes the rows of destinations that are not set, as today.
alert_cooldowns.db
CREATETABLEalert_cooldowns(eventTEXTNOTNULL,netblockTEXTNOTNULL,fileTEXTNOTNULL,sourceTEXTNOTNULL,scopeTEXTNOTNULL,asnTEXTNOTNULL,nameTEXTNOTNULL,sentINTEGERNOTNULL,-- the last alert let through
suppressed_repeatsINTEGERNOTNULL,-- the repeats held back since
PRIMARYKEY(event,netblock,file,source,scope,asn,name))STRICT;
Held as AlertCooldowns, a table.Table[alerts.CooldownKey, alerts.Cooldown]. No limit: the end of each hour deletes the cooldowns that have run out, as today.
alert_hours.db
CREATETABLEalert_hours(startINTEGERPRIMARYKEY,-- the hour under way, by the clock
sentINTEGERNOTNULL-- the alerts let through in it
)STRICT;CREATETABLEalert_hour_held_back(hourINTEGERNOTNULLREFERENCESalert_hours(start)ONDELETECASCADE,eventTEXTNOTNULL,countINTEGERNOTNULL,-- the alerts held back past SWW_ALERT_MAX_PER_HOUR
PRIMARYKEY(hour,event))STRICT;
Held as AlertHours, a table.Table[time.Time, alerts.Hour]: one value, the hour under way. Its end sends the summary, deletes it and puts the next.
anomaly_counters.db
CREATETABLEanomaly_counters(scopeTEXTNOTNULLCHECK(scopeIN('client','net','asn','total','watch')),netblockTEXTNOTNULL,-- '' for asn and total
asnTEXTNOTNULL,-- '' but for asn
nameTEXTNOTNULL,-- '' but for watch
minute_requests_startINTEGER,minute_requests_currentINTEGERNOTNULL,minute_requests_previousINTEGERNOTNULL,hour_requests_startINTEGER,hour_requests_currentINTEGERNOTNULL,hour_requests_previousINTEGERNOTNULL,minute_bytes_startINTEGER,minute_bytes_currentINTEGERNOTNULL,minute_bytes_previousINTEGERNOTNULL,hour_bytes_startINTEGER,hour_bytes_currentINTEGERNOTNULL,hour_bytes_previousINTEGERNOTNULL,PRIMARYKEY(scope,netblock,asn,name))STRICT;
Held as AnomalyCounters, a table.Table[anomaly.Key, anomaly.Counter]. At most 20,000. Read in the order of each counter's latest _start.
requests.db
-- One row per request: the last line discriminator writes about it. The columns are
-- the fields of docs/request-log.md, but type, instance and discriminator_line,
-- which are the same on every row.
CREATETABLErequests(idINTEGERPRIMARYKEY,-- rising, in the order written
timeINTEGERNOTNULL,request_idTEXTNOTNULL,client_ipTEXTNOTNULL,peer_ipTEXTNOTNULL,forwarded_forTEXTNOTNULL,client_groupTEXTNOTNULL,asnTEXTNOTNULL,as_nameTEXTNOTNULL,countryTEXTNOTNULL,methodTEXTNOTNULL,schemeTEXTNOTNULL,hostTEXTNOTNULL,pathTEXTNOTNULL,queryTEXTNOTNULL,protocolTEXTNOTNULL,statusINTEGERNOTNULL,request_bytesINTEGERNOTNULL,response_bytesINTEGERNOTNULL,refererTEXTNOTNULL,user_agentTEXTNOTNULL,content_typeTEXTNOTNULL,content_lengthINTEGER,-- NULL: none announced
has_authorizationINTEGERNOTNULLCHECK(has_authorizationIN(0,1)),has_cookieINTEGERNOTNULLCHECK(has_cookieIN(0,1)),response_content_typeTEXTNOTNULL,abortedINTEGERNOTNULLCHECK(abortedIN(0,1)),actionTEXTNOTNULL,would_actionTEXTNOTNULL,limit_percentINTEGER,limit_percent_settingTEXTNOTNULL,bytes_percentINTEGER,bytes_percent_settingTEXTNOTNULL,minute_requestsREAL,-- the counts; NULL when not counted
hour_requestsREAL,day_requestsREAL,minute_bytesREAL,hour_bytesREAL,day_bytesREAL,limit_hitTEXTNOTNULL,waf_scoreINTEGER,offenceTEXTNOTNULL,ban_expiresINTEGER,-- NULL: no ban, or a permanent one
ban_permanentINTEGERNOTNULLCHECK(ban_permanentIN(0,1)),duration_totalREALNOTNULL,-- milliseconds, as in the log
duration_checksREAL,duration_wafREAL,duration_upstream_totalREAL)STRICT;CREATEINDEXrequests_timeONrequests(time);-- The headers SWW_LOG_REQUEST_HEADERS names.
CREATETABLErequest_headers(requestINTEGERNOTNULLREFERENCESrequests(id)ONDELETECASCADE,nameTEXTNOTNULL,-- in lower case
valueTEXTNOTNULL,PRIMARYKEY(request,name))STRICT;-- The rule file rules that matched, in order.
CREATETABLErequest_rule_ids(requestINTEGERNOTNULLREFERENCESrequests(id)ONDELETECASCADE,positionINTEGERNOTNULL,rule_idTEXTNOTNULL,PRIMARYKEY(request,position))STRICT;-- The Core Rule Set rules that matched, in order.
CREATETABLErequest_waf_rule_ids(requestINTEGERNOTNULLREFERENCESrequests(id)ONDELETECASCADE,positionINTEGERNOTNULL,rule_idINTEGERNOTNULL,PRIMARYKEY(request,position))STRICT;-- The reputation sources the client is on, in the log's order.
CREATETABLErequest_reputation(requestINTEGERNOTNULLREFERENCESrequests(id)ONDELETECASCADE,positionINTEGERNOTNULL,sourceTEXTNOTNULL,PRIMARYKEY(request,position))STRICT;
Written: discriminator adds a row as it writes a request's last line. That is the report line of a question, or the one line of a request to its own endpoints. A question whose report never comes has no row; its decision line is on stdout, as today.
Removed: once a minute, the writer deletes the rows older than SWW_REQUEST_LOG_DATABASE_MAX_AGE, and the rows beyond the newest 1,000,000. Their headers, rule ids and sources go with them. Between two such deletions, the table can hold a minute's rows over either bound.
The setting: SWW_REQUEST_LOG_DATABASE_MAX_AGE (default 24h), how long a request is kept in requests.db. A duration above zero. A value that is set but invalid stops the start. The 1,000,000 is a constant.
Read: nothing reads it while discriminator runs. It is for the web analytics interface to come, which will add its own queries. Meanwhile any SQLite tool can read it, even while discriminator runs.
Size: about 0.5 to 1 KiB per row, with its index and headers.
errors.db
-- discriminator's own messages at warn or error.
CREATETABLEerrors(idINTEGERPRIMARYKEY,-- rising, in the order logged
timeINTEGERNOTNULL,levelTEXTNOTNULLCHECK(levelIN('WARN','ERROR')),messageTEXTNOTNULL)STRICT;CREATEINDEXerrors_timeONerrors(time);-- Each message's attributes, such as error, source, url or file.
CREATETABLEerror_attributes(errorINTEGERNOTNULLREFERENCESerrors(id)ONDELETECASCADE,positionINTEGERNOTNULL,nameTEXTNOTNULL,valueTEXTNOTNULL,PRIMARYKEY(error,position))STRICT;
Recorded: every message the process log gets at warn or error, whatever SWW_LOG_LEVEL says. Its attributes are kept, but type and instance, which every message has.
Where: Store.ErrorHandler wraps the process log's handler. So nothing changes where errors are logged, and every error logged is recorded.
Each example sneak gave is logged already, so it is recorded:
GeoJS failing or timing out, in lookup, and the lookup database that cannot be read;
AbuseIPDB checks failing, timing out or refused, DNSBL queries failing and list fetches failing, in reputation;
besides those, an alert destination failing, the syslog server failing, a rule file that does not parse, and a database file that cannot be written.
routewatch: sww has no routewatch lookup. Its failures will be recorded the same way once it has one.
New: an answer too late for rproxy. rproxy stops waiting after SWRP_TIMEOUT_MS, which discriminator does not know today. So:
rproxy sends SWRP_TIMEOUT_MS with every question, in a new header, X-Sww-Answer-Time-Limit-Ms.
discriminator removes the header from the request the checks see, as it does X-Sww-Client.
A question whose header is missing, or not a whole number above zero, is refused with 400, as one with a bad X-Request-ID is.
Once discriminator has written its answer, it compares the time since it read the question's headers with the header. If that is longer, it logs a warning, "answered after rproxy's time limit", with request_id, the milliseconds taken and the limit.
That time leaves out the network both ways, so an answer late by less than that goes unseen. Every answer it reports was late.
Removed: once a minute, the rows older than 7 days, and the rows beyond the newest 100,000. Neither has a setting.
A failure to write errors.db is logged on stdout only, so that it does not feed itself.
Exported API
packagetable// Table holds values of type V by key, in memory. A Get or a Put of a value// is a use of it. A table with a limit holds at most that many values: a Put// of one more drops the value used longest ago. It is safe for concurrent use.typeTable[Kcomparable,Vany]struct{/* unexported */}// New returns a table holding values, put in the order given, and at most// limit values, or any number when limit is 0. changed, unless nil, is called// under the table's lock with each value put, and with each key deleted or// dropped, deleted then being true. The values given to New are not changes,// but a value they drop past the limit is.funcNew[Kcomparable,Vany](limitint,valuesiter.Seq2[K,V],changedfunc(keyK,valueV,deletedbool),)*Table[K,V]// Get returns the value under key, and whether there is one. A slice or map// in the value is shared with the table: change a copy, then Put it.func(t*Table[K,V])Get(keyK)(V,bool)// Put sets the value under key.func(t*Table[K,V])Put(keyK,valueV)// Delete removes the value under key, if there is one.func(t*Table[K,V])Delete(keyK)// Len returns how many values the table holds.func(t*Table[K,V])Len()int// All yields every key and value, the value used longest ago first. It is// not a use. The loop must not call the table.func(t*Table[K,V])All()iter.Seq2[K,V]
packagestore// Params are what Open needs.typeParamsstruct{// Dir is the directory of the database files (SWW_STATE_DIR).Dirstring// MaxTrackedClients is the most clients held (SWW_MAX_TRACKED_CLIENTS).MaxTrackedClientsint// RequestLogMaxAge is how long a request is kept in requests.db// (SWW_REQUEST_LOG_DATABASE_MAX_AGE).RequestLogMaxAgetime.Duration// Now tells the time, normally time.Now in UTC.Nowfunc()time.Time}// Store is sww's state, held in memory and written to the files in Dir as// it changes, and the request and error logs. Each table belongs to one// part, which is given it as it is made.typeStorestruct{Bans*table.Table[netip.Prefix,[]bans.Ban]Clients*table.Table[netip.Prefix,ratelimit.Client]GeoJSAnswers*table.Table[netip.Prefix,lookup.Answer]Lists*table.Table[string,reputation.List]BlocklistNetblocks*table.Table[reputation.ListNetblock,struct{}]ASNLimitPercents*table.Table[reputation.ListASN,int64]CrowdSecDecisions*table.Table[reputation.ListNetblock,reputation.Decision]DNSBLVerdicts*table.Table[reputation.DNSBLQuery,reputation.Verdict]AbuseIPDBScores*table.Table[netip.Prefix,reputation.Score]AbuseIPDBChecksSpent*table.Table[time.Time,int]Alerts*table.Table[int64,alerts.Alert]AlertsWaiting*table.Table[alerts.Waiting,struct{}]AlertCooldowns*table.Table[alerts.CooldownKey,alerts.Cooldown]AlertHours*table.Table[time.Time,alerts.Hour]AnomalyCounters*table.Table[anomaly.Key,anomaly.Counter]// unexported: the files, the changes waiting, the log entries waiting}// Open opens every database file in params.Dir, creating any that is// missing, and reads every table of the state into memory. It reads nothing// from requests.db and errors.db. An error names the file and, for a row// that does not read, the table and the key.funcOpen(paramsParams)(*Store,error)// Write is one write of a database file, as Run reports it.typeWritestruct{// File is the file's name, such as clients.db.Filestring// Size is the file's size with its WAL, after the write.Sizeint64// Err is why the write failed, nil when it succeeded.Errerror// Dropped is how many log entries were dropped since the file's last// write, since too many were waiting.Droppedint}// Run writes each change as it is made, and removes the logs' old rows once// a minute, until ctx is done. It calls written after each write.func(s*Store)Run(ctxcontext.Context,writtenfunc(Write))// AddRequest adds line, the last line discriminator writes about a request,// to requests.db. It never waits for the disk.func(s*Store)AddRequest(line*requestlog.Line)// ErrorHandler returns a handler that passes every record to next, and adds// each one at level warn or above to errors.db, whatever next's level. It// never waits for the disk.func(s*Store)ErrorHandler(nextslog.Handler)slog.Handler// Close writes the changes and log entries still waiting, and closes every// file. Nothing uses the store or its tables after Close.func(s*Store)Close()error
packagedb// Open opens the SQLite file at path, creating it with mode 0600 when it is// missing, with WAL, synchronous NORMAL and foreign keys on. In one write// transaction it applies 000_migration.sql, then the migrations under// migrations/schema not yet applied, such as clients/001_schema.sql.funcOpen(path,schemastring)(*sql.DB,error)
How a part uses its table, here ratelimit counting a request, under the Limiter's own lock:
// sww or discriminator, making the partlimiter:=ratelimit.New(limits,s.Clients)// counting a requestc,_:=l.clients.Get(client)c.Client=clientc.Minute.Add(now,time.Minute,1)l.clients.Put(client,c)
Each table belongs to one part. The table's own lock keeps it safe, but a part that gets a value, changes it and puts it back holds its own lock across the three, as it does today.
Behaviour
The read at start
sww discriminator opens the store before anything that keeps state. Open opens every file, creating any that is missing, and reads each state table whole, once. It reads nothing from requests.db and errors.db.
Each table's values are put in the order its section gives, so that a full table drops the same value after a restart as before.
Rows beyond a table's limit, after the limit was lowered, are dropped, oldest first, and deleted at the first write.
Each part works out once, at start, what it derives from its tables:
the ledger, its count of bans and the netblock lengths banned;
the lists, the netblock lengths in each list;
the alert queue, its next alert id.
At start, the parts delete what no setting names any more: the lists of other URLs, the verdicts of other zones, and the alerts waiting for a destination that is not set.
Nothing else is rewritten at start. A value past its time is found so when it is used, as today.
Serving starts once every table is read. Nothing reads the state files again until the next start.
Changes written
Put and Delete change memory at once, and leave the change waiting for the writer. No request waits for the disk.
The writer works as "Writing as changes happen" says.
Each file's waiting changes go into one transaction, from BEGIN IMMEDIATE to COMMIT.
Its tables are written in the order its schema creates them, so that a row's parent is written before the row.
How a change becomes rows:
A value of one row is written with INSERT ... ON CONFLICT DO UPDATE.
A value of several rows deletes the key's rows and inserts them again. Such values are a netblock's bans with their hits, and an hour with its held-back counts.
A key deleted or dropped deletes its rows. The rows that reference them go with them.
The writer turns values into rows, outside every table's lock.
Close writes what is still waiting. An orderly stop loses nothing.
Limits and dropping
A table with a limit drops the value used longest ago when a Put would take it past the limit. A Get or a Put is a use. The value leaves memory at once, and its file at the next write.
The limits are today's: SWW_MAX_TRACKED_CLIENTS clients, and 100,000 GeoJS answers, 100,000 DNSBL verdicts, 100,000 AbuseIPDB scores and 20,000 anomaly counters. They move from the parts into internal/store, and the parts no longer know them.
Product rules about what is kept stay in the parts:
the ledger drops bans past SWW_MAX_BANS, never an admin's;
the alert queue keeps at most 1,000 alerts waiting per destination;
the end of each hour deletes the cooldowns that have run out.
Two drop orders change slightly, both of caches:
DNSBL verdicts and AbuseIPDB scores are dropped by their last use, not by when they were fetched.
Reading a client, or a netblock's bans, for an admin endpoint now counts as a use.
Schema versions
Before 1.0, each 001_schema.sql is edited in place and no 002 is added, as REPO_POLICIES.md says.
db.Open applies 000_migration.sql, then each of the file's migrations not yet in migrations, in file order, in one write transaction.
The runner is the few lines that layout needs, with no library. goose and golang-migrate each create their own tracking table, which the policy's 000_migration.sql creates instead.
A file that is missing, damaged or cannot be written
Missing: created with its schema, as on a first start.
To forget one kind of state, an admin stops discriminator and removes its file, with its -wal and -shm.
Not a database, or damaged so that reading it at start fails: discriminator stops with exit status 1 and an error naming the file. Starting empty would forgive every repeat offender. The same holds for requests.db and errors.db, which are opened but not read.
A row that does not read, such as a netblock that is not one, stops the start the same way, naming the file, the table and the key. The CHECK constraints refuse most such edits as they are made.
Cannot be written at start: db.Open takes the write lock to apply the schema, so the start stops with the error.
A write that fails while running, from a full disk, permissions or damage:
The transaction rolls back. Its changes wait again, but those a newer change to the same key has replaced.
It is tried again a second later, and twice as long after each further failure, up to a minute.
The state in memory stays correct, and requests are served as before. Meanwhile memory grows by one waiting change per key changed or dropped.
Run reports the failure. sww logs it, counts it in the metrics, and raises a file_error alert naming the file, with today's cooldown per file.
Removing a file while discriminator runs goes unnoticed. The state in it is lost at the next start.
Reading and editing by hand
Any SQLite tool can read the files at any time, even while discriminator runs.
To change state: stop discriminator, edit the file, start it. A change made while it runs is not read, and the next change to the same row overwrites it.
What goes away
internal/state and its tests: the five JSON files, Load, Run, WriteAll, Watch, taking in edits, .bad files, the checksums and the version check.
Editing state while discriminator runs, which SPEC "Persistent state" and "Bans" designed:
While running, an admin still adds and lifts bans through POST /_sww/bans and DELETE /_sww/bans/....
Keeping a ban (cause admin) or forgetting one (deleting its row) needs discriminator stopped and a SQLite tool. Endpoints for them would be a separate issue.
Settings: SWW_STATE_WRITE_DELAY and SWW_STATE_COUNTER_INTERVAL. That settles both for the settings audit at #202.
Metrics: sww_state_file_edits_taken_in_total and sww_state_file_edits_set_aside_total.
Fields and types:
Offences.TokenRefused, kept only so that old clients.json files load;
lookup.Answer.Used;
reputation.List.Lines and reputation.Checks;
alerts.State.
In each part: Load, LoadEdit, Changed, markChanged, every Snapshot but the ledger's, which GET /_sww/bans uses, and the part's own map or LRU, which its table replaces.
The change each caller needs
bans: New(rules Rules, held *table.Table[netip.Prefix, []Ban]) *Ledger.
Making, lifting and checking a ban, and AddLookup, get the netblock's bans, change a copy and put it.
dropOne takes the first netblock in All() that holds a ban not made by an admin.
ratelimit: New(limits Limits, clients *table.Table[netip.Prefix, Client]) *Limiter. Counting gets the client, changes it and puts it. Requests, Client and Len read the table.
lookup: Params.Answers. Using an answer gets it. A new answer is put.
reputation:
Params.Lists, Params.BlocklistNetblocks, Params.ASNLimitPercents and Params.CrowdSecDecisions;
DNSBLParams.Verdicts;
AbuseIPDBParams.Scores and AbuseIPDBParams.ChecksSpent;
new key types ListNetblock and ListASN, and DNSBLQuery, today's unexported query.
Fetch failure counts are not stored, as today.
alerts:
Params.Alerts, Params.Waiting, Params.Cooldowns and Params.Hours;
new exported types Waiting, CooldownKey (today's cooldownKey) and Detail.
A destination's sender takes its oldest row in Waiting, sends the alert, then deletes the row, and the alert once no destination waits for it.
Every place that raises an alert builds an alerts.Detail in place of a map: discriminator for bans, the Core Rule Set and reputation; anomaly; lookup; reputation; rules; and sww for a file that cannot be written.
anomaly: Params.Counters. key becomes the exported Key.
discriminator: Params.Store, from which it hands the parts their tables.
writeLine also calls Store.AddRequest for a request's last line.
It reads X-Sww-Answer-Time-Limit-Ms and logs a late answer, as under errors.db.
BanEntry and BanEntries, the ban endpoints' answer form, move here from state.
The Server fields that only handed parts to state.Load go.
rproxy: sends X-Sww-Answer-Time-Limit-Ms with each question.
metrics:
StateFileWritten(file string, size int64, err error, dropped int). The file label names a database file. Its size is the file's with its WAL.
New: sww_state_file_entries_dropped_total, by file, the log entries dropped with too many waiting.
The two edit methods go.
config: the two settings go, and SWW_REQUEST_LOG_DATABASE_MAX_AGE comes.
sww:
It opens the store first. An error exits 1 with "cannot use the state", naming the file.
It wraps the process log's handler with Store.ErrorHandler.
It builds the alert queue and the server with the store's tables.
It runs Store.Run with a written that updates the metrics, and logs and alerts a failure. A failure of errors.db is logged on stdout only.
It calls Store.Close at an orderly stop, where WriteAll is today.
Docs:
docs/state-files.md lists the files and tables above, keeping today's field descriptions.
SPEC.md: a new "Persistent state", and a short "Error log" section.
SPEC.md also changes the "State files" settings, the editing paragraph of "Bans" and its mentions of bans.json, and "State" under "Deployment": the volume can now hold about 1 GiB.
SPEC.md "Request log" gets the request database, and "The question" the new header.
README.md, docs/settings.md, docs/metrics.md, and the GET /_sww/bans and GET /_sww/clients/... lines of docs/admin-endpoints.md follow.
go.mod: adds modernc.org/sqlite.
hashicorp/golang-lru/v2 stays, for internal/table and the reports discriminator waits for.
fsnotify stays, for the rule files and the lookup database.
Tests
internal/table:
Past its limit, a table drops the value used longest ago. A Get counts as a use.
All yields the value used longest ago first.
changed gets each put, delete and drop. It gets none of the values given to New, only the values they drop.
internal/db:
Open creates a file with mode 0600 and its tables, and records migrations 0 and 1. A second Open applies nothing.
Open fails on a read-only file, a read-only directory, and a file that is not a database, naming the file.
Foreign keys are on: deleting a list deletes its entries.
internal/store:
Every table's values survive Close and a new Open, every field included. Each field that can be NULL or empty is tested both ways.
A change is on disk without Close, once written reports its file.
A key changed many times while the writer is held up is written once, with its last value.
Values are read back in the order each table's section gives. A lower limit at reopen drops the oldest.
A row that does not read fails Open, naming the file, the table and the key.
A failed write is reported, keeps its changes but those since replaced, and writes them at the next try. The test holds the file's write lock from a second connection, then lets it go.
requests.db and errors.db lose the rows past their age and past their count, with their child rows. The test lowers the counts.
Past 100,000 entries waiting, a log entry is dropped and counted.
ErrorHandler records a warning with its attributes, records nothing at info, and records warnings while SWW_LOG_LEVEL is error.
Each part, replacing today's snapshot_test.go files, checks that its state survives a reopen of the store, and:
bans: an admin's ban is never dropped. The drop takes the netblock seen longest ago.
reputation: a fetch that changes one line of a list writes only that entry. Lists of URLs, and verdicts of zones, that no setting names are deleted at start.
alerts: alerts waiting are sent after a reopen, each stored once for all its destinations. The JSON sent for each event matches today's.
discriminator:
A question's report line becomes a row of requests.db. Its decision line does not.
An answer later than X-Sww-Answer-Time-Limit-Ms is logged as a warning. A question without the header is refused with 400.
rproxy: each question carries X-Sww-Answer-Time-Limit-Ms, holding SWRP_TIMEOUT_MS.
internal/sww:
An orderly stop and start keeps a ban, a client's counts and an alert waiting.
A state directory that cannot be written stops the start with exit status 1.
Disclosures
Rule this cuts against: state well under about 100 MiB is kept in memory as hand-editable JSON, and SQLite for it is a review defect. Carried out on sneak's order on this issue.
Judgement call: REPO_POLICIES.md describes the migrations of one database. With thirteen, each file's schema has a directory of its own under internal/db/migrations/, and they share one 000_migration.sql.
Judgement call: a list is stored as the entries it holds, not the lines fetched. The CrowdSec list is fetched every minute and its lines change each time, while its decisions mostly do not; entries let a fetch write only what changed.
Judgement call: an alert's detail is one set of columns in alerts, NULL where the event has no such field, not a table per event. One table is less code; a table per event would have no NULL columns.
Judgement call: keeping alerts waiting across a restart costs most of the alert schema. Not keeping them would drop a designed feature, which needs sneak's ruling on its own issue, so this spec keeps them.
Judgement call: requests.db keeps one row per request, from its last line. A question whose report never comes has none.
Judgement call: errors.db records every warning and error the process logs, not a chosen list, so that nothing new needs wiring to be recorded.
Judgement call: the time limit travels in a new required header, since discriminator cannot know it otherwise.
Not checked: the disk-write figures are worked out from SQLite's page size and the changes per request. Nothing was measured.
Model: opus-5-5
**Spec, revised: state in SQLite, behind one store** (for sneak's approval; nothing is built before he approves)
This replaces the spec at https://git.eeqj.de/sneak/smallwebwaf/issues/217#issuecomment-170478, after sneak's review at https://git.eeqj.de/sneak/smallwebwaf/issues/217#issuecomment-170544 and https://git.eeqj.de/sneak/smallwebwaf/issues/217#issuecomment-170551. Read from `next` at `42e06bf`.
- A new package, `internal/store`, holds every kind of state in memory. It reads the state from disk once, at start.
- Each change is written to disk as it is made, by one goroutine.
- The state is in 11 SQLite files in `SWW_STATE_DIR`. Tables joined by a foreign key share a file; every other table has a file of its own.
- Every column holds one value. Nothing in the database is JSON.
- Two more files are written and never read back:
- `requests.db`: the last 24 hours of requests, or the last 1,000,000, whichever is fewer;
- `errors.db`: `discriminator`'s own warnings and errors, for the last 7 days or the last 100,000, whichever is fewer.
- The parts that keep state only get, put and delete values in tables. They know nothing of the files, the writing, or how a full table drops values.
- `internal/state`, the five JSON files, editing state while `discriminator` runs, and two settings go away. One setting comes: `SWW_REQUEST_LOG_DATABASE_MAX_AGE`.
## Writing as changes happen
sneak asked why the earlier spec wrote on an interval, and whether one goroutine reading from a channel could write each change as it happens.
- Yes. Nothing prevents it, and this spec does it.
- The interval only cut disk writes. A client's row changes with each of its requests. An interval writes it once per interval instead of once per change. What writing at once costs is under "The catch: disk writes".
- One goroutine writes every file. A change wakes it through a channel. It writes every change then waiting, in one transaction per file, then waits again.
- While it is idle, a change is written at once, in a transaction of its own.
- While it is busy, the changes made meanwhile wait and go into the next transaction together. A row changed several times meanwhile is written once, with its last value.
- The channel carries only the wake-up. The changes wait beside it: for each key changed, its latest value, or that it was deleted.
- A channel carrying the changes themselves can fill up. Then either a request waits for the disk, or a change is lost.
- One waiting change per key does neither. A key changed a thousand times waits as one change.
- The channel holds one wake-up. A wake-up sent while one already waits is dropped, since one is enough. So a full channel never holds anything up.
- The two logs have no keys, so their entries wait in a list. Past 100,000 entries waiting in one log, a new entry is dropped and counted in the metrics. That happens only while the disk is failing or far behind.
## The catch: disk writes
SQLite writes whole pages, so writing each change at once costs more disk writes than an interval does.
- A commit writes each 4 KiB page it changed to the WAL. SQLite later copies the page into the file. A change made alone costs at least 4 KiB, however small it is.
- At low traffic, each change is a commit of its own. An allowed request, at the default settings:
- changes its client's row about three times: once when counted at the question, and for its bytes and its history at the report;
- adds one row to `requests.db`, with its headers: about three pages.
- That is about 7 page writes, about 28 KiB, per request:
- at one request a second, about 2.3 GiB a day;
- at ten a second, about 23 GiB a day.
- The request log alone is about 1 GiB a day for each request a second.
- An anomaly threshold that is set adds up to four pages per request: the client, its netblock, its AS number and the whole service each have a counter.
- Under heavier load, the writer groups what waits into one commit, and a page shared by rows in it is written once. So the cost grows more slowly than the traffic.
- Today, at the defaults, the JSON files cost about 2 to 4 GiB a day, whatever the traffic.
- The lever, if this is too much: a pause of about a second before each write. Changes would then be grouped, and a row changed several times in that second written once. This spec has no pause.
## Packages and driver
- `internal/table`: the in-memory `Table` that every part holds its state in. It knows nothing of files. It keeps the order of use with `hashicorp/golang-lru/v2/simplelru`, as the parts do today.
- `internal/store`: opens the files, reads them into tables at start, writes each change, and keeps the two logs. Only `sww` and `discriminator` import it. The parts get their tables from them.
- `internal/db`: opens one SQLite file and applies its schema. Only `internal/store` imports it. It exists because `REPO_POLICIES.md` puts the schema files in `internal/db/migrations/`, embedded in the binary.
- `internal/store` imports the parts for their types, as `internal/state` does today. So the parts cannot import it: they import `internal/table` instead. That is why the table is a package of its own.
- Driver: `modernc.org/sqlite`, pure Go with no CGO, as the Go package defaults require, through `database/sql`, with one connection per file.
## The files
All in `SWW_STATE_DIR` (default `/var/lib/sww`). Each is named after its first table, and has SQLite's `-wal` and `-shm` files beside it while open.
- The state, read at start:
- `bans.db`
- `clients.db`
- `geojs_answers.db`
- `lists.db`
- `dnsbl_verdicts.db`
- `abuseipdb_scores.db`
- `abuseipdb_checks_spent.db`
- `alerts.db`
- `alert_cooldowns.db`
- `alert_hours.db`
- `anomaly_counters.db`
- The logs, never read back:
- `requests.db`
- `errors.db`
Settings on open:
- `journal_mode=WAL` and `synchronous=NORMAL`. A crash or a hard kill never damages a file.
- A hard kill of the process loses only the changes not yet written, normally none.
- A power loss can also lose the last commits the system had not yet put on the disk: up to about 30 seconds of them, with Linux's defaults.
- `foreign_keys=ON`.
- `db.Open` creates a missing file with mode `0600` before SQLite opens it, since the files hold visitors' addresses. SQLite gives the `-wal` and `-shm` files the same mode.
- The image's entrypoint already gives `SWW_STATE_DIR` to the `sww` user.
Sizes at the defaults, roughly:
- `requests.db`: up to about 1 GiB at 1,000,000 rows.
- `errors.db`: up to about 30 MiB.
- `clients.db`: about 8 MiB. `bans.db`: about 3 MiB.
- The others: a few MiB or less.
SQLite reuses freed pages, so a file never shrinks, and never grows past its fullest.
## Schema
`internal/db/migrations/000_migration.sql` is applied to every file first. It holds only the tracking table, as `REPO_POLICIES.md` requires:
```sql
CREATE TABLE IF NOT EXISTS migrations (
version INTEGER PRIMARY KEY, -- the file's number: 1 for 001_schema.sql
applied_at INTEGER NOT NULL -- when it was applied
) STRICT;
```
Each file's own tables are in `internal/db/migrations/NAME/001_schema.sql`, such as `clients/001_schema.sql`.
In every table:
- Every table is `STRICT`.
- A time is an `INTEGER`: milliseconds since 1970, in UTC.
- An address or a netblock is `TEXT`, as Go's `netip` writes it, such as `192.0.2.1/32` or `2001:db8::/64`.
- An AS number is `TEXT`, such as `AS64496`.
- Yes or no is an `INTEGER`, 0 or 1.
- Text that can be empty is `''`, as in Go.
- `NULL` means a number, a time or a field does not apply.
- A key column is never `NULL`: a part of a key that does not apply is `''`.
- Counters share one layout. For a window and what it counts, such as `minute_requests`, three columns:
- `_start`: when the bucket under way began, `NULL` while it is empty;
- `_current`: the count in it;
- `_previous`: the count in the bucket before it.
Below each file, "Held as" names the `Store` field that holds it, its limit, and the order its values are read in at start.
### `bans.db`
```sql
-- One row per ban. A netblock's bans are numbered from 0, oldest first.
CREATE TABLE bans (
netblock TEXT NOT NULL,
position INTEGER NOT NULL,
start INTEGER NOT NULL,
expires INTEGER, -- NULL: permanent
cause TEXT NOT NULL CHECK (cause IN ('limit', 'attack', 'admin', 'crowdsec')),
reason TEXT NOT NULL,
lifted INTEGER, -- NULL: not lifted
-- The notes:
asn TEXT NOT NULL,
as_name TEXT NOT NULL,
country TEXT NOT NULL,
limit_kind TEXT CHECK (limit_kind IN ('requests', 'bytes', 'refusals')),
limit_value INTEGER,
limit_window TEXT CHECK (limit_window IN ('minute', 'hour', 'day')),
limit_count REAL,
limit_percent INTEGER,
limit_percent_setting TEXT,
rule_id TEXT,
rule_target TEXT,
trap_path TEXT,
request_time INTEGER, -- NULL: no request, as for an admin's ban
request_method TEXT NOT NULL,
request_host TEXT NOT NULL,
request_path TEXT NOT NULL, -- with its query string
request_status INTEGER NOT NULL, -- 0: nothing was sent
request_user_agent TEXT NOT NULL,
requests INTEGER NOT NULL,
refused INTEGER NOT NULL,
earlier_limit_bans INTEGER NOT NULL,
earlier_attack_bans INTEGER NOT NULL,
earlier_admin_bans INTEGER NOT NULL,
earlier_crowdsec_bans INTEGER NOT NULL,
PRIMARY KEY (netblock, position)
) STRICT;
-- The reputation sources that listed the client, in the notes' order.
CREATE TABLE ban_reputation_hits (
netblock TEXT NOT NULL,
ban_position INTEGER NOT NULL,
position INTEGER NOT NULL,
source TEXT NOT NULL, -- a list's URL, a DNSBL zone with its key masked, or abuseipdb
score INTEGER, -- AbuseIPDB's score; NULL for the others
PRIMARY KEY (netblock, ban_position, position),
FOREIGN KEY (netblock, ban_position) REFERENCES bans (netblock, position) ON DELETE CASCADE
) STRICT;
```
Held as `Bans`, a `table.Table[netip.Prefix, []bans.Ban]`: each netblock's bans, oldest first.
- No limit. The ledger drops bans itself, as SPEC "Bans" says: past `SWW_MAX_BANS` of its own, the earliest ban of the netblock that has gone longest without a request. An admin's ban is never dropped.
- Read in the order of each netblock's latest ban start, as today.
### `clients.db`
```sql
CREATE TABLE clients (
client TEXT PRIMARY KEY, -- 192.0.2.1/32, or an IPv6 group such as 2001:db8::/64
minute_requests_start INTEGER,
minute_requests_current INTEGER NOT NULL,
minute_requests_previous INTEGER NOT NULL,
hour_requests_start INTEGER,
hour_requests_current INTEGER NOT NULL,
hour_requests_previous INTEGER NOT NULL,
day_requests_start INTEGER,
day_requests_current INTEGER NOT NULL,
day_requests_previous INTEGER NOT NULL,
minute_bytes_start INTEGER,
minute_bytes_current INTEGER NOT NULL,
minute_bytes_previous INTEGER NOT NULL,
hour_bytes_start INTEGER,
hour_bytes_current INTEGER NOT NULL,
hour_bytes_previous INTEGER NOT NULL,
day_bytes_start INTEGER,
day_bytes_current INTEGER NOT NULL,
day_bytes_previous INTEGER NOT NULL,
minute_refusals_start INTEGER,
minute_refusals_current INTEGER NOT NULL,
minute_refusals_previous INTEGER NOT NULL,
-- The history:
first_seen INTEGER, -- NULL until a request of its has ended
last_seen INTEGER,
asn TEXT NOT NULL, -- as last looked up
as_name TEXT NOT NULL,
country TEXT NOT NULL,
looked_up INTEGER, -- NULL: never looked up
requests INTEGER NOT NULL,
forwarded INTEGER NOT NULL,
refused INTEGER NOT NULL,
request_bytes INTEGER NOT NULL,
response_bytes INTEGER NOT NULL,
responses_1xx INTEGER NOT NULL,
responses_2xx INTEGER NOT NULL,
responses_3xx INTEGER NOT NULL,
responses_4xx INTEGER NOT NULL,
responses_5xx INTEGER NOT NULL, -- every status from 500 up
limit_offences INTEGER NOT NULL,
attack_offences INTEGER NOT NULL,
rule_blocked_offences INTEGER NOT NULL,
waf_blocked_offences INTEGER NOT NULL
) STRICT;
```
Held as `Clients`, a `table.Table[netip.Prefix, ratelimit.Client]`. At most `SWW_MAX_TRACKED_CLIENTS`. Read in the order of `last_seen`.
### `geojs_answers.db`
```sql
CREATE TABLE geojs_answers (
client TEXT PRIMARY KEY,
asn TEXT NOT NULL, -- '' when GeoJS knows none
as_name TEXT NOT NULL,
country TEXT NOT NULL, -- '' when GeoJS cannot place the client
answered INTEGER NOT NULL
) STRICT;
```
Held as `GeoJSAnswers`, a `table.Table[netip.Prefix, lookup.Answer]`. At most 100,000. Read in the order of `answered`. `Answer.Used` goes: the table keeps the order of use.
### `lists.db`
```sql
-- One row per list fetched from a URL: each blocklist, SWW_ASN_LIMIT_PERCENT_URL
-- and the CrowdSec decision list.
CREATE TABLE lists (
url TEXT PRIMARY KEY,
tried INTEGER NOT NULL, -- the last fetch, failed or not
fetched INTEGER -- the last good copy; NULL while no fetch has succeeded
) STRICT;
-- What the last good copy of each list says.
CREATE TABLE blocklist_netblocks (
url TEXT NOT NULL REFERENCES lists (url) ON DELETE CASCADE,
netblock TEXT NOT NULL,
PRIMARY KEY (url, netblock)
) STRICT;
CREATE TABLE asn_limit_percents (
url TEXT NOT NULL REFERENCES lists (url) ON DELETE CASCADE,
asn TEXT NOT NULL,
percent INTEGER NOT NULL CHECK (percent BETWEEN 0 AND 100),
PRIMARY KEY (url, asn)
) STRICT;
CREATE TABLE crowdsec_decisions (
url TEXT NOT NULL REFERENCES lists (url) ON DELETE CASCADE,
netblock TEXT NOT NULL,
expires INTEGER NOT NULL, -- of the decision on the netblock that ends last
scenario TEXT NOT NULL,
PRIMARY KEY (url, netblock)
) STRICT;
```
Held as:
- `Lists`, a `table.Table[string, reputation.List]`;
- `BlocklistNetblocks`, a `table.Table[reputation.ListNetblock, struct{}]`;
- `ASNLimitPercents`, a `table.Table[reputation.ListASN, int64]`;
- `CrowdSecDecisions`, a `table.Table[reputation.ListNetblock, reputation.Decision]`.
None has a limit. The order they are read in does not matter.
- A good fetch is parsed as today, then compared with the table. The entries added or changed are put, and those gone are deleted. An unchanged list writes only its `lists` row.
- `List.Lines` goes. The copy as fetched, comment lines included, is no longer kept; what it says is.
- At start, the part deletes the lists of URLs no setting names, with their entries.
- The part works out the netblock lengths in each list once, at start, and keeps them up to date as entries change.
### `dnsbl_verdicts.db`
```sql
CREATE TABLE dnsbl_verdicts (
zone TEXT NOT NULL, -- as SWW_DNSBL_ZONES names it
client TEXT NOT NULL, -- the address asked about
listed INTEGER NOT NULL CHECK (listed IN (0, 1)),
fetched INTEGER NOT NULL,
PRIMARY KEY (zone, client)
) STRICT;
```
Held as `DNSBLVerdicts`, a `table.Table[reputation.DNSBLQuery, reputation.Verdict]`. At most 100,000. Read in the order of `fetched`. At start, the part deletes the verdicts of zones `SWW_DNSBL_ZONES` does not name.
### `abuseipdb_scores.db`
```sql
CREATE TABLE abuseipdb_scores (
client TEXT PRIMARY KEY,
score INTEGER NOT NULL CHECK (score BETWEEN 0 AND 100),
fetched INTEGER NOT NULL
) STRICT;
```
Held as `AbuseIPDBScores`, a `table.Table[netip.Prefix, reputation.Score]`. At most 100,000. Read in the order of `fetched`.
### `abuseipdb_checks_spent.db`
```sql
CREATE TABLE abuseipdb_checks_spent (
day INTEGER PRIMARY KEY, -- the first moment of the UTC day
spent INTEGER NOT NULL -- checks made that day
) STRICT;
```
Held as `AbuseIPDBChecksSpent`, a `table.Table[time.Time, int]`. A day's first check deletes the rows of earlier days.
### `alerts.db`
```sql
-- One row per alert waiting for a destination. The detail's columns, from mode on,
-- are NULL for an event without that field; docs/alerts.md lists them by event.
CREATE TABLE alerts (
id INTEGER PRIMARY KEY, -- rising, in the order raised
time INTEGER NOT NULL,
event TEXT NOT NULL CHECK (event IN ('ban', 'permanent_ban', 'anomaly',
'waf_block', 'reputation_hit', 'source_failure', 'file_error', 'summary')),
host TEXT NOT NULL,
client TEXT NOT NULL,
netblock TEXT NOT NULL,
asn TEXT NOT NULL,
as_name TEXT NOT NULL,
country TEXT NOT NULL,
reason TEXT NOT NULL,
suppressed_repeats INTEGER NOT NULL,
mode TEXT, -- ban, permanent_ban, waf_block: observe or detect
ban_cause TEXT, -- ban and permanent_ban, with the ban's notes:
ban_expires INTEGER, -- NULL for a permanent ban
limit_kind TEXT,
limit_value INTEGER,
limit_window TEXT,
limit_count REAL,
limit_percent INTEGER,
limit_percent_setting TEXT,
rule_id TEXT,
rule_target TEXT,
trap_path TEXT,
request_time INTEGER,
request_method TEXT, -- waf_block too
request_host TEXT,
request_path TEXT, -- waf_block too
request_status INTEGER,
request_user_agent TEXT,
requests INTEGER,
refused INTEGER,
earlier_limit_bans INTEGER,
earlier_attack_bans INTEGER,
earlier_admin_bans INTEGER,
earlier_crowdsec_bans INTEGER,
waf_score INTEGER, -- waf_block
source TEXT, -- reputation_hit, source_failure
abuseipdb_score INTEGER, -- reputation_hit by AbuseIPDB
error TEXT, -- source_failure, file_error
asking_again_in INTEGER, -- source_failure by GeoJS, in milliseconds
file TEXT, -- file_error
anomaly_scope TEXT, -- anomaly
anomaly_kind TEXT,
anomaly_window TEXT,
anomaly_count REAL,
anomaly_threshold INTEGER,
watch_name TEXT, -- anomaly in the scope watch
summary_hour INTEGER, -- summary: the hour summed up
held_back INTEGER -- summary: the alerts held back in it
) STRICT;
CREATE TABLE alerts_waiting (
destination TEXT NOT NULL CHECK (destination IN ('webhook', 'slack', 'ntfy')),
alert_id INTEGER NOT NULL REFERENCES alerts (id) ON DELETE CASCADE,
PRIMARY KEY (destination, alert_id)
) STRICT;
-- waf_block: the Core Rule Set rules that matched, in order.
CREATE TABLE alert_waf_rule_ids (
alert_id INTEGER NOT NULL REFERENCES alerts (id) ON DELETE CASCADE,
position INTEGER NOT NULL,
rule_id INTEGER NOT NULL,
PRIMARY KEY (alert_id, position)
) STRICT;
-- ban and permanent_ban: the reputation sources in the ban's notes.
CREATE TABLE alert_reputation_hits (
alert_id INTEGER NOT NULL REFERENCES alerts (id) ON DELETE CASCADE,
position INTEGER NOT NULL,
source TEXT NOT NULL,
score INTEGER,
PRIMARY KEY (alert_id, position)
) STRICT;
-- summary: the alerts held back, by event.
CREATE TABLE alert_summary_events (
alert_id INTEGER NOT NULL REFERENCES alerts (id) ON DELETE CASCADE,
event TEXT NOT NULL,
count INTEGER NOT NULL,
PRIMARY KEY (alert_id, event)
) STRICT;
```
Held as `Alerts`, a `table.Table[int64, alerts.Alert]`, and `AlertsWaiting`, a `table.Table[alerts.Waiting, struct{}]`. Neither table has a limit: the queue keeps at most 1,000 alerts waiting per destination, as today.
- An alert is stored once, however many destinations wait for it. Sending it to one deletes that destination's row. The last one sent deletes the alert.
- A destination's queue is its rows in `alerts_waiting`, oldest first.
- A ban's notes take their AS number, AS name and country from the alert's own, which are the same.
- `alerts.Alert.Detail` becomes a struct with a field for each detail `docs/alerts.md` lists, in place of `map[string]any`. The JSON sent has the same fields and values.
- At start, the part deletes the rows of destinations that are not set, as today.
### `alert_cooldowns.db`
```sql
CREATE TABLE alert_cooldowns (
event TEXT NOT NULL,
netblock TEXT NOT NULL,
file TEXT NOT NULL,
source TEXT NOT NULL,
scope TEXT NOT NULL,
asn TEXT NOT NULL,
name TEXT NOT NULL,
sent INTEGER NOT NULL, -- the last alert let through
suppressed_repeats INTEGER NOT NULL, -- the repeats held back since
PRIMARY KEY (event, netblock, file, source, scope, asn, name)
) STRICT;
```
Held as `AlertCooldowns`, a `table.Table[alerts.CooldownKey, alerts.Cooldown]`. No limit: the end of each hour deletes the cooldowns that have run out, as today.
### `alert_hours.db`
```sql
CREATE TABLE alert_hours (
start INTEGER PRIMARY KEY, -- the hour under way, by the clock
sent INTEGER NOT NULL -- the alerts let through in it
) STRICT;
CREATE TABLE alert_hour_held_back (
hour INTEGER NOT NULL REFERENCES alert_hours (start) ON DELETE CASCADE,
event TEXT NOT NULL,
count INTEGER NOT NULL, -- the alerts held back past SWW_ALERT_MAX_PER_HOUR
PRIMARY KEY (hour, event)
) STRICT;
```
Held as `AlertHours`, a `table.Table[time.Time, alerts.Hour]`: one value, the hour under way. Its end sends the summary, deletes it and puts the next.
### `anomaly_counters.db`
```sql
CREATE TABLE anomaly_counters (
scope TEXT NOT NULL CHECK (scope IN ('client', 'net', 'asn', 'total', 'watch')),
netblock TEXT NOT NULL, -- '' for asn and total
asn TEXT NOT NULL, -- '' but for asn
name TEXT NOT NULL, -- '' but for watch
minute_requests_start INTEGER,
minute_requests_current INTEGER NOT NULL,
minute_requests_previous INTEGER NOT NULL,
hour_requests_start INTEGER,
hour_requests_current INTEGER NOT NULL,
hour_requests_previous INTEGER NOT NULL,
minute_bytes_start INTEGER,
minute_bytes_current INTEGER NOT NULL,
minute_bytes_previous INTEGER NOT NULL,
hour_bytes_start INTEGER,
hour_bytes_current INTEGER NOT NULL,
hour_bytes_previous INTEGER NOT NULL,
PRIMARY KEY (scope, netblock, asn, name)
) STRICT;
```
Held as `AnomalyCounters`, a `table.Table[anomaly.Key, anomaly.Counter]`. At most 20,000. Read in the order of each counter's latest `_start`.
### `requests.db`
```sql
-- One row per request: the last line discriminator writes about it. The columns are
-- the fields of docs/request-log.md, but type, instance and discriminator_line,
-- which are the same on every row.
CREATE TABLE requests (
id INTEGER PRIMARY KEY, -- rising, in the order written
time INTEGER NOT NULL,
request_id TEXT NOT NULL,
client_ip TEXT NOT NULL,
peer_ip TEXT NOT NULL,
forwarded_for TEXT NOT NULL,
client_group TEXT NOT NULL,
asn TEXT NOT NULL,
as_name TEXT NOT NULL,
country TEXT NOT NULL,
method TEXT NOT NULL,
scheme TEXT NOT NULL,
host TEXT NOT NULL,
path TEXT NOT NULL,
query TEXT NOT NULL,
protocol TEXT NOT NULL,
status INTEGER NOT NULL,
request_bytes INTEGER NOT NULL,
response_bytes INTEGER NOT NULL,
referer TEXT NOT NULL,
user_agent TEXT NOT NULL,
content_type TEXT NOT NULL,
content_length INTEGER, -- NULL: none announced
has_authorization INTEGER NOT NULL CHECK (has_authorization IN (0, 1)),
has_cookie INTEGER NOT NULL CHECK (has_cookie IN (0, 1)),
response_content_type TEXT NOT NULL,
aborted INTEGER NOT NULL CHECK (aborted IN (0, 1)),
action TEXT NOT NULL,
would_action TEXT NOT NULL,
limit_percent INTEGER,
limit_percent_setting TEXT NOT NULL,
bytes_percent INTEGER,
bytes_percent_setting TEXT NOT NULL,
minute_requests REAL, -- the counts; NULL when not counted
hour_requests REAL,
day_requests REAL,
minute_bytes REAL,
hour_bytes REAL,
day_bytes REAL,
limit_hit TEXT NOT NULL,
waf_score INTEGER,
offence TEXT NOT NULL,
ban_expires INTEGER, -- NULL: no ban, or a permanent one
ban_permanent INTEGER NOT NULL CHECK (ban_permanent IN (0, 1)),
duration_total REAL NOT NULL, -- milliseconds, as in the log
duration_checks REAL,
duration_waf REAL,
duration_upstream_total REAL
) STRICT;
CREATE INDEX requests_time ON requests (time);
-- The headers SWW_LOG_REQUEST_HEADERS names.
CREATE TABLE request_headers (
request INTEGER NOT NULL REFERENCES requests (id) ON DELETE CASCADE,
name TEXT NOT NULL, -- in lower case
value TEXT NOT NULL,
PRIMARY KEY (request, name)
) STRICT;
-- The rule file rules that matched, in order.
CREATE TABLE request_rule_ids (
request INTEGER NOT NULL REFERENCES requests (id) ON DELETE CASCADE,
position INTEGER NOT NULL,
rule_id TEXT NOT NULL,
PRIMARY KEY (request, position)
) STRICT;
-- The Core Rule Set rules that matched, in order.
CREATE TABLE request_waf_rule_ids (
request INTEGER NOT NULL REFERENCES requests (id) ON DELETE CASCADE,
position INTEGER NOT NULL,
rule_id INTEGER NOT NULL,
PRIMARY KEY (request, position)
) STRICT;
-- The reputation sources the client is on, in the log's order.
CREATE TABLE request_reputation (
request INTEGER NOT NULL REFERENCES requests (id) ON DELETE CASCADE,
position INTEGER NOT NULL,
source TEXT NOT NULL,
PRIMARY KEY (request, position)
) STRICT;
```
- Written: `discriminator` adds a row as it writes a request's last line. That is the `report` line of a question, or the one line of a request to its own endpoints. A question whose report never comes has no row; its `decision` line is on stdout, as today.
- Removed: once a minute, the writer deletes the rows older than `SWW_REQUEST_LOG_DATABASE_MAX_AGE`, and the rows beyond the newest 1,000,000. Their headers, rule ids and sources go with them. Between two such deletions, the table can hold a minute's rows over either bound.
- The setting: `SWW_REQUEST_LOG_DATABASE_MAX_AGE` (default `24h`), how long a request is kept in `requests.db`. A duration above zero. A value that is set but invalid stops the start. The 1,000,000 is a constant.
- Read: nothing reads it while `discriminator` runs. It is for the web analytics interface to come, which will add its own queries. Meanwhile any SQLite tool can read it, even while `discriminator` runs.
- Size: about 0.5 to 1 KiB per row, with its index and headers.
### `errors.db`
```sql
-- discriminator's own messages at warn or error.
CREATE TABLE errors (
id INTEGER PRIMARY KEY, -- rising, in the order logged
time INTEGER NOT NULL,
level TEXT NOT NULL CHECK (level IN ('WARN', 'ERROR')),
message TEXT NOT NULL
) STRICT;
CREATE INDEX errors_time ON errors (time);
-- Each message's attributes, such as error, source, url or file.
CREATE TABLE error_attributes (
error INTEGER NOT NULL REFERENCES errors (id) ON DELETE CASCADE,
position INTEGER NOT NULL,
name TEXT NOT NULL,
value TEXT NOT NULL,
PRIMARY KEY (error, position)
) STRICT;
```
- Recorded: every message the process log gets at `warn` or `error`, whatever `SWW_LOG_LEVEL` says. Its attributes are kept, but `type` and `instance`, which every message has.
- Where: `Store.ErrorHandler` wraps the process log's handler. So nothing changes where errors are logged, and every error logged is recorded.
- Each example sneak gave is logged already, so it is recorded:
- GeoJS failing or timing out, in `lookup`, and the lookup database that cannot be read;
- AbuseIPDB checks failing, timing out or refused, DNSBL queries failing and list fetches failing, in `reputation`;
- besides those, an alert destination failing, the syslog server failing, a rule file that does not parse, and a database file that cannot be written.
- routewatch: `sww` has no routewatch lookup. Its failures will be recorded the same way once it has one.
- New: an answer too late for `rproxy`. `rproxy` stops waiting after `SWRP_TIMEOUT_MS`, which `discriminator` does not know today. So:
- `rproxy` sends `SWRP_TIMEOUT_MS` with every question, in a new header, `X-Sww-Answer-Time-Limit-Ms`.
- `discriminator` removes the header from the request the checks see, as it does `X-Sww-Client`.
- A question whose header is missing, or not a whole number above zero, is refused with `400`, as one with a bad `X-Request-ID` is.
- Once `discriminator` has written its answer, it compares the time since it read the question's headers with the header. If that is longer, it logs a warning, "answered after rproxy's time limit", with `request_id`, the milliseconds taken and the limit.
- That time leaves out the network both ways, so an answer late by less than that goes unseen. Every answer it reports was late.
- Removed: once a minute, the rows older than 7 days, and the rows beyond the newest 100,000. Neither has a setting.
- A failure to write `errors.db` is logged on stdout only, so that it does not feed itself.
## Exported API
```go
package table
// Table holds values of type V by key, in memory. A Get or a Put of a value
// is a use of it. A table with a limit holds at most that many values: a Put
// of one more drops the value used longest ago. It is safe for concurrent use.
type Table[K comparable, V any] struct{ /* unexported */ }
// New returns a table holding values, put in the order given, and at most
// limit values, or any number when limit is 0. changed, unless nil, is called
// under the table's lock with each value put, and with each key deleted or
// dropped, deleted then being true. The values given to New are not changes,
// but a value they drop past the limit is.
func New[K comparable, V any](
limit int, values iter.Seq2[K, V], changed func(key K, value V, deleted bool),
) *Table[K, V]
// Get returns the value under key, and whether there is one. A slice or map
// in the value is shared with the table: change a copy, then Put it.
func (t *Table[K, V]) Get(key K) (V, bool)
// Put sets the value under key.
func (t *Table[K, V]) Put(key K, value V)
// Delete removes the value under key, if there is one.
func (t *Table[K, V]) Delete(key K)
// Len returns how many values the table holds.
func (t *Table[K, V]) Len() int
// All yields every key and value, the value used longest ago first. It is
// not a use. The loop must not call the table.
func (t *Table[K, V]) All() iter.Seq2[K, V]
```
```go
package store
// Params are what Open needs.
type Params struct {
// Dir is the directory of the database files (SWW_STATE_DIR).
Dir string
// MaxTrackedClients is the most clients held (SWW_MAX_TRACKED_CLIENTS).
MaxTrackedClients int
// RequestLogMaxAge is how long a request is kept in requests.db
// (SWW_REQUEST_LOG_DATABASE_MAX_AGE).
RequestLogMaxAge time.Duration
// Now tells the time, normally time.Now in UTC.
Now func() time.Time
}
// Store is sww's state, held in memory and written to the files in Dir as
// it changes, and the request and error logs. Each table belongs to one
// part, which is given it as it is made.
type Store struct {
Bans *table.Table[netip.Prefix, []bans.Ban]
Clients *table.Table[netip.Prefix, ratelimit.Client]
GeoJSAnswers *table.Table[netip.Prefix, lookup.Answer]
Lists *table.Table[string, reputation.List]
BlocklistNetblocks *table.Table[reputation.ListNetblock, struct{}]
ASNLimitPercents *table.Table[reputation.ListASN, int64]
CrowdSecDecisions *table.Table[reputation.ListNetblock, reputation.Decision]
DNSBLVerdicts *table.Table[reputation.DNSBLQuery, reputation.Verdict]
AbuseIPDBScores *table.Table[netip.Prefix, reputation.Score]
AbuseIPDBChecksSpent *table.Table[time.Time, int]
Alerts *table.Table[int64, alerts.Alert]
AlertsWaiting *table.Table[alerts.Waiting, struct{}]
AlertCooldowns *table.Table[alerts.CooldownKey, alerts.Cooldown]
AlertHours *table.Table[time.Time, alerts.Hour]
AnomalyCounters *table.Table[anomaly.Key, anomaly.Counter]
// unexported: the files, the changes waiting, the log entries waiting
}
// Open opens every database file in params.Dir, creating any that is
// missing, and reads every table of the state into memory. It reads nothing
// from requests.db and errors.db. An error names the file and, for a row
// that does not read, the table and the key.
func Open(params Params) (*Store, error)
// Write is one write of a database file, as Run reports it.
type Write struct {
// File is the file's name, such as clients.db.
File string
// Size is the file's size with its WAL, after the write.
Size int64
// Err is why the write failed, nil when it succeeded.
Err error
// Dropped is how many log entries were dropped since the file's last
// write, since too many were waiting.
Dropped int
}
// Run writes each change as it is made, and removes the logs' old rows once
// a minute, until ctx is done. It calls written after each write.
func (s *Store) Run(ctx context.Context, written func(Write))
// AddRequest adds line, the last line discriminator writes about a request,
// to requests.db. It never waits for the disk.
func (s *Store) AddRequest(line *requestlog.Line)
// ErrorHandler returns a handler that passes every record to next, and adds
// each one at level warn or above to errors.db, whatever next's level. It
// never waits for the disk.
func (s *Store) ErrorHandler(next slog.Handler) slog.Handler
// Close writes the changes and log entries still waiting, and closes every
// file. Nothing uses the store or its tables after Close.
func (s *Store) Close() error
```
```go
package db
// Open opens the SQLite file at path, creating it with mode 0600 when it is
// missing, with WAL, synchronous NORMAL and foreign keys on. In one write
// transaction it applies 000_migration.sql, then the migrations under
// migrations/schema not yet applied, such as clients/001_schema.sql.
func Open(path, schema string) (*sql.DB, error)
```
How a part uses its table, here `ratelimit` counting a request, under the `Limiter`'s own lock:
```go
// sww or discriminator, making the part
limiter := ratelimit.New(limits, s.Clients)
// counting a request
c, _ := l.clients.Get(client)
c.Client = client
c.Minute.Add(now, time.Minute, 1)
l.clients.Put(client, c)
```
Each table belongs to one part. The table's own lock keeps it safe, but a part that gets a value, changes it and puts it back holds its own lock across the three, as it does today.
## Behaviour
**The read at start**
- `sww discriminator` opens the store before anything that keeps state. `Open` opens every file, creating any that is missing, and reads each state table whole, once. It reads nothing from `requests.db` and `errors.db`.
- Each table's values are put in the order its section gives, so that a full table drops the same value after a restart as before.
- Rows beyond a table's limit, after the limit was lowered, are dropped, oldest first, and deleted at the first write.
- Each part works out once, at start, what it derives from its tables:
- the ledger, its count of bans and the netblock lengths banned;
- the lists, the netblock lengths in each list;
- the alert queue, its next alert id.
- At start, the parts delete what no setting names any more: the lists of other URLs, the verdicts of other zones, and the alerts waiting for a destination that is not set.
- Nothing else is rewritten at start. A value past its time is found so when it is used, as today.
- Serving starts once every table is read. Nothing reads the state files again until the next start.
**Changes written**
- `Put` and `Delete` change memory at once, and leave the change waiting for the writer. No request waits for the disk.
- The writer works as "Writing as changes happen" says.
- Each file's waiting changes go into one transaction, from `BEGIN IMMEDIATE` to `COMMIT`.
- Its tables are written in the order its schema creates them, so that a row's parent is written before the row.
- How a change becomes rows:
- A value of one row is written with `INSERT ... ON CONFLICT DO UPDATE`.
- A value of several rows deletes the key's rows and inserts them again. Such values are a netblock's bans with their hits, and an hour with its held-back counts.
- A key deleted or dropped deletes its rows. The rows that reference them go with them.
- The writer turns values into rows, outside every table's lock.
- `Close` writes what is still waiting. An orderly stop loses nothing.
**Limits and dropping**
- A table with a limit drops the value used longest ago when a `Put` would take it past the limit. A `Get` or a `Put` is a use. The value leaves memory at once, and its file at the next write.
- The limits are today's: `SWW_MAX_TRACKED_CLIENTS` clients, and 100,000 GeoJS answers, 100,000 DNSBL verdicts, 100,000 AbuseIPDB scores and 20,000 anomaly counters. They move from the parts into `internal/store`, and the parts no longer know them.
- Product rules about what is kept stay in the parts:
- the ledger drops bans past `SWW_MAX_BANS`, never an admin's;
- the alert queue keeps at most 1,000 alerts waiting per destination;
- the end of each hour deletes the cooldowns that have run out.
- Two drop orders change slightly, both of caches:
- DNSBL verdicts and AbuseIPDB scores are dropped by their last use, not by when they were fetched.
- Reading a client, or a netblock's bans, for an admin endpoint now counts as a use.
**Schema versions**
- Before 1.0, each `001_schema.sql` is edited in place and no `002` is added, as `REPO_POLICIES.md` says.
- `db.Open` applies `000_migration.sql`, then each of the file's migrations not yet in `migrations`, in file order, in one write transaction.
- The runner is the few lines that layout needs, with no library. goose and `golang-migrate` each create their own tracking table, which the policy's `000_migration.sql` creates instead.
**A file that is missing, damaged or cannot be written**
- Missing: created with its schema, as on a first start.
- To forget one kind of state, an admin stops `discriminator` and removes its file, with its `-wal` and `-shm`.
- Not a database, or damaged so that reading it at start fails: `discriminator` stops with exit status 1 and an error naming the file. Starting empty would forgive every repeat offender. The same holds for `requests.db` and `errors.db`, which are opened but not read.
- A row that does not read, such as a netblock that is not one, stops the start the same way, naming the file, the table and the key. The `CHECK` constraints refuse most such edits as they are made.
- Cannot be written at start: `db.Open` takes the write lock to apply the schema, so the start stops with the error.
- A write that fails while running, from a full disk, permissions or damage:
- The transaction rolls back. Its changes wait again, but those a newer change to the same key has replaced.
- It is tried again a second later, and twice as long after each further failure, up to a minute.
- The state in memory stays correct, and requests are served as before. Meanwhile memory grows by one waiting change per key changed or dropped.
- `Run` reports the failure. `sww` logs it, counts it in the metrics, and raises a `file_error` alert naming the file, with today's cooldown per file.
- Removing a file while `discriminator` runs goes unnoticed. The state in it is lost at the next start.
**Reading and editing by hand**
- Any SQLite tool can read the files at any time, even while `discriminator` runs.
- To change state: stop `discriminator`, edit the file, start it. A change made while it runs is not read, and the next change to the same row overwrites it.
## What goes away
- `internal/state` and its tests: the five JSON files, `Load`, `Run`, `WriteAll`, `Watch`, taking in edits, `.bad` files, the checksums and the `version` check.
- Editing state while `discriminator` runs, which SPEC "Persistent state" and "Bans" designed:
- While running, an admin still adds and lifts bans through `POST /_sww/bans` and `DELETE /_sww/bans/...`.
- Keeping a ban (cause `admin`) or forgetting one (deleting its row) needs `discriminator` stopped and a SQLite tool. Endpoints for them would be a separate issue.
- Settings: `SWW_STATE_WRITE_DELAY` and `SWW_STATE_COUNTER_INTERVAL`. That settles both for the settings audit at https://git.eeqj.de/sneak/smallwebwaf/issues/202.
- Metrics: `sww_state_file_edits_taken_in_total` and `sww_state_file_edits_set_aside_total`.
- Fields and types:
- `Offences.TokenRefused`, kept only so that old `clients.json` files load;
- `lookup.Answer.Used`;
- `reputation.List.Lines` and `reputation.Checks`;
- `alerts.State`.
- In each part: `Load`, `LoadEdit`, `Changed`, `markChanged`, every `Snapshot` but the ledger's, which `GET /_sww/bans` uses, and the part's own map or LRU, which its table replaces.
## The change each caller needs
- `bans`: `New(rules Rules, held *table.Table[netip.Prefix, []Ban]) *Ledger`.
- Making, lifting and checking a ban, and `AddLookup`, get the netblock's bans, change a copy and put it.
- `dropOne` takes the first netblock in `All()` that holds a ban not made by an admin.
- `ratelimit`: `New(limits Limits, clients *table.Table[netip.Prefix, Client]) *Limiter`. Counting gets the client, changes it and puts it. `Requests`, `Client` and `Len` read the table.
- `lookup`: `Params.Answers`. Using an answer gets it. A new answer is put.
- `reputation`:
- `Params.Lists`, `Params.BlocklistNetblocks`, `Params.ASNLimitPercents` and `Params.CrowdSecDecisions`;
- `DNSBLParams.Verdicts`;
- `AbuseIPDBParams.Scores` and `AbuseIPDBParams.ChecksSpent`;
- new key types `ListNetblock` and `ListASN`, and `DNSBLQuery`, today's unexported `query`.
- Fetch failure counts are not stored, as today.
- `alerts`:
- `Params.Alerts`, `Params.Waiting`, `Params.Cooldowns` and `Params.Hours`;
- new exported types `Waiting`, `CooldownKey` (today's `cooldownKey`) and `Detail`.
- A destination's sender takes its oldest row in `Waiting`, sends the alert, then deletes the row, and the alert once no destination waits for it.
- Every place that raises an alert builds an `alerts.Detail` in place of a map: `discriminator` for bans, the Core Rule Set and reputation; `anomaly`; `lookup`; `reputation`; `rules`; and `sww` for a file that cannot be written.
- `anomaly`: `Params.Counters`. `key` becomes the exported `Key`.
- `discriminator`: `Params.Store`, from which it hands the parts their tables.
- `writeLine` also calls `Store.AddRequest` for a request's last line.
- It reads `X-Sww-Answer-Time-Limit-Ms` and logs a late answer, as under `errors.db`.
- `BanEntry` and `BanEntries`, the ban endpoints' answer form, move here from `state`.
- The `Server` fields that only handed parts to `state.Load` go.
- `rproxy`: sends `X-Sww-Answer-Time-Limit-Ms` with each question.
- `metrics`:
- `StateFileWritten(file string, size int64, err error, dropped int)`. The `file` label names a database file. Its size is the file's with its WAL.
- New: `sww_state_file_entries_dropped_total`, by file, the log entries dropped with too many waiting.
- The two edit methods go.
- `config`: the two settings go, and `SWW_REQUEST_LOG_DATABASE_MAX_AGE` comes.
- `sww`:
- It opens the store first. An error exits 1 with "cannot use the state", naming the file.
- It wraps the process log's handler with `Store.ErrorHandler`.
- It builds the alert queue and the server with the store's tables.
- It runs `Store.Run` with a `written` that updates the metrics, and logs and alerts a failure. A failure of `errors.db` is logged on stdout only.
- It calls `Store.Close` at an orderly stop, where `WriteAll` is today.
- Docs:
- `docs/state-files.md` lists the files and tables above, keeping today's field descriptions.
- `SPEC.md`: a new "Persistent state", and a short "Error log" section.
- `SPEC.md` also changes the "State files" settings, the editing paragraph of "Bans" and its mentions of `bans.json`, and "State" under "Deployment": the volume can now hold about 1 GiB.
- `SPEC.md` "Request log" gets the request database, and "The question" the new header.
- `README.md`, `docs/settings.md`, `docs/metrics.md`, and the `GET /_sww/bans` and `GET /_sww/clients/...` lines of `docs/admin-endpoints.md` follow.
- `go.mod`: adds `modernc.org/sqlite`.
- `hashicorp/golang-lru/v2` stays, for `internal/table` and the reports `discriminator` waits for.
- `fsnotify` stays, for the rule files and the lookup database.
## Tests
- `internal/table`:
- Past its limit, a table drops the value used longest ago. A `Get` counts as a use.
- `All` yields the value used longest ago first.
- `changed` gets each put, delete and drop. It gets none of the values given to `New`, only the values they drop.
- `internal/db`:
- `Open` creates a file with mode `0600` and its tables, and records migrations 0 and 1. A second `Open` applies nothing.
- `Open` fails on a read-only file, a read-only directory, and a file that is not a database, naming the file.
- Foreign keys are on: deleting a list deletes its entries.
- `internal/store`:
- Every table's values survive `Close` and a new `Open`, every field included. Each field that can be `NULL` or empty is tested both ways.
- A change is on disk without `Close`, once `written` reports its file.
- A key changed many times while the writer is held up is written once, with its last value.
- Values are read back in the order each table's section gives. A lower limit at reopen drops the oldest.
- A row that does not read fails `Open`, naming the file, the table and the key.
- A failed write is reported, keeps its changes but those since replaced, and writes them at the next try. The test holds the file's write lock from a second connection, then lets it go.
- `requests.db` and `errors.db` lose the rows past their age and past their count, with their child rows. The test lowers the counts.
- Past 100,000 entries waiting, a log entry is dropped and counted.
- `ErrorHandler` records a warning with its attributes, records nothing at `info`, and records warnings while `SWW_LOG_LEVEL` is `error`.
- Each part, replacing today's `snapshot_test.go` files, checks that its state survives a reopen of the store, and:
- `bans`: an admin's ban is never dropped. The drop takes the netblock seen longest ago.
- `reputation`: a fetch that changes one line of a list writes only that entry. Lists of URLs, and verdicts of zones, that no setting names are deleted at start.
- `alerts`: alerts waiting are sent after a reopen, each stored once for all its destinations. The JSON sent for each event matches today's.
- `discriminator`:
- A question's `report` line becomes a row of `requests.db`. Its `decision` line does not.
- An answer later than `X-Sww-Answer-Time-Limit-Ms` is logged as a warning. A question without the header is refused with `400`.
- `rproxy`: each question carries `X-Sww-Answer-Time-Limit-Ms`, holding `SWRP_TIMEOUT_MS`.
- `internal/sww`:
- An orderly stop and start keeps a ban, a client's counts and an alert waiting.
- A state directory that cannot be written stops the start with exit status 1.
## Disclosures
- Rule this cuts against: state well under about 100 MiB is kept in memory as hand-editable JSON, and SQLite for it is a review defect. Carried out on sneak's order on this issue.
- Judgement call: `REPO_POLICIES.md` describes the migrations of one database. With thirteen, each file's schema has a directory of its own under `internal/db/migrations/`, and they share one `000_migration.sql`.
- Judgement call: a list is stored as the entries it holds, not the lines fetched. The CrowdSec list is fetched every minute and its lines change each time, while its decisions mostly do not; entries let a fetch write only what changed.
- Judgement call: an alert's detail is one set of columns in `alerts`, `NULL` where the event has no such field, not a table per event. One table is less code; a table per event would have no `NULL` columns.
- Judgement call: keeping alerts waiting across a restart costs most of the alert schema. Not keeping them would drop a designed feature, which needs sneak's ruling on its own issue, so this spec keeps them.
- Judgement call: `requests.db` keeps one row per request, from its last line. A question whose report never comes has none.
- Judgement call: `errors.db` records every warning and error the process logs, not a chosen list, so that nothing new needs wiring to be recorded.
- Judgement call: the time limit travels in a new required header, since `discriminator` cannot know it otherwise.
- Not checked: the disk-write figures are worked out from SQLite's page size and the changes per request. Nothing was measured.
Model: opus-5-5
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
when items are written to the in-memory store, they are written into sqlite files.
on startup, the sqlite files are read into memory. no reads happen during normal operation.
the persistent state should be abstracted away entirely so things storing or querying don't know anything about underlying storage or LRU or anything. it should hide the in-memory live cache, as well as the full-read from disk on instantiation (which should happen once at startup).
Question: does SQLite replace the zstd-compressed JSON files you asked for on #202 (comment), four minutes earlier?
Picked up for smallwebwaf's manager once you answer: one storage layer that hides both the in-memory copy and the database. It reads everything once at start and writes each change through, and nothing else knows how state is stored.
Model: opus-5-5
yes, it does. i changed my mind.
Recorded: SQLite replaces the zstd-compressed JSON (sneak, #217 (comment)). There is no JSON state and no zstd step. Picked up for smallwebwaf's manager at its next run.
Model: opus-5-5
begin this now with opus. show me a detailed spec for the change (all table schema, module exported api shape) for approval before implementation
For the spec writer (an Opus run, by sneak's order #217 (comment)): write the detailed spec for this change as one comment on this issue, for sneak's approval. Nobody implements it before he approves. Write no code and open no PR.
Read the code on
nextfirst. The spec covers:CREATE TABLEand index statements, with each column's type, constraints and meaning. Cover every kind of state the discriminator keeps today: bans, client records, and anything else it holds in memory or writes to disk.modernc.org/sqlite, pure Go with no CGO, as sneak's Go package defaults require.Model: opus-5-5
Spec: state in SQLite, behind one store (for sneak's approval; nothing is built before he approves)
sww.db, inSWW_STATE_DIR.internal/store, holds every kind of state in memory. Each table is read whole fromsww.dbonce, at start, and never read again whilediscriminatoris running.sww.dbwithin 10 seconds, in one transaction per write.bans,ratelimit,lookup,reputation,alerts,anomaly) only get, put and delete values. They know nothing of the file, the memory copy, or how a full table drops values.internal/state, the five JSON files, their hand edits while running, and two settings go away.Read from
nextatbeb503b.Packages
internal/store: the API every part uses (below).internal/db: openssww.db, applies the schema, reads a table's rows, writes changes. Onlyinternal/storeimports it. It exists becauseREPO_POLICIES.mdputs the schema files ininternal/db/migrations/, embedded in the binary.modernc.org/sqlite, pure Go, no CGO, throughdatabase/sql, with one open connection.The file
SWW_STATE_DIR/sww.db, with SQLite'ssww.db-walandsww.db-shmbeside it while it is open.journal_mode=WAL,synchronous=NORMAL. With these a crash or hard kill never damages the file; it loses at most the last write.db.Opencreatessww.dbwith mode0600before SQLite opens it, since it holds visitors' addresses. SQLite gives the-waland-shmfiles the same mode.SWW_STATE_DIRstays, default/var/lib/sww. The image's entrypoint already gives that directory to theswwuser.Schema
internal/db/migrations/000_migration.sql(only the tracking table, asREPO_POLICIES.mdrequires):internal/db/migrations/001_schema.sql. Every table has the same three columns:key: what the row is about, such as a client's netblock. Each table's key is listed below.value: the state, as JSON.write_order: the table's count of puts when this value was last put. Rows are read back in this order, so a full table drops the same value after a restart as before it.No other index: the only statements are the read at start, the upsert by key, and the delete by key.
Each table
The value is the part's existing Go type, encoded with its existing JSON tags. Those are the fields
docs/state-files.mdlists today. A key of several fields is the JSON array of them, which keeps it unambiguous and readable.bans, kept bybans.Ledger.203.0.113.0/24.{"netblock", "last_request", "bans": [...]}: the netblock's bans oldest first, each with today's ban fields and notes, and when the netblock last sent a request.SWW_MAX_BANSof its own, it drops the earliest such ban of the netblock with the earliestlast_request. Admin bans are never dropped and not counted.clients, kept byratelimit.Limiter.192.0.2.1/32or an IPv6 group such as2001:db8::/64.ratelimit.Client, as one line ofclients.jsonholds it today.SWW_MAX_TRACKED_CLIENTS.geojs_answers, kept bylookup.GeoJS.lookup.Answer. At most 100,000.lists, kept byreputation.Lists.reputation.List(url,tried,fetched,lines).dnsbl_verdicts, kept byreputation.DNSBL.["zen.spamhaus.org","192.0.2.1"], the zone and the address. Value:reputation.Verdict. At most 100,000.SWW_DNSBL_ZONESdoes not name are deleted at start.abuseipdb_scores, kept byreputation.AbuseIPDB.reputation.Score. At most 100,000.abuseipdb_budget, kept byreputation.AbuseIPDB.today. Value:{"day", "spent"}, the checks spent on that UTC day.alert_cooldowns, kept byalerts.Queue.alerts.Cooldown.alert_hour, kept byalerts.Queue.current. Value:alerts.Hour.alerts_waiting, kept byalerts.Queue.webhook,slackorntfy. Value: the alerts waiting for it, oldest first, at most 1,000 as today.anomaly_counters, kept byanomaly.Counters.anomaly.Counter. At most 20,000.An answer, verdict or score found past its time is deleted when it is found. Otherwise, at start each value is used as it was written: the counters' buckets already start afresh when their time has passed.
Exported API
Table names are constants in the parts, never input, since SQL cannot take a table name as a parameter.
How a part uses a table, here
ratelimitcounting a request under the Limiter's own lock:Each table belongs to one part. The part's own lock makes a get, change and put one step. The table's lock only keeps the table safe while the store writes it.
Behaviour
The read at start
sww discriminatorcallsstore.Open, then builds the parts. Each part's constructor callsNewTablefor its tables, and each table reads its rows once, oldestwrite_orderfirst.sww.dbagain until the next start.Changes written
PutandDeletechange memory at once and note the key. No request waits for the disk.INSERT ... ON CONFLICT(key) DO UPDATE) of the current value, or aDELETEfor a value deleted or dropped. A key changed many times in those 10 seconds is written once.Closewrites what is left. An orderly stop loses nothing; a hard kill loses at most the last 10 seconds.internal/store, not a setting. It replacesSWW_STATE_WRITE_DELAY(10 seconds for bans) andSWW_STATE_COUNTER_INTERVAL(15 minutes for everything else).Limits and dropping
sww.dbat the next write.Getdoes not change which value is dropped; onlyPutdoes. Every part already puts a client, counter or answer each time it is used, so the order matches today's.SWW_MAX_TRACKED_CLIENTSclients, 100,000 GeoJS answers, 100,000 DNSBL verdicts, 100,000 AbuseIPDB scores, 20,000 anomaly counters, at most 1,000 alerts waiting per destination.hashicorp/golang-lru/v2moves from the parts into the store.Schema versions
001_schema.sqlis edited in place, and no002is added, asREPO_POLICIES.mdsays.db.Openapplies each file not yet inmigrations, in file order.golang-migrateeach create their own tracking table, which the policy's000_migration.sqldoes instead.A file that is missing, damaged or cannot be written
db.Opencreates it with the schema. That is a first start.discriminatorstops with exit status 1 and an error naming the file, as a broken state file does today. Starting empty would forgive every repeat offender. To start afresh, an admin removessww.db,sww.db-walandsww.db-shm.db.Openalways takes the write lock (BEGIN IMMEDIATE) to apply the migrations, so an unwritable file or directory stops the start with the error.writtengets the error. The process log has it, the failure metric counts it, and afile_erroralert namessww.db, with today's cooldown per file. The next write retries. The state in memory stays correct, and requests are served as before. Meanwhile memory grows by the keys changed or dropped.sww.dbwhilediscriminatoris running goes unnoticed: the state is lost at the next start.discriminator, editsww.dbwith any SQLite tool, start it. An edit made while it is running is not read, and a later change to the same row overwrites it.The catch: disk writes
SQLite writes only the values that changed. But the counters change with nearly every request, so on a busy site this writes more than today, not less.
What goes away
internal/stateand its tests: the five JSON files,Load,Run,WriteAll,Watch, taking in edits,.badfiles, the checksums, theversioncheck.bans.json, anddiscriminatortakes the edit in.POST /_sww/bansandDELETE /_sww/bans/....admin) and forgetting one (deleting its entry) are possible only whilediscriminatoris stopped, with a SQLite tool. Endpoints for them would be a separate issue.SWW_STATE_WRITE_DELAYandSWW_STATE_COUNTER_INTERVAL. That settles both for the settings audit at #202.sww_state_file_edits_taken_in_totalandsww_state_file_edits_set_aside_totalgo. Thefilelabel leaves the write, failure, last-write and size metrics. Size becomes the size ofsww.dbplus its WAL.Offences.TokenRefused, kept only so that oldclients.jsonfiles load.The change each caller needs:
bans:New(s *store.Store, rules Rules) (*Ledger, error). Thesimplelrufield becomes thebanstable.last_request.dropOnescans the table for the earliestlast_requestamong netblocks holding a ban not made by an admin.Load,LoadEdit,ChangedandmarkChangedgo.Snapshotstays forGET /_sww/bans.ratelimit:New(s *store.Store, limits Limits, maxClients int) (*Limiter, error).LoadandSnapshotgo.Clientgets,RequestsandLenread the table, and counting puts.lookup:Params.Store.Newreturns an error.LoadandSnapshotgo. Using an answer puts it with its newusedtime, as today.reputation:Params.Store,DNSBLParams.StoreandAbuseIPDBParams.Store.New,NewDNSBLandNewAbuseIPDBreturn an error.LoadandSnapshotgo. Fetch failure counts are not stored, as today.alerts:Params.Store.Newreturns an error.LoadandSnapshotgo. Each destination's queue is thealerts_waitingrow: add and remove get it, change a copy and put it.anomaly:Params.Store.Newreturns an error.LoadandSnapshotgo.discriminator:Params.Store.Newreturns(*Server, error).BanEntryandBanEntries, the ban endpoints' answer form, move here fromstate. TheServerfields that only handed parts tostate.Loadgo.metrics:StateFileWritten(size int, err error), with no file name. The two edit methods go.config: the two settings go.sww:store.Runwith awrittenthat updates the metrics and logs and alerts a failure.store.Closeat an orderly stop whereWriteAllis today.docs/state-files.mdis rewritten as the tables above, keeping today's field lists.SPEC.mdgets a new "Persistent state", the "State files" settings, the editing paragraph of "Bans", and the size and backup lines of "State".README.mdloses the settings rows and gets a new "State files" section.docs/settings.mdanddocs/metrics.mdfollow, as does theGET /_sww/clients/...line indocs/admin-endpoints.md.go.mod: addsmodernc.org/sqlite.fsnotifystays, for the rule files and the lookup database.Tests
internal/db:Opencreatessww.dbwith mode0600and every table, and records migrations 0 and 1; a secondOpenapplies nothing.Openfails on a read-only file, a read-only directory, and a file that is not a database, naming the file.Writeapplies upserts and deletes together, and applies none when one fails.Readyields rows lowest write order first.internal/store:Closeand a newOpen.sww.dbat the next write.Getleaves the drop order alone. The drop order survives a reopen.Putof a new value under its key.NewTable, naming the table and the key.writtenwith the error, keeps its changes, and writes them at the next write. The test drops a table through a second connection, then restores it.snapshot_test.gofiles: its state survives a reopen of the store.bans: admin bans are never dropped, and after a reopen the drop takes the netblock with the earliest last request.reputation: a list's copy is parsed at start; rows for URLs and zones no setting names are deleted.alerts: alerts waiting are sent after a reopen.internal/sww:Disclosures
store.Open.001_schema.sqland writing those mappings.banstable has no limit, since the ledger's dropping counts bans, not netblocks, and spares admin bans. AtSWW_MAX_BANS5,000 each drop scans 5,000 rows. At 100,000, if the settings audit moves it there, it scans 100,000, estimated at about a millisecond per new ban when full.Model: opus-5-5
any table not joined with other tables gets its own sqlite file.
no json in the db. normalize it properly.
why are we doing write intervals? can we not write in realtime in a dedicated single threaded goroutine eating from a channel? is there some issue with keeping the files up to date in realtime?
i’d also like a log database in the discriminator that keeps a cache of the last 24 hours (configurable) of requests or 1M rows, whichever is smaller.
we will eventually be adding a web analytics interface and a local log cache will be extremely useful.
also an error log sqlite file that tracks our own errors, 7d or 100k lines whichever is smaller. things like ipabusedb or geoip or routewatch backend timeouts, missed response to rproxy deadlines, etc.
For the spec writer (an Opus run, continuing sneak's order #217 (comment)): revise the spec at #217 (comment) according to sneak's review (#217 (comment) and #217 (comment)). Post the whole revised spec as one new comment, for his approval.
rproxydeadlines. Give its schema, its settings and what is recorded, and where.Model: opus-5-5
Spec, revised: state in SQLite, behind one store (for sneak's approval; nothing is built before he approves)
This replaces the spec at #217 (comment), after sneak's review at #217 (comment) and #217 (comment). Read from
nextat42e06bf.internal/store, holds every kind of state in memory. It reads the state from disk once, at start.SWW_STATE_DIR. Tables joined by a foreign key share a file; every other table has a file of its own.requests.db: the last 24 hours of requests, or the last 1,000,000, whichever is fewer;errors.db:discriminator's own warnings and errors, for the last 7 days or the last 100,000, whichever is fewer.internal/state, the five JSON files, editing state whilediscriminatorruns, and two settings go away. One setting comes:SWW_REQUEST_LOG_DATABASE_MAX_AGE.Writing as changes happen
sneak asked why the earlier spec wrote on an interval, and whether one goroutine reading from a channel could write each change as it happens.
The catch: disk writes
SQLite writes whole pages, so writing each change at once costs more disk writes than an interval does.
requests.db, with its headers: about three pages.Packages and driver
internal/table: the in-memoryTablethat every part holds its state in. It knows nothing of files. It keeps the order of use withhashicorp/golang-lru/v2/simplelru, as the parts do today.internal/store: opens the files, reads them into tables at start, writes each change, and keeps the two logs. Onlyswwanddiscriminatorimport it. The parts get their tables from them.internal/db: opens one SQLite file and applies its schema. Onlyinternal/storeimports it. It exists becauseREPO_POLICIES.mdputs the schema files ininternal/db/migrations/, embedded in the binary.internal/storeimports the parts for their types, asinternal/statedoes today. So the parts cannot import it: they importinternal/tableinstead. That is why the table is a package of its own.modernc.org/sqlite, pure Go with no CGO, as the Go package defaults require, throughdatabase/sql, with one connection per file.The files
All in
SWW_STATE_DIR(default/var/lib/sww). Each is named after its first table, and has SQLite's-waland-shmfiles beside it while open.bans.dbclients.dbgeojs_answers.dblists.dbdnsbl_verdicts.dbabuseipdb_scores.dbabuseipdb_checks_spent.dbalerts.dbalert_cooldowns.dbalert_hours.dbanomaly_counters.dbrequests.dberrors.dbSettings on open:
journal_mode=WALandsynchronous=NORMAL. A crash or a hard kill never damages a file.foreign_keys=ON.db.Opencreates a missing file with mode0600before SQLite opens it, since the files hold visitors' addresses. SQLite gives the-waland-shmfiles the same mode.SWW_STATE_DIRto theswwuser.Sizes at the defaults, roughly:
requests.db: up to about 1 GiB at 1,000,000 rows.errors.db: up to about 30 MiB.clients.db: about 8 MiB.bans.db: about 3 MiB.SQLite reuses freed pages, so a file never shrinks, and never grows past its fullest.
Schema
internal/db/migrations/000_migration.sqlis applied to every file first. It holds only the tracking table, asREPO_POLICIES.mdrequires:Each file's own tables are in
internal/db/migrations/NAME/001_schema.sql, such asclients/001_schema.sql.In every table:
STRICT.INTEGER: milliseconds since 1970, in UTC.TEXT, as Go'snetipwrites it, such as192.0.2.1/32or2001:db8::/64.TEXT, such asAS64496.INTEGER, 0 or 1.'', as in Go.NULLmeans a number, a time or a field does not apply.NULL: a part of a key that does not apply is''.minute_requests, three columns:_start: when the bucket under way began,NULLwhile it is empty;_current: the count in it;_previous: the count in the bucket before it.Below each file, "Held as" names the
Storefield that holds it, its limit, and the order its values are read in at start.bans.dbHeld as
Bans, atable.Table[netip.Prefix, []bans.Ban]: each netblock's bans, oldest first.SWW_MAX_BANSof its own, the earliest ban of the netblock that has gone longest without a request. An admin's ban is never dropped.clients.dbHeld as
Clients, atable.Table[netip.Prefix, ratelimit.Client]. At mostSWW_MAX_TRACKED_CLIENTS. Read in the order oflast_seen.geojs_answers.dbHeld as
GeoJSAnswers, atable.Table[netip.Prefix, lookup.Answer]. At most 100,000. Read in the order ofanswered.Answer.Usedgoes: the table keeps the order of use.lists.dbHeld as:
Lists, atable.Table[string, reputation.List];BlocklistNetblocks, atable.Table[reputation.ListNetblock, struct{}];ASNLimitPercents, atable.Table[reputation.ListASN, int64];CrowdSecDecisions, atable.Table[reputation.ListNetblock, reputation.Decision].None has a limit. The order they are read in does not matter.
listsrow.List.Linesgoes. The copy as fetched, comment lines included, is no longer kept; what it says is.dnsbl_verdicts.dbHeld as
DNSBLVerdicts, atable.Table[reputation.DNSBLQuery, reputation.Verdict]. At most 100,000. Read in the order offetched. At start, the part deletes the verdicts of zonesSWW_DNSBL_ZONESdoes not name.abuseipdb_scores.dbHeld as
AbuseIPDBScores, atable.Table[netip.Prefix, reputation.Score]. At most 100,000. Read in the order offetched.abuseipdb_checks_spent.dbHeld as
AbuseIPDBChecksSpent, atable.Table[time.Time, int]. A day's first check deletes the rows of earlier days.alerts.dbHeld as
Alerts, atable.Table[int64, alerts.Alert], andAlertsWaiting, atable.Table[alerts.Waiting, struct{}]. Neither table has a limit: the queue keeps at most 1,000 alerts waiting per destination, as today.alerts_waiting, oldest first.alerts.Alert.Detailbecomes a struct with a field for each detaildocs/alerts.mdlists, in place ofmap[string]any. The JSON sent has the same fields and values.alert_cooldowns.dbHeld as
AlertCooldowns, atable.Table[alerts.CooldownKey, alerts.Cooldown]. No limit: the end of each hour deletes the cooldowns that have run out, as today.alert_hours.dbHeld as
AlertHours, atable.Table[time.Time, alerts.Hour]: one value, the hour under way. Its end sends the summary, deletes it and puts the next.anomaly_counters.dbHeld as
AnomalyCounters, atable.Table[anomaly.Key, anomaly.Counter]. At most 20,000. Read in the order of each counter's latest_start.requests.dbdiscriminatoradds a row as it writes a request's last line. That is thereportline of a question, or the one line of a request to its own endpoints. A question whose report never comes has no row; itsdecisionline is on stdout, as today.SWW_REQUEST_LOG_DATABASE_MAX_AGE, and the rows beyond the newest 1,000,000. Their headers, rule ids and sources go with them. Between two such deletions, the table can hold a minute's rows over either bound.SWW_REQUEST_LOG_DATABASE_MAX_AGE(default24h), how long a request is kept inrequests.db. A duration above zero. A value that is set but invalid stops the start. The 1,000,000 is a constant.discriminatorruns. It is for the web analytics interface to come, which will add its own queries. Meanwhile any SQLite tool can read it, even whilediscriminatorruns.errors.dbwarnorerror, whateverSWW_LOG_LEVELsays. Its attributes are kept, buttypeandinstance, which every message has.Store.ErrorHandlerwraps the process log's handler. So nothing changes where errors are logged, and every error logged is recorded.lookup, and the lookup database that cannot be read;reputation;swwhas no routewatch lookup. Its failures will be recorded the same way once it has one.rproxy.rproxystops waiting afterSWRP_TIMEOUT_MS, whichdiscriminatordoes not know today. So:rproxysendsSWRP_TIMEOUT_MSwith every question, in a new header,X-Sww-Answer-Time-Limit-Ms.discriminatorremoves the header from the request the checks see, as it doesX-Sww-Client.400, as one with a badX-Request-IDis.discriminatorhas written its answer, it compares the time since it read the question's headers with the header. If that is longer, it logs a warning, "answered after rproxy's time limit", withrequest_id, the milliseconds taken and the limit.errors.dbis logged on stdout only, so that it does not feed itself.Exported API
How a part uses its table, here
ratelimitcounting a request, under theLimiter's own lock:Each table belongs to one part. The table's own lock keeps it safe, but a part that gets a value, changes it and puts it back holds its own lock across the three, as it does today.
Behaviour
The read at start
sww discriminatoropens the store before anything that keeps state.Openopens every file, creating any that is missing, and reads each state table whole, once. It reads nothing fromrequests.dbanderrors.db.Changes written
PutandDeletechange memory at once, and leave the change waiting for the writer. No request waits for the disk.BEGIN IMMEDIATEtoCOMMIT.INSERT ... ON CONFLICT DO UPDATE.Closewrites what is still waiting. An orderly stop loses nothing.Limits and dropping
Putwould take it past the limit. AGetor aPutis a use. The value leaves memory at once, and its file at the next write.SWW_MAX_TRACKED_CLIENTSclients, and 100,000 GeoJS answers, 100,000 DNSBL verdicts, 100,000 AbuseIPDB scores and 20,000 anomaly counters. They move from the parts intointernal/store, and the parts no longer know them.SWW_MAX_BANS, never an admin's;Schema versions
001_schema.sqlis edited in place and no002is added, asREPO_POLICIES.mdsays.db.Openapplies000_migration.sql, then each of the file's migrations not yet inmigrations, in file order, in one write transaction.golang-migrateeach create their own tracking table, which the policy's000_migration.sqlcreates instead.A file that is missing, damaged or cannot be written
discriminatorand removes its file, with its-waland-shm.discriminatorstops with exit status 1 and an error naming the file. Starting empty would forgive every repeat offender. The same holds forrequests.dbanderrors.db, which are opened but not read.CHECKconstraints refuse most such edits as they are made.db.Opentakes the write lock to apply the schema, so the start stops with the error.Runreports the failure.swwlogs it, counts it in the metrics, and raises afile_erroralert naming the file, with today's cooldown per file.discriminatorruns goes unnoticed. The state in it is lost at the next start.Reading and editing by hand
discriminatorruns.discriminator, edit the file, start it. A change made while it runs is not read, and the next change to the same row overwrites it.What goes away
internal/stateand its tests: the five JSON files,Load,Run,WriteAll,Watch, taking in edits,.badfiles, the checksums and theversioncheck.discriminatorruns, which SPEC "Persistent state" and "Bans" designed:POST /_sww/bansandDELETE /_sww/bans/....admin) or forgetting one (deleting its row) needsdiscriminatorstopped and a SQLite tool. Endpoints for them would be a separate issue.SWW_STATE_WRITE_DELAYandSWW_STATE_COUNTER_INTERVAL. That settles both for the settings audit at #202.sww_state_file_edits_taken_in_totalandsww_state_file_edits_set_aside_total.Offences.TokenRefused, kept only so that oldclients.jsonfiles load;lookup.Answer.Used;reputation.List.Linesandreputation.Checks;alerts.State.Load,LoadEdit,Changed,markChanged, everySnapshotbut the ledger's, whichGET /_sww/bansuses, and the part's own map or LRU, which its table replaces.The change each caller needs
bans:New(rules Rules, held *table.Table[netip.Prefix, []Ban]) *Ledger.AddLookup, get the netblock's bans, change a copy and put it.dropOnetakes the first netblock inAll()that holds a ban not made by an admin.ratelimit:New(limits Limits, clients *table.Table[netip.Prefix, Client]) *Limiter. Counting gets the client, changes it and puts it.Requests,ClientandLenread the table.lookup:Params.Answers. Using an answer gets it. A new answer is put.reputation:Params.Lists,Params.BlocklistNetblocks,Params.ASNLimitPercentsandParams.CrowdSecDecisions;DNSBLParams.Verdicts;AbuseIPDBParams.ScoresandAbuseIPDBParams.ChecksSpent;ListNetblockandListASN, andDNSBLQuery, today's unexportedquery.alerts:Params.Alerts,Params.Waiting,Params.CooldownsandParams.Hours;Waiting,CooldownKey(today'scooldownKey) andDetail.Waiting, sends the alert, then deletes the row, and the alert once no destination waits for it.alerts.Detailin place of a map:discriminatorfor bans, the Core Rule Set and reputation;anomaly;lookup;reputation;rules; andswwfor a file that cannot be written.anomaly:Params.Counters.keybecomes the exportedKey.discriminator:Params.Store, from which it hands the parts their tables.writeLinealso callsStore.AddRequestfor a request's last line.X-Sww-Answer-Time-Limit-Msand logs a late answer, as undererrors.db.BanEntryandBanEntries, the ban endpoints' answer form, move here fromstate.Serverfields that only handed parts tostate.Loadgo.rproxy: sendsX-Sww-Answer-Time-Limit-Mswith each question.metrics:StateFileWritten(file string, size int64, err error, dropped int). Thefilelabel names a database file. Its size is the file's with its WAL.sww_state_file_entries_dropped_total, by file, the log entries dropped with too many waiting.config: the two settings go, andSWW_REQUEST_LOG_DATABASE_MAX_AGEcomes.sww:Store.ErrorHandler.Store.Runwith awrittenthat updates the metrics, and logs and alerts a failure. A failure oferrors.dbis logged on stdout only.Store.Closeat an orderly stop, whereWriteAllis today.docs/state-files.mdlists the files and tables above, keeping today's field descriptions.SPEC.md: a new "Persistent state", and a short "Error log" section.SPEC.mdalso changes the "State files" settings, the editing paragraph of "Bans" and its mentions ofbans.json, and "State" under "Deployment": the volume can now hold about 1 GiB.SPEC.md"Request log" gets the request database, and "The question" the new header.README.md,docs/settings.md,docs/metrics.md, and theGET /_sww/bansandGET /_sww/clients/...lines ofdocs/admin-endpoints.mdfollow.go.mod: addsmodernc.org/sqlite.hashicorp/golang-lru/v2stays, forinternal/tableand the reportsdiscriminatorwaits for.fsnotifystays, for the rule files and the lookup database.Tests
internal/table:Getcounts as a use.Allyields the value used longest ago first.changedgets each put, delete and drop. It gets none of the values given toNew, only the values they drop.internal/db:Opencreates a file with mode0600and its tables, and records migrations 0 and 1. A secondOpenapplies nothing.Openfails on a read-only file, a read-only directory, and a file that is not a database, naming the file.internal/store:Closeand a newOpen, every field included. Each field that can beNULLor empty is tested both ways.Close, oncewrittenreports its file.Open, naming the file, the table and the key.requests.dbanderrors.dblose the rows past their age and past their count, with their child rows. The test lowers the counts.ErrorHandlerrecords a warning with its attributes, records nothing atinfo, and records warnings whileSWW_LOG_LEVELiserror.snapshot_test.gofiles, checks that its state survives a reopen of the store, and:bans: an admin's ban is never dropped. The drop takes the netblock seen longest ago.reputation: a fetch that changes one line of a list writes only that entry. Lists of URLs, and verdicts of zones, that no setting names are deleted at start.alerts: alerts waiting are sent after a reopen, each stored once for all its destinations. The JSON sent for each event matches today's.discriminator:reportline becomes a row ofrequests.db. Itsdecisionline does not.X-Sww-Answer-Time-Limit-Msis logged as a warning. A question without the header is refused with400.rproxy: each question carriesX-Sww-Answer-Time-Limit-Ms, holdingSWRP_TIMEOUT_MS.internal/sww:Disclosures
REPO_POLICIES.mddescribes the migrations of one database. With thirteen, each file's schema has a directory of its own underinternal/db/migrations/, and they share one000_migration.sql.alerts,NULLwhere the event has no such field, not a table per event. One table is less code; a table per event would have noNULLcolumns.requests.dbkeeps one row per request, from its last line. A question whose report never comes has none.errors.dbrecords every warning and error the process logs, not a chosen list, so that nothing new needs wiring to be recorded.discriminatorcannot know it otherwise.Model: opus-5-5