btcdata-archive

Standalone Python archiver for the three fixed btcprod tables: public.binance_price, public.strike_price, and public.kraken_minute. A restricted RustFS IAM identity and versioned archive bucket, progress ledger and restricted PostgreSQL role have been provisioned. The Kubernetes CronJob is deployed through GitOps, suspended. The initial ledger transition bug is fixed; the operator completed 28 backlog weeks. Rollout validation ran verify-only and did not delete any source rows. The repository is hosted at https://git.pmagnus.cloud/magnus/btcdata-archive; Gitea Actions and project-scoped Harbor CI/pull credentials are configured.

Install and test

Python 3.12+; production image pins Python 3.12.12 (Debian Bookworm).

python3.12 -m venv .venv
source .venv/bin/activate
python -m pip install --require-hashes -r requirements-test.lock
export PYTHONPATH="$PWD/src"
python -m pytest -q
# Requires Docker, only starts disposable PostgreSQL 18.0 and RustFS 1.0.0:
./scripts/test-integration.sh

The integration fixture always creates a new random database, login and bucket, then removes only those test resources. To use already-running disposable services, set TEST_PGHOST, TEST_PGPORT, TEST_PGUSER, TEST_PGPASSWORD, TEST_S3_ENDPOINT_URL, TEST_S3_ACCESS_KEY, TEST_S3_SECRET_KEY. Never point tests at production infrastructure. Full S3 integration tests skip without both required TEST endpoints. PostgreSQL-only regressions require only the disposable PostgreSQL TEST variables and can run with python -m pytest -q tests/integration/test_postgres.py. Gitea CI passed all 65 unit/integration tests against disposable PostgreSQL 18.0 and RustFS 1.0.0, then published an immutable image. Boundary/crash tests inject interruptions at workflow boundaries; actual process-kill/network/disk fault testing remains additional operator validation. Production pod connectivity was verified on Citadel.

requirements.lock pins runtime packages and hashes; requirements-test.lock adds pytest. Regenerate with Python 3.12, pip 25.2 and pip-tools 7.5.1: pip-compile --generate-hashes -o requirements.lock pyproject.toml and pip-compile --extra test --generate-hashes -o requirements-test.lock pyproject.toml. CI runs the full suite against disposable PostgreSQL/RustFS before publishing a full-commit tag to the private btcdata-archive project in the approved Harbor registry. CI has project-scoped push/pull access; Kubernetes has a separate pull-only identity. Tests run inside a disposable container on an isolated Docker network, without published ports or production credentials, including when CI uses a Docker job container. Deploy using the published digest, not a mutable tag. Remote: gitea:magnus/btcdata-archive.git.

Configuration

Supply credentials through environment/Secret injection, never arguments or tracked files. For local use, the catch-up wrapper automatically reads the repository's untracked .env (keep mode 0600). Set ARCHIVE_ENV_FILE to choose another file, including for direct CLI use. Existing environment variables take precedence. Entries use literal KEY=value syntax with optional shell-style quoting/comments; no shell commands or variable interpolation are executed. Fill blank S3 credential fields with the restricted archive identity before running. Required PostgreSQL variables: PGHOST, PGUSER, PGPASSWORD. Defaults: PGPORT=5432, PGDATABASE=btcprod, PGSSLMODE=prefer. Runs also require S3_ENDPOINT_URL, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY. S3 uses path-style addressing, with no fallback to an AWS public endpoint.

Optional defaults: AWS_DEFAULT_REGION=us-east-1, ARCHIVE_BUCKET=btcdata-archive, ARCHIVE_PREFIX=btcprod/weeks/, ARCHIVE_WORK_DIR=/work, ARCHIVE_BATCH_ROWS=5000. Inventory/status do not require S3 credentials. The SQLite file report requires only S3 settings, not PostgreSQL credentials. Use a private work directory, accessible only to the runtime identity. The CLI sanitizes unexpected exception messages; it does not dump SQL, row data, SDK responses, connection strings or secrets. Database/SDK error classes are logged rather than raw messages; investigate those failures via privileged tooling without publishing secrets.

python -m btcdata_archive inventory
python -m btcdata_archive status
./scripts/archive-old-weeks.sh --mode verify-only --max-weeks 1
# Destructive; run ONLY after explicit rollout approval:
./scripts/archive-old-weeks.sh --mode archive-and-prune

The catch-up wrapper uses PYTHON when explicitly set, otherwise the active virtual environment, the repository's .venv/bin/python, or python3. It adds the repository's src to PYTHONPATH; dependencies must still be installed in the selected interpreter.

