Skip to content

MariaDB → DuckDB analytics snapshots

The extractor uses DuckDB's signed MySQL extension to read MariaDB directly. It projects approved columns in MariaDB, writes a compressed .duckdb database, and generates SCHEMA.md, manifest.json, and a copy of columns.yaml. Python orchestrates SQL; it does not fetch/convert data rows.

Dependencies

nix run .#export-analytics-db -- --help
# Alternatively (also includes the AWS CLI for R2):
nix develop .#analytics

Nix pins Python/DuckDB through flake.lock. The matching, signed MySQL extension is downloaded from https://extensions.duckdb.org on first use using Python's HTTPS client, then installed with DuckDB's signature verification. This avoids DuckDB 1.4's cold-cache HTTPS/httpfs bootstrap problem. Extension files are cached outside the snapshot; building the Nix application alone does not prefetch the extension, so first use needs network access.

1. Clone production locally

bin/clone-db --with-invites --with-all-reviews \
  --with-review-translations --with-webshop-orders

This replaces the local wwk_keurmerk database. It does not modify application records in production, but bin/dump-db temporarily clones/sanitizes leden on production as part of its existing implementation.

Even with these flags, the existing dump script omits data in logins, messenger_messages, growthbook_conversion_events, growthbook_viewed_experiments, host_checks, and vat_validations. The clone also replaces admin passwords and inserts a development Twilio number. It is not a complete or exact production snapshot. The extractor preserves empty base tables and reports their actual counts; it does not silently refill gaps. The dump does not promise a single consistent production snapshot.

The raw SQL clone still contains sensitive data. Keep it local/private; never upload it to R2. The DuckDB exporter is a separate sanitization step.

2. YAML column include list

The current include list is version-controlled in config/analytics-columns.yaml. Use it for exports; edit it in a PR to change which data is shared. To generate a fresh candidate from a changed source schema, use --plan as shown below, then review the diff before updating the checked-in list. Regeneration includes new fields by default and is not an automatic production step.

Create a private DSN file outside Git (the data/ directory is ignored):

mkdir -p data/analytics
(umask 077; printf '%s\n' \
  'host=127.0.0.1 port=18175 user=root database=wwk_keurmerk' \
  > data/analytics/mysql.dsn)

nix run .#export-analytics-db -- \
  --connection-file data/analytics/mysql.dsn \
  --plan data/analytics/columns.yaml

Prefer a SELECT-only MariaDB account for a real production connection. The attachment is read-only, but that is not a substitute for account permissions. Do not put credentials on the command line, in the policy, or in documentation. Connector error details are suppressed because they can contain the DSN.

The file is just a table-to-column-list mapping, without version, review flags, fingerprints or per-column actions/reasons:

invites:
    - id
    - shop
    - sent
leden:
    - id
    - package_id

Remove a column to exclude it, omit a table or use table: [] to exclude the table. Listed tables/columns must exist; duplicates and malformed lists are rejected. New source tables/columns remain excluded until explicitly added. Source fingerprints are still recorded in the export manifest for provenance; there is no schema-fingerprint gate in the YAML.

Generation initially includes everything in base tables except:

  • Obvious passwords/credentials by column name (passwords, tokens, secrets, TOTP fields, private/public keys, hashes, verification/tracking codes, api).
  • Direct consumer email, phone, name, address, IP and user-agent fields on invites, reviews, disputes and other consumer-facing tables listed in the extractor. Both email and e-mail spellings are handled.

Member/shop identities, bank details, order identifiers, free text, JSON and binary payloads are retained. No pseudonymization or content redaction is performed. This is NOT a PII-free export: review text, notes and order data may contain consumer identifiers or embedded credentials. Review the list and privacy requirements before sharing externally or sending results to Claude. The include list is the authority; editing it can intentionally include a field omitted by the generator. There is no reviewed flag or separate approval gate.

