Files
bradleyshep fb0a458d4f LLM Benchmark: Sequential Upgrades Test (#4817)
# Description of Changes

AI app generation benchmark comparing SpacetimeDB vs PostgreSQL (Express
+ Socket.io + Drizzle ORM). Same AI model (Claude Sonnet 4.6), same
prompts, same chat app, two backends. Upgraded through 12 feature
levels, manually graded at each level, bugs fixed, all costs measured
via OpenTelemetry.

Results viewable at:
https://spacetimedb.com/llms-benchmark-sequential-upgrade

## Benchmark harness (`tools/llm-sequential-upgrade/`)

- `run.sh`: orchestrates headless Claude Code sessions for code
generation, sequential upgrades, and bug fixes. Tracks all API costs via
OTel. Supports `--upgrade`, `--fix`, `--composed-prompt`,
`--resume-session` modes.
- `grade.sh` / `grade-agents.sh`: grading harnesses for manual testing
of generated apps.
- `docker-compose.otel.yaml`: OTel collector + PostgreSQL services.
- `generate-report.mjs` / `parse-telemetry.mjs`: aggregate per-session
telemetry into cost reports.
- Backend guidelines in `backends/`: SpacetimeDB SDK reference, config
templates, server setup docs, PostgreSQL setup with Drizzle/Socket.io
guidance.
**After https://github.com/clockworklabs/SpacetimeDB/pull/4740 merges,
we will likely want to update this so that it reads backend and SDK
guidance from SKILLS**

## Two complete benchmark runs

**Run 1 (20260403):** Original methodology.
**Run 2 (20260406):** Refined methodology with domain bias removed from
SpacetimeDB SDK docs and PostgreSQL instructions made
feature-spec-neutral.
**Note: no meaningful changes in results were observed with these
changes. Domain familiarity biases were very small and almost certainly
not the cause of STDB's major gains over PG stack.**

Each run contains full L1-L12 app source for both backends, level
snapshots preserving state before each upgrade, and per-session OTel
cost summaries.

## 12 feature levels

| Level | Feature |
|---|---|
| L1 | Basic Chat + Typing + Read Receipts + Unread Counts |
| L2 | Scheduled Messages |
| L3 | Ephemeral Messages |
| L4 | Message Reactions |
| L5 | Message Editing with History |
| L6 | Real-Time Permissions (kick, ban, promote) |
| L7 | Rich User Presence |
| L8 | Message Threading |
| L9 | Private Rooms + Direct Messages |
| L10 | Room Activity Indicators |
| L11 | Draft Sync |
| L12 | Anonymous to Registered Migration |

## Results

| | Run 1 (20260403) | Run 2 (20260406) |
|---|---|---|
| **SpacetimeDB total cost** | $13.33 | $12.62 |
| **PostgreSQL total cost** | $17.80 | $19.68 |
| **SpacetimeDB bugs** | 5 | 2 |
| **PostgreSQL bugs** | 19 | 8 |
| **SpacetimeDB fix sessions** | 4 | 1 |
| **PostgreSQL fix sessions** | 17 | 10 |

Both runs agree: SpacetimeDB apps are cheaper to build, have fewer bugs,
and require fewer fix iterations. The refined methodology (Run 2)
widened the cost gap and **confirmed the advantage is structural, not an
artifact of domain-biased SDK docs.**

## Performance benchmark (`perf-benchmark/`)

Stress throughput tool that fires concurrent writers at peak saturation
against the AI-generated `send_message` handlers.

| Tier | SpacetimeDB (avg) | PostgreSQL (avg) | Ratio |
|---|---|---|---|
| AI-generated (as-shipped) | 5,267 msgs/sec | 694 msgs/sec | 7.6x |
| PG rate limit removed | 5,267 msgs/sec | 1,070 msgs/sec | 4.9x |
| Optimized (same features kept) | 25,278 msgs/sec | 1,139 msgs/sec |
22x |

The gap widens with optimization because SpacetimeDB's bottleneck is
fixable code patterns in the reducer while PostgreSQL's bottleneck is
architectural (sequential network round-trips to an external database).

Optimized reference code with all features preserved is in
`perf-benchmark/results/optimized-reference/`.

## Data handling

Per-session cost summaries (`cost-summary.json`, `COST_REPORT.md`,
`metadata.json`) are committed. Raw OTel telemetry
(`raw-telemetry.jsonl`) containing PII is excluded via `.gitignore` and
stored privately.

# API and ABI breaking changes

None. All changes are in `tools/llm-sequential-upgrade/`. No production
code, library, or SDK changes.

# Expected complexity level and risk

**1 - Trivial.** Self-contained benchmarking tooling and data. No
interaction with production code.

# Testing

- [x] L1-L12 upgrades completed on all 4 apps (2 backends x 2 runs) with
OTel cost capture
- [x] All levels manually graded after each upgrade; bugs filed and
fixed via the harness
- [x] Methodology refinement between runs validated (domain bias
removal, feature-neutral instructions)
- [x] Stress benchmarks run across both runs x 3 tiers (as-shipped,
rate-limit-removed, optimized)
- [x] Optimized benchmarks verified to preserve all original features
- [x] Sensitive data (PII in raw telemetry) removed from repo and
gitignored
- [ ] Reviewer: spot-check that METRICS_DATA.json / METRICS_REPORT.json
numbers match the telemetry cost-summary.json files

---------

Co-authored-by: Tyler Cloutier <cloutiertyler@users.noreply.github.com>
Co-authored-by: clockwork-labs-bot <clockwork-labs-bot@users.noreply.github.com>
2026-06-10 16:37:33 +00:00

238 lines
7.8 KiB
Bash

#!/bin/bash
# Exhaust Loop — Full generate → grade → fix cycle for a single run.
#
# Drives one backend through the complete benchmark:
# 1. Generate (or upgrade) the app
# 2. Grade with Chrome MCP
# 3. If bugs: fix and re-grade (repeat until pass or max iterations)
# 4. For sequential: upgrade to next level, repeat from step 2
#
# Usage:
# ./run-loop.sh --backend spacetime --level 7 --rules standard --run-index 0
# ./run-loop.sh --backend postgres --variant one-shot --level 7 --run-index 1
# ./run-loop.sh --backend spacetime --variant sequential-upgrade --level 12 --run-index 0
#
# Grading uses Chrome MCP (interactive Claude Code session).
# A lock file serializes grading across parallel runs.
set -euo pipefail
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
# ─── Parse arguments ─────────────────────────────────────────────────────────
BACKEND="spacetime"
VARIANT="one-shot"
LEVEL=7
RULES="guided"
TEST_MODE=""
RUN_INDEX=0
MAX_FIX_ITERATIONS=5
while [[ $# -gt 0 ]]; do
case $1 in
--backend) BACKEND="$2"; shift 2 ;;
--variant) VARIANT="$2"; shift 2 ;;
--level) LEVEL="$2"; shift 2 ;;
--rules) RULES="$2"; shift 2 ;;
--test) TEST_MODE="$2"; shift 2 ;;
--run-index) RUN_INDEX="$2"; shift 2 ;;
--max-fixes) MAX_FIX_ITERATIONS="$2"; shift 2 ;;
*) echo "Unknown option: $1"; exit 1 ;;
esac
done
TEST_FLAG=""
if [[ -n "$TEST_MODE" ]]; then
TEST_FLAG="--test $TEST_MODE"
fi
LOCK_FILE="$SCRIPT_DIR/.grade-lock"
LOG_PREFIX="[run-$RUN_INDEX/$BACKEND]"
echo "═══════════════════════════════════════════"
echo "$LOG_PREFIX Exhaust Loop"
echo " Backend: $BACKEND"
echo " Variant: $VARIANT"
echo " Level: $LEVEL"
echo " Rules: $RULES"
echo " Run index: $RUN_INDEX"
echo " Max fixes: $MAX_FIX_ITERATIONS"
echo "═══════════════════════════════════════════"
# ─── Helper: acquire grading lock ────────────────────────────────────────────
# Only one grading session at a time (Chrome MCP limitation).
acquire_grade_lock() {
echo "$LOG_PREFIX Waiting for grading lock..."
while ! mkdir "$LOCK_FILE" 2>/dev/null; do
sleep 5
done
echo "$LOG_PREFIX Grading lock acquired"
}
release_grade_lock() {
rmdir "$LOCK_FILE" 2>/dev/null || true
}
# Clean up lock on exit
trap 'release_grade_lock' EXIT
# ─── Helper: grade the app ──────────────────────────────────────────────────
grade_app() {
local app_dir="$1"
local grade_level="$2"
acquire_grade_lock
echo "$LOG_PREFIX Grading at level $grade_level..."
"$SCRIPT_DIR/grade.sh" "$app_dir" 2>&1 | tee "$app_dir/grade-output-level${grade_level}.log"
release_grade_lock
# Check if bugs were found
if [[ -f "$app_dir/BUG_REPORT.md" ]]; then
echo "$LOG_PREFIX Bugs found — fix iteration needed"
return 1
else
echo "$LOG_PREFIX All features passed at level $grade_level"
return 0
fi
}
# ─── Helper: fix bugs ───────────────────────────────────────────────────────
fix_bugs() {
local app_dir="$1"
local iteration="$2"
echo "$LOG_PREFIX Fix iteration $iteration..."
"$SCRIPT_DIR/run.sh" \
--fix "$app_dir" \
--variant "$VARIANT" \
--rules "$RULES" \
$TEST_FLAG \
--run-index "$RUN_INDEX" \
--level "$LEVEL" \
--resume-session \
2>&1 | tee "$app_dir/fix-output-iter${iteration}.log"
}
# ─── ONE-SHOT FLOW ──────────────────────────────────────────────────────────
if [[ "$VARIANT" == "one-shot" ]]; then
echo "$LOG_PREFIX === One-Shot: Generating all features ==="
# Step 1: Generate
"$SCRIPT_DIR/run.sh" \
--variant "$VARIANT" \
--rules "$RULES" \
$TEST_FLAG \
--backend "$BACKEND" \
--run-index "$RUN_INDEX" \
--level "$LEVEL"
# Find the app directory
APP_DIR=$(ls -dt "$SCRIPT_DIR/$VARIANT"/*"/$BACKEND/results"/chat-app-* 2>/dev/null | head -1)
if [[ -z "$APP_DIR" || ! -d "$APP_DIR" ]]; then
echo "$LOG_PREFIX ERROR: Could not find generated app directory"
exit 1
fi
echo "$LOG_PREFIX App dir: $APP_DIR"
# Step 2: Grade → Fix loop
ITERATION=0
while true; do
if grade_app "$APP_DIR" "$LEVEL"; then
echo "$LOG_PREFIX === One-Shot Complete: All features pass ==="
break
fi
ITERATION=$((ITERATION + 1))
if [[ $ITERATION -ge $MAX_FIX_ITERATIONS ]]; then
echo "$LOG_PREFIX === Max fix iterations ($MAX_FIX_ITERATIONS) reached ==="
break
fi
fix_bugs "$APP_DIR" "$ITERATION"
done
# ─── SEQUENTIAL-UPGRADE FLOW ────────────────────────────────────────────────
else
echo "$LOG_PREFIX === Sequential Upgrade: Levels 1 → $LEVEL ==="
# Step 1: Generate level 1
echo "$LOG_PREFIX Generating level 1..."
"$SCRIPT_DIR/run.sh" \
--variant "$VARIANT" \
--rules "$RULES" \
--backend "$BACKEND" \
--run-index "$RUN_INDEX" \
--level 1
APP_DIR=$(ls -dt "$SCRIPT_DIR/$VARIANT"/*"/$BACKEND/results"/chat-app-* 2>/dev/null | head -1)
if [[ -z "$APP_DIR" || ! -d "$APP_DIR" ]]; then
echo "$LOG_PREFIX ERROR: Could not find generated app directory"
exit 1
fi
echo "$LOG_PREFIX App dir: $APP_DIR"
# Grade level 1
ITERATION=0
while ! grade_app "$APP_DIR" 1; do
ITERATION=$((ITERATION + 1))
if [[ $ITERATION -ge $MAX_FIX_ITERATIONS ]]; then
echo "$LOG_PREFIX Max fixes at level 1 — moving on"
break
fi
fix_bugs "$APP_DIR" "$ITERATION"
done
# Step 2: Upgrade through remaining levels
for current_level in $(seq 2 "$LEVEL"); do
PROMPT_EXISTS=$(ls "$SCRIPT_DIR/../llm-oneshot/apps/chat-app/prompts/composed/$(printf '%02d' "$current_level")_"*.md 2>/dev/null | head -1)
if [[ -z "$PROMPT_EXISTS" ]]; then
echo "$LOG_PREFIX No prompt for level $current_level — stopping"
break
fi
echo "$LOG_PREFIX === Upgrading to level $current_level ==="
"$SCRIPT_DIR/run.sh" \
--variant "$VARIANT" \
--rules "$RULES" \
$TEST_FLAG \
--backend "$BACKEND" \
--run-index "$RUN_INDEX" \
--upgrade "$APP_DIR" \
--level "$current_level" \
--resume-session
# Grade ALL features (regression test)
ITERATION=0
while ! grade_app "$APP_DIR" "$current_level"; do
ITERATION=$((ITERATION + 1))
if [[ $ITERATION -ge $MAX_FIX_ITERATIONS ]]; then
echo "$LOG_PREFIX Max fixes at level $current_level — moving on"
break
fi
fix_bugs "$APP_DIR" "$ITERATION"
done
done
echo "$LOG_PREFIX === Sequential Upgrade Complete ==="
fi
# ─── Summary ────────────────────────────────────────────────────────────────
echo ""
echo "═══════════════════════════════════════════"
echo "$LOG_PREFIX Exhaust Loop Complete"
echo " App dir: $APP_DIR"
echo " Variant: $VARIANT"
echo " Backend: $BACKEND"
echo "═══════════════════════════════════════════"
echo ""
echo "When done grading, clean up with: ./cleanup.sh $APP_DIR"