Both run modes require explicit --mode; optional --max-weeks must be positive. Omission processes all eligible weeks, oldest first, one at a time. A failure or unresolved changed row stops the invocation immediately. No local checkpoint is needed. Inventory/status require the operator-created ledger and supported source schema.

List file sizes and SQLite table counts

./scripts/list-archives.sh

This read-only report loads .env using the same interpreter selection as the catch-up wrapper. It lists every current object under ARCHIVE_PREFIX, showing MiB sizes and binance_price, strike_price, and kraken_minute row counts, plus totals. Counts come from actual SELECT COUNT(*) queries on the downloaded SQLite tables, not archive metadata or PostgreSQL. It neither imports the PostgreSQL backend nor requires PostgreSQL connection settings.

Each SQLite object's exact current version is downloaded one at a time into a private temporary directory under ARCHIVE_WORK_DIR. The report checks file size, advertised SHA-256 when present, and SQLite integrity, opens SQLite read-only, counts the tables, and deletes its temporary copy before the next file. It stops on corrupt/missing archives or insufficient disk space. Non-SQLite objects are listed with - counts. Historical object versions are excluded; files published after listing begins may appear only on the next invocation. Large inventories require downloading every SQLite file again; this does not run archival, update the ledger, or delete any data.

Retention and safety

One UTC instant is captured at CLI start. Its Monday 00:00 UTC is the anchor; cutoff is anchor minus 28 days. Weekly intervals are half-open. Rows exactly at cutoff stay. Price tables use recorded_at; candles use Unix-second ts, not created_at. A Monday 2026-10-05 run has cutoff 2026-09-07: it retains four completed weeks plus the current week's arrivals. Weekly scheduling permits about 28–35 days between runs; failed runs retain more. There is no daily pruning.

The same PostgreSQL session holds advisory lock 728194107 throughout the run. It never takes the application's writer lock 728194106, starts application writers, or reconnects a lost lock session. Export uses bounded server-side cursors in a read-only repeatable-read transaction, which closes before network operations.

For each week the archiver:

1. Checks conservative available-disk estimates (existing object, text expansion, SQLite journal, download copy and 64 MiB reserve). Disk errors fail safely.
2. Downloads an existing exact version, validates integrity/schema/metadata/hashes, and preserves previously archived rows. Missing previously verified objects fail closed; recover with an operator rather than rebuilding from a partial remainder.
3. Overlays current source rows by primary key. Source stream hashes/counts must match fresh SQLite reads of those keys. Internal gaps produce explicit empty weekly files; there are no empty files before the first source/incomplete week.
4. Closes/commits SQLite, validates the full archive, hashes it and uploads it with SHA-256 metadata. Bucket versioning must already be enabled. The multipart completion's version ID—not a subsequent HEAD—is the upload identity.
5. Downloads that exact uploaded version separately and verifies file SHA-256, SQLite integrity, schema, week bounds, table counts and canonical content digests. ETags and metadata alone are never content proof.
6. Records verified in btcdata_archive.weeks. Verify-only stops this week here, leaves source rows untouched, then advances to the next week.
7. In prune mode stages bounded batches from the verified download into typed TEMP tables. Deletes require primary-key matches, IS NOT DISTINCT FROM equality of every column, week bounds and fixed cutoff. Each batch commits independently.
8. Recounts source rows; only zero remaining rows produces complete. Changed/late rows produce failed with reconciliation required, and stop later-week processing.

A late insertion after the final count is discovered next invocation. Repeated runs reopen completed weeks with new old rows. Retry merges the full existing object, including already-pruned records, and writes another indefinitely retained version. Ledger statuses: exporting, verified, pruning, complete, failed. Deletion counts describe committed batches of the current attempt, not lifetime totals. A crash between batch commit and progress update can leave underreported progress; source equality and final recount—not progress counts—govern recovery and completion.

SQLite format 1

Keys: btcprod/weeks/YYYY-MM-DD.sqlite, Monday's date. All three source tables are always present. PKs/trade counts/quote_sats are SQLite INTEGER; decimals/JSON/timestamps are TEXT. Price tables have (recorded_at,id) indexes. metadata(key,value) holds JSON-encoded format version, source database/schema/types, week bounds, creation time, per-table counts and checksums. Source columns/types/nullability/PKs must match the supported migrations exactly; additions/removals/incompatibilities require an explicit format migration. RLS, nonordinary tables and custom DELETE triggers are rejected.

Decimals retain PostgreSQL's exact decimal string (including scale), never float/REAL. Timestamps are UTC YYYY-MM-DDTHH:MM:SS.ffffffZ. JSONB text is sorted by Unicode key, compact, with decimal parsing/serialization and no binary floating-point conversion. JSON null remains part of JSON text, distinct from SQL NULL. JSONB whitespace and original key order are not preserved.

