infrastructure

commit 521d4fa653e1a46518fb7470dd56c44d050e0c94
parent 3ea34c191980f9e27bbc529cddfc42029d324cf3
Author: Silas Brack <silasbrack@gmail.com>
Date:   Sat,  4 Jul 2026 22:12:13 +0200

feat: add manifest.csv for DuckDB httpfs log queries

Remove manifest.csv generation from log archival timer.
Add query-logs wrapper to dev shell for querying logs via DuckDB.

Usage: query-logs [since] [until] [sql]
  query-logs                              # last 24h, default query
  query-logs 2026-07-01T00 2026-07-02T00  # specific range
  query-logs 2026-07-01T00 2026-07-02T00 "SELECT * FROM logs WHERE message ILIKE '%error%'"

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

Diffstat:
M
flake.nix
 | 
33
+++++++++++++++++++++++++++++++++
M
modules/simple-web-app.nix
 | 
5
-----

2 files changed, 33 insertions(+), 5 deletions(-)
diff --git a/flake.nix b/flake.nix
@@ -76,6 +76,38 @@
           pkgs = import nixpkgs {
             inherit system;
           };
+          query-logs = pkgs.writeShellScriptBin "query-logs" ''
+            set -euo pipefail
+            BASE_URL="http://10.100.0.7:9090"
+            SINCE="''${1:-$(date -d '24 hours ago' +%Y-%m-%dT%H)}"
+            UNTIL="''${2:-$(date +%Y-%m-%dT%H)}"
+            SQL="''${3:-SELECT * FROM logs ORDER BY timestamp DESC LIMIT 50}"
+
+            # Generate hourly URLs between SINCE and UNTIL
+            urls=""
+            current=$(date -d "''${SINCE}:00:00" +%s)
+            end=$(date -d "''${UNTIL}:00:00" +%s)
+            while [ "$current" -le "$end" ]; do
+              path=$(date -d "@$current" +%Y/%m/%d/%H)
+              urls="$urls '$BASE_URL/$path.json.gz',"
+              current=$((current + 3600))
+            done
+            urls="[''${urls%,}]"
+
+            ${pkgs.duckdb}/bin/duckdb -c "
+              LOAD httpfs;
+              CREATE TEMP TABLE logs AS
+                SELECT
+                  json_extract_string(j, '\$.MESSAGE') as message,
+                  json_extract_string(j, '\$.__REALTIME_TIMESTAMP')::BIGINT / 1000000 as epoch,
+                  to_timestamp(json_extract_string(j, '\$.__REALTIME_TIMESTAMP')::BIGINT / 1000000) as timestamp,
+                  json_extract_string(j, '\$.PRIORITY') as priority,
+                  json_extract_string(j, '\$.SYSLOG_IDENTIFIER') as identifier
+                FROM read_json_auto($urls, format='newline_delimited', ignore_errors=true) j;
+              $SQL;
+            "
+          ''
+          ;
         in
         pkgs.mkShell {
           buildInputs = with pkgs; [
@@ -88,6 +120,7 @@
             squawk
             nixfmt-rfc-style
             forgejo-cli
+            query-logs
           ];
         };
     };
diff --git a/modules/simple-web-app.nix b/modules/simple-web-app.nix
@@ -88,11 +88,6 @@ in
           -o json | \
           ${pkgs.gzip}/bin/gzip \
           > $LOG_STORAGE_LOCATION/$(date -d "$PREVIOUS_HOUR" +%Y/%m/%d/%H).json.gz
-
-        # Rebuild manifest for DuckDB httpfs queries
-        ${pkgs.findutils}/bin/find $LOG_STORAGE_LOCATION -name '*.json.gz' -printf '%P\n' \
-          | ${pkgs.coreutils}/bin/sort \
-          > $LOG_STORAGE_LOCATION/manifest.csv
       '';
       environment = {
         SERVICE_NAME = "simple-web-app";