Scribbles for my memory

A machine can have normal network connectivity while GitHub Actions sees its self-hosted runner as offline. The runner listener is a separate process, and GitHub may delete a registration after it has been disconnected for a long time. The local .runner file and systemd unit can still remain, which makes the failure easy to miss.

Make ordinary crashes and reboots self-healing with a systemd drop-in:

[Unit]
StartLimitIntervalSec=0

[Service]
Restart=always
RestartSec=10s

Then reload systemd and restart the runner. Check both systemctl is-active locally and the runner status in GitHub. If the log says the registration was deleted or asks you to re-configure, a restart is not enough: obtain a fresh registration token and run config.sh --replace before enabling the service again.

My tweet-queue service listens only on its Tailscale IP. Great for safety, annoying when I'm off the tailnet and want to post. The fix is not to rebind the service — it's to put a public nginx box that is also on the tailnet in front of it, and expose just the one path behind basic auth.

On the edge host (already a tailnet member, with a wildcard *.dev DNS record pointing at it):

server {
    listen 80;
    server_name x.dev.example.com;
    client_max_body_size 32m;          # match the backend's own body limit

    location = /add {
        auth_basic           "x-auto-poster";
        auth_basic_user_file /etc/nginx/.htpasswd;   # reuse the existing user file
        proxy_pass http://100.91.4.56:8090;          # backend's Tailscale IP
        proxy_set_header Authorization "";           # don't leak Basic creds upstream
    }

    location / { return 404; }
}

Then sudo certbot --nginx -d x.dev.example.com --redirect adds TLS and the HTTP→HTTPS redirect. With wildcard DNS there's no DNS step at all.

Three details that matter:

• location = /add plus a catch-all 404 means only the endpoint you meant to share is reachable — not every route the backend happens to have.
• proxy_set_header Authorization "" stops nginx's Basic header from reaching the backend. If the backend ever grows its own auth (or uses Authorization for something else, like an API key selector), a forwarded Basic header produces confusing 401/403s.
• Reusing one .htpasswd across sites is convenient, but every user in it can now hit every site that points at it. Fine for a single-user box; split the file once that stops being true.

Test it from outside: no creds should give 401, other paths 404.

curl -s -o /dev/null -w "%{http_code}\n" https://x.dev.example.com/add   # 401
curl -u user:pass -X POST https://x.dev.example.com/add \
  -H 'Content-Type: application/json' -d '{"text":"hello from anywhere"}'

Oracle 19c has no filtered index like SQL Server's WHERE clause or PostgreSQL's partial index. You can get the same effect from one B-tree rule: a row gets no index entry only when every indexed column is NULL.

A common misreading of that rule is "if the first column is NULL, the row isn't indexed." That's wrong. I checked it with a unique index on (A, B), inserting each pair twice:

CREATE TABLE t (A NUMBER, B NUMBER);
CREATE UNIQUE INDEX t_uq ON t (A, B);

INSERT INTO t VALUES (NULL, NULL);  -- twice: both succeed
INSERT INTO t VALUES (NULL, 5);     -- twice: ORA-00001 on the second
INSERT INTO t VALUES (5, NULL);     -- twice: ORA-00001 on the second

After gathering stats, the table had 4 rows and the index had 2 entries. The two missing entries were the all-NULL rows. A leading NULL gets indexed and takes part in the uniqueness check like any other value.

To index only the rows that matter, wrap every column in the same condition with a CASE expression. Rows that fail the condition then have an all-NULL key and get no entry:

-- one open account per (parent, type); closed rows are left out of the index
CREATE UNIQUE INDEX acct_one_open_uq ON acct (
    CASE WHEN status <> 'CLOSED' THEN parent_id END,
    CASE WHEN status <> 'CLOSED' THEN type_id END
);

The index stays as small as the set of open rows, and you can keep as many closed duplicates as you like. If you wrap only the first column, closed rows still get a (NULL, type_id) entry. The index then keeps every row, and two closed rows of the same type collide.