Canonical checksum: rows in ascending PK order, encoded as compact UTF-8 JSON arrays of integer/string/null values, ensure_ascii=False, followed by one LF per row; SHA-256 over that byte stream (empty stream for an empty table). Field types/boundaries and nulls are unambiguous. Each file is closed, journal-free and self-contained.

Download, verify and query manually

Get the exact object_key, object_version_id, and file_sha256 from the ledger. With restricted credentials already supplied via environment:

aws --endpoint-url "$S3_ENDPOINT_URL" s3api get-object \
  --bucket btcdata-archive --key btcprod/weeks/2026-08-31.sqlite \
  --version-id "$VERSION_ID" /work/2026-08-31.sqlite
sha256sum /work/2026-08-31.sqlite  # compare with the verified ledger SHA-256
sqlite3 /work/2026-08-31.sqlite 'PRAGMA integrity_check;'
sqlite3 /work/2026-08-31.sqlite 'SELECT key,value FROM metadata;'
sqlite3 /work/2026-08-31.sqlite 'SELECT id,price,recorded_at FROM binance_price LIMIT 10;'

Full independent format/content validation using the installed package:

from datetime import datetime, timezone
from pathlib import Path
from btcdata_archive.sqlite_archive import validate, file_hash
p = Path('/work/2026-08-31.sqlite')
print(file_hash(p))  # compare with ledger, not only object metadata
print(validate(p, datetime(2026, 8, 31, tzinfo=timezone.utc), 'btcprod', 5000))

For exact arithmetic use Python Decimal(price_text), not SQLite casts to REAL or Python float. Restore first into an isolated database/schema with original types: BIGINT PKs, NUMERIC(18,8) price/OHLC/VWAP, NUMERIC(24,8) volume, JSONB rates, TIMESTAMPTZ timestamps, INTEGER trade_count, nullable BIGINT quote_sats. Iterate SQLite in batches; schema.decode_row(table,row) converts decimal/timestamp values exactly. Insert JSON text with an explicit %s::jsonb cast; insert the other decoded values through psycopg parameters. Read restored JSONB as rates::text, canonicalize using schema.encode_row, and compare every value/count/digest with the SQLite archive. tests/integration/test_archive.py demonstrates this independent round trip. Never restore directly over active source tables without a separate recovery plan.

Current provisioning state

On RustFS (192.168.50.45), IAM user btcdata-archive is attached only to policy btcdata-archive-runtime-v1, matching sql/s3-runtime-policy.json. Its credentials are saved in the ignored local .env (mode 0600), not in tracked files. Negative live checks confirmed denial of access to the existing xtdb bucket, administrative APIs (including self-admin access), and object deletion. On subsequent explicit approval, bucket btcdata-archive was created with versioning Enabled and no lifecycle/expiry rules. Live checks using the restricted identity confirmed versioning inspection, archive-prefix object/version listing, and denial of outside-prefix listing. The operator subsequently verified the 2026-02-16 weekly archive (74,123 Binance rows) with an exact-version download. Pruning then failed before deletion: PostgreSQL checks an UPSERT's proposed INSERT before its ON CONFLICT UPDATE, and progress-only ledger transitions omitted the stored verification fields. The fix preserves those fields before INSERT checks; read-only diagnosis confirmed all 74,123 source rows remained. Retry uses the existing verified archive, with no need to reset the ledger.

On separate explicit approval, btcdata_archive.weeks and PostgreSQL login btcdata_archiver were created in btcprod. Verified effective privileges are source SELECT/DELETE, ledger SELECT/INSERT/UPDATE, schema USAGE, and database CONNECT/TEMP. Source INSERT/UPDATE/TRUNCATE/REFERENCES/TRIGGER and ledger DELETE are denied; the role has no permanent object creation privilege, elevated attributes or memberships. A real login/read-only ledger/source-access check succeeded. .env now uses this restricted role instead of the application's administrative credentials. No source rows were changed during that setup. During explicitly authorized rollout, valid (recorded_at,id) indexes were built concurrently for the price tables. PostgreSQL HBA access was added only for btcprod/btcdata_archiver from Citadel's 10.42.1.0/24 pod subnet, backed up and reloaded without a restart. No source rows were changed by these preparation steps.

