clawbot
c16f177575
check / check (push) Waiting to run
Vendor .golangci.yml byte-identical from sneak/prompts at cc440118 and move the Dockerfile lint phase to golangci-lint v2.14.0 by the digest REPO_POLICIES.md names. Fix the code to that config with flags, help text, output files, SQL and the order of steps unchanged; long functions are split into named steps. Judgement call: the extraction transaction is now rolled back on every early return; the old deferred rollback missed most failures and could dereference a nil transaction. Thirteen //nolint directives (gosec, unconvert, mnd, unqueryvet), each with its reason. Model: opus-5-5
287 lines
13 KiB
Markdown
287 lines
13 KiB
Markdown
# bsdaily
|
|
|
|
[bsdaily](https://git.eeqj.de/sneak/bsdaily) is a command-line utility written
|
|
in [Go](https://golang.org) that carves a single day (or a range of days) of
|
|
[Bluesky](https://bsky.app) firehose data out of a large, continuously-growing
|
|
SQLite database and writes it out as a self-contained,
|
|
[zstd](https://facebook.github.io/zstd/)-compressed SQL dump. The dumps are
|
|
named by date (e.g. `2026-06-27.sql.zst`), organized into per-month directories,
|
|
and are designed to be published, archived, mirrored, and later re-merged back
|
|
into a single database.
|
|
|
|
The source database is read from a read-only [ZFS](https://openzfs.org)
|
|
snapshot, so extraction never contends with the live firehose ingester that is
|
|
writing to the original database. The tool is operationally conservative: it
|
|
checks free disk space before starting, copies the snapshot to fast scratch
|
|
storage, processes one day at a time to avoid SQLite lock contention, verifies
|
|
every compressed output before publishing it, and writes output atomically via a
|
|
temp-file-and-rename so a partial run never leaves a corrupt `.sql.zst` behind.
|
|
|
|
This project was written by [@sneak](https://sneak.berlin) to produce a daily,
|
|
mergeable, publicly-mirrorable archive of the Bluesky firehose. It is currently
|
|
a one-person effort. The current version is pre-1.0 and there has not yet been a
|
|
versioned release; [SemVer](https://semver.org) will be used for releases.
|
|
|
|
# Build Status
|
|
|
|
CI runs the standard `make check` (formatting, linting, tests). The `main`
|
|
branch must always be green.
|
|
|
|
# Participation
|
|
|
|
Primary development happens on a privately-run Gitea instance at
|
|
[https://git.eeqj.de/sneak/bsdaily](https://git.eeqj.de/sneak/bsdaily) and
|
|
issues are [tracked there](https://git.eeqj.de/sneak/bsdaily/issues).
|
|
|
|
Changes must always be formatted with `make fmt` (`go fmt` for Go, prettier for
|
|
Markdown), syntactically valid, and must pass the linting defined in the
|
|
repository (the shared `.golangci.yml` from `sneak/prompts`), which can be run
|
|
with a `make lint`. The `main` branch is protected and all changes must be made
|
|
via [pull requests](https://git.eeqj.de/sneak/bsdaily/pulls) and pass CI to be
|
|
merged.
|
|
|
|
See [`REPO_POLICIES.md`](REPO_POLICIES.md) for detailed coding standards,
|
|
tooling requirements, and workflow conventions.
|
|
|
|
# Entrypoints
|
|
|
|
This repository adheres to the
|
|
[Scripts to Rule Them All](https://github.com/github/scripts-to-rule-them-all)
|
|
standard: normalized scripts in `script/` are the entrypoints for the
|
|
development workflow, and the Makefile targets are thin shims that call them. We
|
|
provide:
|
|
|
|
- `script/bootstrap` — install all development dependencies (go, Go module
|
|
download, node and yarn at pinned versions when absent, and the prettier
|
|
pinned in `package.json` and `yarn.lock`); a node or yarn already installed is
|
|
used whatever its version; the linter is not installed on the host
|
|
- `script/setup` — make a fresh clone ready for development: runs
|
|
`script/bootstrap`, then `script/install-precommit`
|
|
- `script/projectname` — print the project name (used for the Docker image tags)
|
|
- `script/test` — build the Dockerfile's `test` phase, which runs the test suite
|
|
with `-race` (verbose rerun on failure)
|
|
- `script/lint` — build the Dockerfile's `lint` phase, which runs
|
|
`golangci-lint run ./...`
|
|
- `script/fmt` — format the Go code with `go fmt` and every Markdown file with
|
|
prettier (writes)
|
|
- `script/fmt-check` — check the Go formatting with `gofmt` and the Markdown
|
|
formatting with prettier (read-only)
|
|
- `script/check` — run `script/test`, `script/lint`, and `script/fmt-check`
|
|
- `script/docker` — build the Docker image tagged via `script/projectname`; the
|
|
build runs the `lint` and `test` phases first
|
|
- `script/cibuild` — CI entrypoint: runs `script/bootstrap` and `script/check`,
|
|
then builds the Docker image tagged via `script/projectname`
|
|
- `script/precommit` — pre-commit gate: `go mod tidy` (must not change `go.mod`
|
|
or `go.sum`) and `go fmt`, then `script/check`
|
|
- `script/install-precommit` — install the git pre-commit hook that runs
|
|
`script/precommit`
|
|
|
|
Every Docker build in `script/` is uncached, so the `lint` and `test` phases
|
|
always run rather than being served from the build cache.
|
|
|
|
# Problem Statement
|
|
|
|
A Bluesky firehose ingester writes every observed post (and associated users,
|
|
hashtags, URLs, and media references) into a single ever-growing SQLite
|
|
database, `firehose.db`. This database has several properties that make it
|
|
awkward to publish or archive directly:
|
|
|
|
- It is **large and always growing**, so re-publishing the whole thing every day
|
|
is wasteful.
|
|
- It is **continuously written**, so reading from it directly risks lock
|
|
contention with the live ingester and inconsistent reads.
|
|
- It is **monolithic**, so there is no natural unit at which to mirror, share,
|
|
or distribute "just yesterday's posts".
|
|
|
|
What is wanted instead is a stable, immutable, per-day artifact: a small file
|
|
containing exactly one calendar day of firehose data, cheap to publish, cheap to
|
|
mirror, and trivially re-mergeable into a full database by anyone who collects a
|
|
set of them.
|
|
|
|
# Proposed Solution
|
|
|
|
A tool, `bsdaily`, that:
|
|
|
|
- locates the most recent read-only **ZFS daily snapshot** of the firehose
|
|
filesystem, so it reads from a consistent point-in-time copy that the live
|
|
ingester cannot be writing to;
|
|
- copies the snapshot's database files to fast scratch storage;
|
|
- **extracts** a single day's `posts` (and all rows reachable from them) into a
|
|
fresh, minimal per-day SQLite database;
|
|
- **dumps** that per-day database to SQL and pipes it through multithreaded zstd
|
|
compression;
|
|
- **verifies** the compressed output (zstd integrity check plus a sanity check
|
|
that the decompressed stream actually looks like SQL);
|
|
- **publishes** the result atomically as
|
|
`DailiesBase/YYYY-MM/YYYY-MM-DD.sql.zst`.
|
|
|
|
Each daily dump is emitted with `INSERT` statements over the full schema
|
|
(including the deduplicated `users`, `hashtags`, and `urls` lookup tables), so
|
|
any collection of daily dumps can be merged into a single database by rewriting
|
|
`INSERT INTO` to `INSERT OR IGNORE INTO` and replaying them in sequence. Two
|
|
helper scripts ([`merge_daily_dumps.sh`](merge_daily_dumps.sh) and
|
|
[`regenerate_auxiliary_tables.sql`](regenerate_auxiliary_tables.sql)) are
|
|
included to do exactly this and to rebuild the aggregate statistics
|
|
(`use_count`, `first_seen`, user `resolved_at`/`updated_at`) afterward.
|
|
|
|
# Design Goals
|
|
|
|
- **Never disturb the live ingester.** All reads come from a ZFS snapshot, never
|
|
the live database.
|
|
- **Crash-safe, idempotent runs.** Output is written to a temp file and
|
|
atomically renamed; a day whose final output already exists is skipped, so
|
|
re-running a range is safe and resumable.
|
|
- **Mergeable output.** Daily dumps re-combine losslessly into a full database
|
|
via `INSERT OR IGNORE`.
|
|
- **Operationally cautious.** Free-space preflight checks on both scratch and
|
|
output filesystems; explicit verification of every artifact before it is
|
|
published.
|
|
- **Fast where it's free.** Large snapshot copies use a 256MiB buffer,
|
|
pre-allocate the destination, and (on Linux) issue `posix_fadvise`
|
|
sequential/willneed hints; extraction uses aggressive, crash-unsafe-by-design
|
|
SQLite pragmas because the working data lives only in disposable scratch
|
|
space.
|
|
|
|
# Non-Goals
|
|
|
|
- **Real-time export.** `bsdaily` operates on daily snapshots; the freshest day
|
|
it can produce is the snapshot date minus one.
|
|
- **Schema ownership.** The schema is defined by the upstream firehose ingester;
|
|
[`schema.sql`](schema.sql) is included for reference only. `bsdaily` copies
|
|
whatever table and index DDL it finds in the source.
|
|
- **Cross-platform deployment.** It is built and run on Linux (the free-space
|
|
check and fadvise hints use `golang.org/x/sys/unix`; a non-Linux build
|
|
compiles but is a no-op for the fadvise hints). The hard-coded paths assume
|
|
the production host's ZFS layout.
|
|
|
|
# How It Works
|
|
|
|
A single run proceeds as follows:
|
|
|
|
1. **Find the snapshot.** Scan `SnapshotBase` for directories matching
|
|
`zfs-auto-snap_daily-YYYY-MM-DD-NNNN`, pick the most recent, and confirm it
|
|
contains `firehose.db`.
|
|
2. **Determine target days.** Default to the snapshot date minus one day; or use
|
|
`--date`, or every day in the inclusive `--from`/`--to` range.
|
|
3. **Preflight disk space.** Require at least 500GiB free on the scratch
|
|
filesystem and 20GiB free on the output filesystem.
|
|
4. **Copy the database to scratch.** Copy `firehose.db`, its `-wal`, and (if
|
|
present) its `-shm` from the snapshot into a fresh temp directory under
|
|
`TmpBase`.
|
|
5. **Per day**, processed strictly one at a time to avoid SQLite contention:
|
|
- skip the day if its final output file already exists;
|
|
- `ATTACH` the copied source DB to a new empty per-day DB, recreate the
|
|
table DDL, and `INSERT ... SELECT` the target day's `posts` plus all rows
|
|
reachable from them (`posts_hashtags`, `posts_urls`, `hashtags`, `urls`,
|
|
`users`, and `media` if that table exists);
|
|
- abort the day cleanly if there are zero posts (`ErrNoPosts`), rather than
|
|
emitting an empty dump;
|
|
- recreate indexes, detach the source, and verify the inserted row count;
|
|
- `sqlite3 .dump | zstdmt` into a hidden temp file;
|
|
- run a zstd integrity check and confirm the decompressed head looks like
|
|
SQL;
|
|
- atomically rename into place and delete the per-day scratch DB.
|
|
6. **Clean up** the temp directory and log a processed/skipped/total summary.
|
|
|
|
# Usage
|
|
|
|
```
|
|
bsdaily # extract the snapshot date minus one day
|
|
bsdaily --date 2026-06-27 # extract a single specific day
|
|
bsdaily --from 2026-06-01 --to 2026-06-27 # extract an inclusive range
|
|
```
|
|
|
|
Flags:
|
|
|
|
- `-d`, `--date YYYY-MM-DD` — extract a single day. Mutually exclusive with
|
|
`--from`/`--to`.
|
|
- `--from YYYY-MM-DD` — start of an inclusive range (requires `--to`).
|
|
- `--to YYYY-MM-DD` — end of an inclusive range (requires `--from`).
|
|
|
|
With no flags, the tool extracts the day before the latest snapshot. All
|
|
progress is logged as structured `slog` text to stderr.
|
|
|
|
## Merging dumps back into a database
|
|
|
|
```
|
|
# Using the helper script:
|
|
./merge_daily_dumps.sh merged.db daily_dumps/*.sql.zst
|
|
|
|
# Or by hand:
|
|
zstdcat *.sql.zst | sed 's/INSERT INTO/INSERT OR IGNORE INTO/g' | sqlite3 merged.db
|
|
sqlite3 merged.db < regenerate_auxiliary_tables.sql
|
|
```
|
|
|
|
# Requirements
|
|
|
|
- **Go** (see [`go.mod`](go.mod) for the toolchain version) to build.
|
|
- **Linux** for production use (ZFS snapshots, `statfs` free-space checks,
|
|
`posix_fadvise` hints).
|
|
- The **`sqlite3`** and **`zstdmt`** (multithreaded zstd) binaries on `PATH`;
|
|
`zstdcat` is used for verification. SQLite reads/writes during extraction use
|
|
the pure-Go [`modernc.org/sqlite`](https://pkg.go.dev/modernc.org/sqlite)
|
|
driver, so no cgo is required for that part.
|
|
|
|
# Configuration
|
|
|
|
Operational parameters are compile-time constants in
|
|
[`internal/bsdaily/config.go`](internal/bsdaily/config.go):
|
|
|
|
- `SnapshotBase` — `/srv/berlin.sneak.fs.blueskyarchive/.zfs/snapshot`
|
|
- `TmpBase` — `/srv/storage/tmp` (fast scratch space for copies + per-day DBs)
|
|
- `DailiesBase` — `/srv/berlin.sneak.fs.bluesky-dailies` (output root)
|
|
- `MinTmpFreeGB` / `MinDailiesFreeGB` — `500` / `20`
|
|
- `zstdCompressionLevel` — `15`
|
|
- `sqliteCacheSizeKB` — `200000` (≈200MB)
|
|
|
|
These are tuned for one specific production host; adjust and rebuild to run
|
|
elsewhere.
|
|
|
|
# Data Model
|
|
|
|
The firehose schema (reference copy in [`schema.sql`](schema.sql)) centers on a
|
|
`posts` table, with `users` keyed by DID and many-to-many junction tables
|
|
linking posts to deduplicated `hashtags` and `urls`. An optional `media` table
|
|
tracks downloaded blobs by content hash. `bsdaily` does not own this schema; it
|
|
reflects whatever DDL exists in the source snapshot and selects forward from
|
|
`posts` along the foreign-key relationships to produce a referentially-complete
|
|
per-day slice.
|
|
|
|
# Use Cases
|
|
|
|
## Daily public archive
|
|
|
|
Publish one small, immutable file per day to static HTTP (or IPFS, or a mirror
|
|
network) so that anyone can fetch exactly the day(s) they want and re-merge them
|
|
locally.
|
|
|
|
## Backfilling a range
|
|
|
|
Run `--from`/`--to` over a span of dates to (re)generate any missing daily
|
|
dumps; already-published days are skipped, so the operation is resumable and
|
|
safe to re-run.
|
|
|
|
## Reconstituting a full database
|
|
|
|
Collect any set of daily dumps and merge them with `INSERT OR IGNORE` to rebuild
|
|
a complete, queryable SQLite database, then regenerate the aggregate statistics
|
|
tables.
|
|
|
|
# See Also
|
|
|
|
## Links
|
|
|
|
- Repo: [https://git.eeqj.de/sneak/bsdaily](https://git.eeqj.de/sneak/bsdaily)
|
|
- Issues:
|
|
[https://git.eeqj.de/sneak/bsdaily/issues](https://git.eeqj.de/sneak/bsdaily/issues)
|
|
- Bluesky: [https://bsky.app](https://bsky.app)
|
|
- zstd: [https://facebook.github.io/zstd/](https://facebook.github.io/zstd/)
|
|
|
|
# Authors
|
|
|
|
- [@sneak <sneak@sneak.berlin>](mailto:sneak@sneak.berlin)
|
|
|
|
# License
|
|
|
|
- [WTFPL](https://wtfpl.net)
|