Source views are documented but not executed/exported. View definitions are not included, avoiding embedded literals and unreviewed SQL in the artifact.

3. Build the snapshot

nix run .#export-analytics-db -- \
  --connection-file data/analytics/mysql.dsn \
  --include config/analytics-columns.yaml \
  --output data/analytics/snapshot-2026-10-06 \
  --memory-limit 2GB --threads 2

The output directory must not already exist. Exports use private permissions, one REPEATABLE READ source transaction, and an atomic directory rename after commit/checkpoint/close. Failed runs retain a clearly named private .partial-* directory for inspection/removal, never a finished snapshot. There is no resume mode; restart into a new output directory.

A long source snapshot can retain InnoDB undo history and scanning every table creates I/O load. Prefer exporting from the local clone. No DuckDB indexes or source constraints are copied; foreign/primary keys are documented for Claude. Incomplete MySQL dates become NULL, TIMESTAMPs are read in UTC, and source types are documented (DuckDB's mapped types can differ, e.g. MySQL TIME).

SCHEMA.md includes tables/views, source column types, exclusions with reasons, primary/unique/foreign keys, key application-level invitation relationships, row counts, snapshot timing and policy fingerprints. manifest.json contains exact exported counts and per-table timings. No source DSN or data samples are included.

4. Upload to private Cloudflare R2

Use bucket-scoped upload credentials via the standard AWS credential chain (environment variables or a private AWS credentials file). Give the analyst separate read-only credentials. Use a new dated prefix for each snapshot. Keep the bucket private and configure a retention policy.

nix develop .#analytics
export R2_ENDPOINT_URL=https://ACCOUNT_ID.r2.cloudflarestorage.com
export AWS_DEFAULT_REGION=auto
# Configure AWS_PROFILE or AWS_ACCESS_KEY_ID/AWS_SECRET_ACCESS_KEY privately.
snapshot=data/analytics/snapshot-2026-10-06
prefix=s3://BUCKET/snapshots/2026-10-06

# Upload only the finished artifacts, never mysql.dsn, raw SQL or partial files.
for file in analytics.duckdb SCHEMA.md columns.yaml manifest.json; do
  aws --endpoint-url "$R2_ENDPOINT_URL" s3 cp "$snapshot/$file" "$prefix/$file" \
    --cache-control no-store --no-progress
done

Upload the manifest last as a completion marker. The analyst downloads the DuckDB file and SCHEMA.md, then opens the database read-only. DuckDB does not query a remote .duckdb file directly from R2; remote SQL over object storage would instead use Parquet. Restrict filesystem/network permissions in the Claude integration too: read-only database access is not a sandbox. Query results sent to Claude are data disclosures and still require an appropriate privacy policy.

Deployment

This is a standalone export job, not an application/database migration. Merge the PR and run the Nix app on a designated export host. Before enabling uploads or a schedule, configure:

  • A source MariaDB connection file (mode 0600), preferably using a dedicated SELECT-only account. The session must default to REPEATABLE READ.
  • A private R2 bucket/prefix, account endpoint, and bucket-scoped upload credentials. Analysts get separate read-only credentials.
  • An agreed data-sharing policy: the checked-in list still retains member/shop details and free text/JSON, and must not be described as PII-free.
  • Refresh schedule/off-peak window, retention, and an owner for failed runs.
  • At least the configured 2 GB DuckDB memory budget plus process overhead and sufficient local workspace. The full local test took about 4.5 minutes and produced a 3.30 GiB file (232 tables, roughly 74 million rows). Allow additional temporary space and old snapshots; cloning MariaDB needs substantially more.

The export host needs outbound HTTPS as well as access to MariaDB and R2. Long snapshots hold InnoDB undo history; monitor production load if exporting there rather than from a clone/replica. Configure R2 retention separately; failed attempts may leave an incomplete object prefix without a manifest.

User-level systemd publication service

systemd/analytics-snapshot.service runs nix run .#analytics-snapshot from ~/public_html. Deployment links the service and reloads systemd, but does not start/restart it or enable a timer. Configure analytics-snapshot.timer manually to target this service.

The service reads ~/etc/analytics as a systemd EnvironmentFile (plain KEY=value, not shell export statements):

AWS_PROFILE=r2-analytics
AWS_DEFAULT_REGION=auto
R2_ENDPOINT_URL=https://ACCOUNT_ID.r2.cloudflarestorage.com
R2_BUCKET=analytics
R2_PREFIX=snapshots
HC_API_URL=https://YOUR_HEALTHCHECKS_PING_ENDPOINT
CHECK_UUID=YOUR_CHECK_UUID

The service wraps the entire Nix command with /usr/bin/env runitor -no-output-in-ping nix run .#analytics-snapshot. It uses the configured user manager's PATH to resolve runitor and Nix, and HC_API_URL / CHECK_UUID from the EnvironmentFile to send start/success/failure pings. Command output is not sent in those pings (runitor may include its own exit-status diagnostic); journal output and the existing Telegram failure report remain available. Runitor must be installed in the export user's PATH. Keep ~/etc/analytics mode 0600 because the check UUID is a ping credential.

Keep upload credentials in ~/.aws/credentials (mode 0600), not in the repository. The service defaults to local Unix-socket authentication with user=%u database=%u socket=/run/mysqld/mysqld.sock; on the dash host this is the existing dash account and dash database. For a dedicated SELECT-only account or another source, add ANALYTICS_MYSQL_DSN_FILE=/home/dash/etc/analytics-mysql.dsn to the settings and put the DSN in that separate mode-0600 file. MYSQL_DSN in the EnvironmentFile can also override the default, but prefer a private file for passwords.

Other optional settings:

  • ANALYTICS_INCLUDE_FILE: override the immutable checked-in include list bundled with the Nix application.
  • ANALYTICS_SNAPSHOT_DIR: workspace (default ~/.cache/analytics-snapshots, outside the web root).
  • ANALYTICS_MEMORY_LIMIT / ANALYTICS_THREADS: default 2GB / 2.

Each attempt takes a local lock, exports into private working files, and uploads only analytics.duckdb, SCHEMA.md, columns.yaml, then manifest.json to a unique UTC timestamp/random-suffix R2 prefix. A prefix is complete only when its manifest exists. Working files are deleted after successful publication; failures retain them privately for investigation/retry. No mutable latest snapshot is maintained. The service reports failures through the existing report-job-status.py Telegram integration, has a two-hour timeout and a 4 GiB memory cap, and runs at reduced CPU/I/O priority.

After deployment, test a publication explicitly:

systemctl --user start analytics-snapshot.service
journalctl --user -u analytics-snapshot.service -n 50

This command exports production data and uploads it; it is not a dry run. The timer should be enabled only after checking the publication and approving the data-sharing scope. For a manual CLI run, load the non-secret settings explicitly:

set -a
. "$HOME/etc/analytics"
set +a
export MYSQL_DSN='host=localhost user=dash database=dash socket=/run/mysqld/mysqld.sock'
nix run .#analytics-snapshot

Tests

make test-analytics

# Integration tests create/drop a uniquely named fixture database locally only:
ANALYTICS_TEST_DSN='host=127.0.0.1 port=18175 user=root database=mysql' \
  make test-analytics

Integration coverage includes cold-cache signed-extension installation, joins, exclusions, documentation, schema drift, read-only protection, YAML validation, retention of text fields, exclusion of new unlisted columns, and preservation of a remote snapshot across concurrent MariaDB updates. Publication tests use fake exporter/AWS clients, with no real R2 writes: they check upload order, unique prefixes, locking, failure retention, cleanup, DSN-file overrides, and link-only deployment behavior. Runitor tests use a local HTTP server to verify start/success/failure pings, exit-code propagation, and suppression of command output without pinging the configured check.