scripts/rustfs-create-identity.py is an explicit operator-only provisioning utility, run on RustFS with access to its administrative credential file. It refuses to overwrite existing users/policies and disables a newly created identity on incomplete setup. It is not executed by the runtime job. RustFS 1.0.0 rejects bare Resource: "*" and mixed action families in a single statement; the policy uses separate admin/STS deny statements and S3 wildcard ARNs compatible with the deployed build.

Kubernetes deployment state

GitOps magnus/deploy main commit c635511 registers apps/btcdata-archive under namespace sejs. Flux successfully reconciled it. CronJob btcdata-archive remains suspended, with Monday 00:00 UTC schedule, Citadel affinity, and the specified resource/storage limits. Existing feature-branch work in the deploy repository was preserved; rollout was committed from a separate main-based worktree.

Deployed image (tested CI commit d93735b): harbor.pmagnus.cloud/btcdata-archive/btcdata-archive@sha256:9d297326b4dc0d997a344b7353ee48257632be8020094a3a29f02b268e9f8a81. Secrets btcdata-archive-credentials and btcdata-archive-pull were created in sejs outside Git, using the restricted PostgreSQL/S3 identities and a project-scoped Harbor pull-only identity. CI uses a separate project-scoped push/pull robot.

Validation Job btcdata-archive-verify-20261003193601 completed successfully on Citadel using this image, run --mode verify-only --max-weeks 1, non-root execution, read-only root filesystem and the actual work volume. There were no eligible weeks because the initial catch-up had completed, so it validated schema, lock, database access, S3 versioning and ledger discovery without uploading or pruning new data. The full export/upload/exact-version-download/prune/recovery path passed in CI.

Enabling suspend: false is a separate explicit approval/GitOps step. No recurring archival/pruning schedule was enabled during deployment. Update image digests explicitly; this app has no floating-tag image automation.

Operator setup and rollout reference

1. Run disposable integration tests. Inspect live schemas, constraints/DELETE triggers, RLS, existing indexes, source volumes/per-week sizes, source role privileges, RustFS disk capacity, versioning support and pod LAN connectivity. Live schema remains authoritative; migration references alone are not a production preflight.
2. Create a restricted PostgreSQL LOGIN through secure administrative tooling. Run psql -v ON_ERROR_STOP=1 -v runtime_role=btcdata_archiver -f sql/setup.sql against the chosen database as its administrator. This creates only the ledger schema/table and grants SELECT/DELETE on the fixed source tables, TEMP, and ledger SELECT/INSERT/UPDATE. Runtime does not create roles/schemas or run migrations. Verify inherited/public privileges do not confer extra source writes or ownership.
3. Inspect equivalent indexes before running sql/indexes.sql, outside a transaction. Inspect interrupted/invalid concurrent builds before retrying. Reuse Kraken's TS PK.
4. With an administrative RustFS identity, create bucket btcdata-archive and enable versioning. Verify version PUT/GET behavior against the deployed RustFS build. Attach sql/s3-runtime-policy.json to a dedicated bucket-scoped identity; validate RustFS IAM compatibility. Runtime can inspect versioning but cannot enable it, administer the bucket, or delete objects/versions. No expiry/lifecycle rules. Provisioning and CI image pushes require separate credentials, never runtime keys.
5. GitOps artifacts are in magnus/deploy main at apps/btcdata-archive/, registered in the parent apps kustomization and currently suspended with a tested immutable digest. For upgrades, publish a tested CI image and update the digest explicitly. Provision Secrets in sejs through out-of-Git tooling; namespace references cannot cross namespaces. Never commit runtime or registry credentials.
6. Confirm requests/limits/work capacity against the largest old week. Initial work limit is 8 GiB; disk estimates are conservative, not a capacity guarantee. Limit whole-week files require an increase for large weeks; streaming is not parallelism.
7. After explicit approval, deploy suspended; verify-only one oldest week, download and independently restore/compare it. Then authorize/run the unlimited oldest-first prune catch-up script. Stop on the first failure. Check source/ledger/object agreement and fixed cutoff before enabling the Monday 00:00 UTC CronJob through GitOps.

Suspend to stop future schedules; terminate active work gracefully (SIGTERM exits nonzero and closes the lock session). SIGKILL can leave local temporary files; disposable pod storage is cleared with the pod. Standalone operators may remove orphan btcdata-week-* directories only when no archiver is running; they are not checkpoints. Retries use object versions and ledger, not these files. Failed multipart uploads may need administrative inspection; never expire completed versions.

Rollback cannot undo committed deletions. Preserve archives and ledger, suspend the CronJob and restore verified versions through an explicit recovery procedure if needed. Use normal autovacuum/vacuum for reusable space. VACUUM FULL, filesystem shrinking, partitioning, notification integrations and historical-query APIs are out of scope.