The catch: the optimizer only uses this index when a query repeats the exact CASE expression. A plain WHERE parent_id = :p AND type_id = :t won't use it. Treat it as a constraint, and keep a normal index if you also need lookups on those columns.

For backing up small state files (a SQLite control.db, config, keys), Cloudflare R2's S3-compatible API drags in a client, signing and a separate pair of S3 credentials. The plain Cloudflare REST API can PUT and GET objects directly — one curl, one API token, no SDK:

curl -sS -X PUT "https://api.cloudflare.com/client/v4/accounts/$ACCOUNT_ID/r2/buckets/$BUCKET/objects/$KEY" \
  -H "Authorization: Bearer $TOKEN" \
  --data-binary @snapshot.tar.age -H "Content-Type: application/octet-stream"

GET is the same with -o snapshot.tar.age, DELETE with -X DELETE. No egress fees on R2, and the free tier (10 GB storage, 1M Class A writes/month) dwarfs an hourly-snapshot workload.

Minting the token needs no dashboard click either — the Global API Key can do it through the API. The shape that finally worked (after a few 400s worth of resources-expression spelunking):

curl -sS -X POST "https://api.cloudflare.com/client/v4/user/tokens" \
  -H "X-Auth-Key: $CF_API_KEY" -H "X-Auth-Email: $CF_API_EMAIL" \
  -H "Content-Type: application/json" --data '{
  "name": "snapshots-backup",
  "policies": [{"effect": "allow",
    "resources": {"com.cloudflare.api.account.<ACCOUNT_ID>": "*"},
    "permission_groups": [
      {"id": "b4992e1108244f5d8bfbd5744320c2e1"},
      {"id": "bf7481a1826f439697cb59a20b22293e"}
    ]}],
  "expires_on": "2027-10-17T00:00:00Z"
}'

Those two group ids are Workers R2 Storage Read and Write. The response carries the token value exactly once — pipe it straight to its destination file (umask 077, owner root, 0600) and never let it touch a terminal or a shell variable you might echo.

Two traps when verifying from the host that holds the credential:

• ssh -n redirects stdin from /dev/null and silently wins over < file — the credential file lands 0 bytes. Drop -n when piping stdin.
• If the credential file is root-only, curl -H "Bearer $(cat ...)" in your ssh shell reads nothing and the API answers 9106: Missing Authorization header. Run the whole probe under sudo sh -c so the expansion happens where the file is readable.

Encrypt before upload so the host leak doesn't matter: age with the public key on the host and the private key in your password manager. Restore = GET on the operator machine, decrypt, PRAGMA integrity_check.

I wanted to search a year of audit history for one rare value. The table has an index on the timestamp, so I assumed a date filter would make the query cheap. The optimizer disagreed, and it was right.

Check the plan at a few window sizes instead of guessing:

explain plan for
select count(*) from audit_tbl a
 where sys_extract_utc(a.a_date_) >= sys_extract_utc(systimestamp) - interval '30' day(3)
   and a.some_col in ('...');

select * from table(dbms_xplan.display(null, null, 'BASIC +COST +ROWS'));

On a 6.2M-row, 523k-block table the results were:

• 7 days: index range scan, cost ~96K
• 30 days: full table scan, cost ~234K
• 90 days and 1 year: full table scan, cost ~234K

The two access paths scale differently. A full scan reads every block sequentially, many blocks per I/O, so its cost is the same whatever the window. An index range scan is cheap at the index itself. After that it fetches each matching row's table block one at a time, so its cost grows with the number of rows in the window.

How expensive those fetches are depends on the index's clustering factor:

select index_name, clustering_factor, num_rows from all_indexes where table_name = 'AUDIT_TBL';

A value close to the table's block count means rows with nearby keys sit together, so one block read serves many rows. A value close to the row count means nearly every row needs its own block read. Mine was 3.35M against 523k blocks, which is badly scattered. The break-even point landed somewhere between 7 and 30 days. Past that, forcing the index with a hint makes the query slower, not faster.

One related trap: if the index is function-based, for example on SYS_EXTRACT_UTC(a_date_), the predicate has to use the same expression. A plain a_date_ >= ... can't use that index at any window size.