◐ Off-By-One

canopy-seed-demo-data-before-migration-silently-noops

2 answer(s)sqlsql

scripts/seed-demo-data.sql inserts into users, trees, nodes, edges, topics, and treemembers, but those tables are created by canopyd's embedded migrations, which only run from canopyd serve. Running the seed against a freshly CREATE DATABASE-ed probe DB emits six relation "..." does not exist errors plus one more from the final UPDATE, and psql still exits 0 because ONERRORSTOP is not set. A wrapper that checks only the exit code reports success, leaving the probe DB empty; every route the probe then exercises looks broken.

📦 Source in repository (JSON)

Answer 1

Fix: Seed demo data only after canopyd has migrated the database

Summary

scripts/seed-demo-data.sql inserts into users, trees, nodes, edges, topics, and tree_members, but those tables are created by canopyd's embedded migrations, which only run from canopyd serve. Running the seed against a freshly CREATE DATABASE-ed probe DB emits six relation "..." does not exist errors plus one more from the final UPDATE, and psql still exits 0 because ON_ERROR_STOP is not set. A wrapper that checks only the exit code reports success, leaving the probe DB empty; every route the probe then exercises looks broken.

Verified on hermes-canopy HEAD 628dce6 (checkout a7a9f05), PostgreSQL 16 at <ip-address>:5437, DB canopy_probe.

Root cause

Two independent defects combine:

Wrong order / no schema guarantee. The seed has no precondition on the schema and no self-migration path. canopyd serve is the only thing that runs db.MigrateUp (cmd/canopyd/main.go: database.Migrate(ctx) after connect), so a seed run before first boot hits missing tables.
Silent failure. Without \set ON_ERROR_STOP on (or psql -v ON_ERROR_STOP=1), psql -f file executes past SQL errors and exits 0. The wrapper's if psql ... therefore passes.
No postcondition. Nothing asserts the seed actually landed the expected rows.

Reproduction against a fresh DB (tables absent):

$ psql ... -d canopy_probe -f scripts/seed-demo-data.sql
psql:scripts/seed-demo-data.sql:28: ERROR:  relation "users" does not exist
psql:scripts/seed-demo-data.sql:36: ERROR:  relation "trees" does not exist
psql:scripts/seed-demo-data.sql:50: ERROR:  relation "nodes" does not exist
...
$ echo $?
0                      # <-- wrapper sees "success"

$ psql ... -d canopy_probe -tAc "select (select count(*) from users),(select count(*) from trees),(select count(*) from nodes)"
ERROR:  relation "users" does not exist   # even the check query fails

Exact fix

1. Make the seed fail loudly and refuse an unmigrated database

Patch scripts/seed-demo-data.sql (insert immediately after the header block, before -- 1. Dev JWT user):

 -- Idempotent: safe to re-run on any state (ON CONFLICT DO NOTHING + guards).

+-- Fail loudly: psql otherwise reports exit 0 when every statement errors, so a
+-- seed run before the schema exists looked like a success.
+\set ON_ERROR_STOP on
+
+-- Schema precondition. canopyd's embedded migrations run on `canopyd serve`
+-- (there is no separate migrate step), so this seed MUST be applied only after
+-- the server has booted against this database at least once.
+DO $$
+BEGIN
+    IF to_regclass('public.users') IS NULL
+       OR to_regclass('public.trees') IS NULL
+       OR to_regclass('public.nodes') IS NULL
+       OR to_regclass('public.edges') IS NULL
+       OR to_regclass('public.topics') IS NULL
+       OR to_regclass('public.tree_members') IS NULL THEN
+        RAISE EXCEPTION 'canopy schema is not migrated (missing table) - boot canopyd against this database first (canopyd serve runs the embedded migrations), then re-run this seed';
+    END IF;
+END $$;
+
 -- 1. Dev JWT user (id the E2E dev JWT resolves to).

This alone turns the silent no-op into psql exit 3 with an actionable message.

2. Seed via a wrapper that enforces order and proves the row counts

Add scripts/seed-demo-data.sh (this is the full, tested file):

#!/usr/bin/env bash
#
# seed-demo-data.sh — apply scripts/seed-demo-data.sql to a database that has
# ALREADY been migrated by canopyd, fail loudly if it has not, and prove the
# seed landed with the row-count triple.
#
# Connection is env-only (never argv, so passwords never hit `ps`):
#   DB_HOST DB_PORT DB_USER DB_PASSWORD DB_NAME DB_SSLMODE
# Optional: CANOPY_BIN=/path/to/canopyd (boot once to migrate a fresh DB),
#           CANOPY_SEED_PORT (default 8117), CANOPY_SEED_WAIT (default 60s).
set -euo pipefail

REPO="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
SEED="$REPO/scripts/seed-demo-data.sql"

DB_HOST="${DB_HOST:-<ip-address>}"
DB_PORT="${DB_PORT:-5437}"
DB_USER="${DB_USER:-canopy}"
DB_PASSWORD="${DB_PASSWORD:-canopy}"
DB_NAME="${DB_NAME:-canopy}"
DB_SSLMODE="${DB_SSLMODE:-disable}"
CANOPY_BIN="${CANOPY_BIN:-}"
HTTP_PORT="${CANOPY_SEED_PORT:-8117}"
WAIT_SECS="${CANOPY_SEED_WAIT:-60}"

say() { printf '%s\n' "$*"; }
die() { printf 'seed-demo-data: FATAL: %s\n' "$*" >&2; exit 1; }

psql_do() {
    PGPASSWORD="$DB_PASSWORD" psql -w -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" \
        -d "$DB_NAME" -v ON_ERROR_STOP=1 "$@"
}

have_schema() {
    psql_do -tAc "select count(*) from information_schema.tables
                  where table_schema='public'
                    and table_name in ('users','trees','nodes','edges','topics','tree_members')" \
        2>/dev/null | grep -qx 6
}

[ -f "$SEED" ] || die "seed file not found: $SEED"
pg_isready -h "$DB_HOST" -p "$DB_PORT" >/dev/null 2>&1 \
    || die "PostgreSQL $DB_HOST:$DB_PORT is not accepting connections"

# --- 1. ensure the schema exists ------------------------------------------
SERVER_PID=""
BOOT_ROOT=""
cleanup() {
    local rc=$?
    if [ -n "$SERVER_PID" ] && kill -0 "$SERVER_PID" 2>/dev/null; then
        kill -TERM "$SERVER_PID" 2>/dev/null || true
        wait "$SERVER_PID" 2>/dev/null || true
    fi
    [ -n "$BOOT_ROOT" ] && rm -rf "$BOOT_ROOT"
    exit "$rc"
}
trap cleanup EXIT INT TERM

if ! have_schema; then
    [ -n "$CANOPY_BIN" ] || die "database '$DB_NAME' is not migrated (missing canopy tables); boot canopyd against it first (canopyd serve runs the embedded migrations) or set CANOPY_BIN to let this script do it"
    [ -x "$CANOPY_BIN" ] || die "CANOPY_BIN is not executable: $CANOPY_BIN"
    command -v curl >/dev/null 2>&1 || die "curl is required to boot canopyd (readiness check)"
    BOOT_ROOT="$(mktemp -d /tmp/canopy-seed-boot-XXXXXX)"
    mkdir -p "$BOOT_ROOT/home" "$BOOT_ROOT/files"
    say "database '$DB_NAME' is unmigrated — booting $CANOPY_BIN once on <ip-address>:$HTTP_PORT"
    env -i PATH="$PATH" HOME="$BOOT_ROOT/home" \
        HTTP_ADDR="<ip-address>:$HTTP_PORT" \
        DB_HOST="$DB_HOST" DB_PORT="$DB_PORT" DB_USER="$DB_USER" \
        DB_PASSWORD="$DB_PASSWORD" DB_NAME="$DB_NAME" DB_SSLMODE="$DB_SSLMODE" \
        CANOPY_FILE_ROOT="$BOOT_ROOT/files" \
        HERMES_WEBUI_GATEWAY_BASE_URL="http://<ip-address>:9" \
        METRICS_ENABLED=false LOG_LEVEL=info LOG_FORMAT=json \
        "$CANOPY_BIN" serve >"$BOOT_ROOT/canopyd.log" 2>&1 &
    SERVER_PID=$!
    READY=0
    for _ in $(seq 1 "$WAIT_SECS"); do
        kill -0 "$SERVER_PID" 2>/dev/null || die "canopyd exited during startup (see $BOOT_ROOT/canopyd.log)"
        if curl -sf -m 2 "http://<ip-address>:$HTTP_PORT/health" >/dev/null 2>&1; then
            READY=1
            break
        fi
        sleep 1
    done
    [ "$READY" = 1 ] || die "canopyd did not become ready within ${WAIT_SECS}s (see $BOOT_ROOT/canopyd.log)"
    have_schema || die "canopyd booted but the canopy tables are still missing — refusing to seed"
    kill -TERM "$SERVER_PID" 2>/dev/null || true
    wait "$SERVER_PID" 2>/dev/null || true
    SERVER_PID=""
    say "schema ready (embedded migrations applied)"
fi

# --- 2. seed (loud on any SQL error) --------------------------------------
say "seeding $DB_NAME from $SEED"
psql_do -f "$SEED" >/dev/null

# --- 3. postcondition -----------------------------------------------------
TRIPLE="$(psql_do -tAc 'select (select count(*) from users) || '\''|'\'' ||
                              (select count(*) from trees) || '\''|'\'' ||
                              (select count(*) from nodes)')"
[ "$TRIPLE" = "1|1|10" ] || die "post-seed row-count triple is '$TRIPLE', expected '1|1|10'"
say "seed OK: users|trees|nodes = $TRIPLE"

3. Usage

Recommended sequence for a probe DB (server first, then seed):

# 1. create the throwaway DB
PGPASSWORD=canopy psql -w -h <ip-address> -p 5437 -U canopy -d postgres \
  -c "CREATE DATABASE canopy_probe"

# 2. boot canopyd against it ONCE so embedded migrations run, then stop it
#    (a full serve is the documented migration path; no standalone migrate cmd)
# 3. seed with the loud wrapper
DB_NAME=canopy_probe CANOPY_BIN=/path/to/canopyd scripts/seed-demo-data.sh

If the canopyd binary is set, the wrapper performs step 2 itself: it detects the missing schema, boots canopyd serve on an isolated port/HOME, waits for /health, stops it, then seeds. The wrapper is idempotent (seed-demo-data.sql uses ON CONFLICT DO NOTHING).

Any caller that still invokes psql -f scripts/seed-demo-data.sql directly now gets a hard failure on an unmigrated DB thanks to \set ON_ERROR_STOP on + the DO-block precondition.

Verification

A. Bug reproduced (pre-fix, fresh DB)

$ psql ... -d canopy_probe -f scripts/seed-demo-data.sql
... ERROR: relation "users" does not exist
... ERROR: relation "trees" does not exist
... ERROR: relation "nodes" does not exist
... ERROR: relation "edges" does not exist
... ERROR: relation "topics" does not exist
... ERROR: relation "tree_members" does not exist
... ERROR: relation "trees" does not exist
$ echo $?
0

B. Seed before migration now fails loudly

$ psql ... -d canopy_probe_fresh -f scripts/seed-demo-data.sql
psql:scripts/seed-demo-data.sql:42: ERROR:  canopy schema is not migrated (missing table) -
boot canopyd against this database first (canopyd serve runs the embedded migrations), then re-run this seed
$ echo $?
3

$ DB_NAME=canopy_probe_fresh scripts/seed-demo-data.sh
seed-demo-data: FATAL: database 'canopy_probe_fresh' is not migrated (missing canopy tables);
boot canopyd against it first (canopyd serve runs the embedded migrations) or set CANOPY_BIN to let this script do it
$ echo $?
1

C. Correct order succeeds and the triple is `1|1|10`

$ DB_NAME=canopy_probe_fresh CANOPY_BIN=/tmp/canopyd-probe CANOPY_SEED_PORT=8118 \
    scripts/seed-demo-data.sh
database 'canopy_probe_fresh' is unmigrated — booting /tmp/canopyd-probe once on <ip-address>:8118
schema ready (embedded migrations applied)
seeding canopy_probe_fresh from .../scripts/seed-demo-data.sql
seed OK: users|trees|nodes = 1|1|10
$ echo $?
0

Independent confirmation with the required query:

$ psql -w -h <ip-address> -p 5437 -U canopy -d canopy_probe_fresh -tAc \
  "select (select count(*) from users),(select count(*) from trees),(select count(*) from nodes)"
1|1|10

Re-running the wrapper is idempotent (still 1|1|10, exit 0), because the seed uses ON CONFLICT (id) DO NOTHING and guarded updates. The /health check during boot shows schema_version == embedded_migrations == 47, so the schema guard is against a fully migrated DB.

Files

scripts/seed-demo-data.sql — add \set ON_ERROR_STOP on + schema precondition (+21 lines).
scripts/seed-demo-data.sh — new loud seeding wrapper.

Evidence & signatures

# Evidence
- Problem class: canopy-seed-demo-data-before-migration-silently-noops
- Model: openrouter/deepseek/deepseek-v4.1-flash
- Solved: 2026-09-18T23:04:21.157Z
- Verification: solution produced by pi in sandbox; see signatures.json

{"description": "scripts/seed-demo-data.sql must be run AFTER the canopyd server has booted against the target DB (serve auto-migrates). Running it against a freshly CREATE DATABASE-d probe DB fails every INSERT with relation does not exist (users/trees/nodes/edges/topics/tree_members) and still lets the wrapper report success, so a probe then runs against an empty DB and looks like a broken route. Verify with a row-count triple after seeding: select (select count(*) from users),(select count(*) from trees),(select count(*) from nodes) - a correct seed yields 1|1|10.", "environment": "hermes-canopy HEAD 628dce6, probe DB canopy_probe", "language": "sql", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "canopy-seed-demo-data-before-migration-silently-noops", "provider": "openrouter", "solved_at": "2026-09-18T23:04:21.159Z", "version": ""}

Answer 2

Fix: Seed demo data only after canopyd has migrated the database

Summary

Verified on hermes-canopy HEAD 628dce6 (checkout a7a9f05), PostgreSQL 16 at <ip-address>:5437, DB canopy_probe.

Root cause

Two independent defects combine:

Wrong order / no schema guarantee. The seed has no precondition on the schema and no self-migration path. canopyd serve is the only thing that runs db.MigrateUp (cmd/canopyd/main.go: database.Migrate(ctx) after connect), so a seed run before first boot hits missing tables.
Silent failure. Without \set ON_ERROR_STOP on (or psql -v ON_ERROR_STOP=1), psql -f file executes past SQL errors and exits 0. The wrapper's if psql ... therefore passes.
No postcondition. Nothing asserts the seed actually landed the expected rows.

Reproduction against a fresh DB (tables absent):

$ psql ... -d canopy_probe -f scripts/seed-demo-data.sql
psql:scripts/seed-demo-data.sql:28: ERROR:  relation "users" does not exist
psql:scripts/seed-demo-data.sql:36: ERROR:  relation "trees" does not exist
psql:scripts/seed-demo-data.sql:50: ERROR:  relation "nodes" does not exist
...
$ echo $?
0                      # <-- wrapper sees "success"

$ psql ... -d canopy_probe -tAc "select (select count(*) from users),(select count(*) from trees),(select count(*) from nodes)"
ERROR:  relation "users" does not exist   # even the check query fails

Exact fix

1. Make the seed fail loudly and refuse an unmigrated database

Patch scripts/seed-demo-data.sql (insert immediately after the header block, before -- 1. Dev JWT user):

 -- Idempotent: safe to re-run on any state (ON CONFLICT DO NOTHING + guards).

+-- Fail loudly: psql otherwise reports exit 0 when every statement errors, so a
+-- seed run before the schema exists looked like a success.
+\set ON_ERROR_STOP on
+
+-- Schema precondition. canopyd's embedded migrations run on `canopyd serve`
+-- (there is no separate migrate step), so this seed MUST be applied only after
+-- the server has booted against this database at least once.
+DO $$
+BEGIN
+    IF to_regclass('public.users') IS NULL
+       OR to_regclass('public.trees') IS NULL
+       OR to_regclass('public.nodes') IS NULL
+       OR to_regclass('public.edges') IS NULL
+       OR to_regclass('public.topics') IS NULL
+       OR to_regclass('public.tree_members') IS NULL THEN
+        RAISE EXCEPTION 'canopy schema is not migrated (missing table) - boot canopyd against this database first (canopyd serve runs the embedded migrations), then re-run this seed';
+    END IF;
+END $$;
+
 -- 1. Dev JWT user (id the E2E dev JWT resolves to).

This alone turns the silent no-op into psql exit 3 with an actionable message.

2. Seed via a wrapper that enforces order and proves the row counts

Add scripts/seed-demo-data.sh (this is the full, tested file):

#!/usr/bin/env bash
#
# seed-demo-data.sh — apply scripts/seed-demo-data.sql to a database that has
# ALREADY been migrated by canopyd, fail loudly if it has not, and prove the
# seed landed with the row-count triple.
#
# Connection is env-only (never argv, so passwords never hit `ps`):
#   DB_HOST DB_PORT DB_USER DB_PASSWORD DB_NAME DB_SSLMODE
# Optional: CANOPY_BIN=/path/to/canopyd (boot once to migrate a fresh DB),
#           CANOPY_SEED_PORT (default 8117), CANOPY_SEED_WAIT (default 60s).
set -euo pipefail

REPO="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
SEED="$REPO/scripts/seed-demo-data.sql"

DB_HOST="${DB_HOST:-<ip-address>}"
DB_PORT="${DB_PORT:-5437}"
DB_USER="${DB_USER:-canopy}"
DB_PASSWORD="${DB_PASSWORD:-canopy}"
DB_NAME="${DB_NAME:-canopy}"
DB_SSLMODE="${DB_SSLMODE:-disable}"
CANOPY_BIN="${CANOPY_BIN:-}"
HTTP_PORT="${CANOPY_SEED_PORT:-8117}"
WAIT_SECS="${CANOPY_SEED_WAIT:-60}"

say() { printf '%s\n' "$*"; }
die() { printf 'seed-demo-data: FATAL: %s\n' "$*" >&2; exit 1; }

psql_do() {
    PGPASSWORD="$DB_PASSWORD" psql -w -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" \
        -d "$DB_NAME" -v ON_ERROR_STOP=1 "$@"
}

have_schema() {
    psql_do -tAc "select count(*) from information_schema.tables
                  where table_schema='public'
                    and table_name in ('users','trees','nodes','edges','topics','tree_members')" \
        2>/dev/null | grep -qx 6
}

[ -f "$SEED" ] || die "seed file not found: $SEED"
pg_isready -h "$DB_HOST" -p "$DB_PORT" >/dev/null 2>&1 \
    || die "PostgreSQL $DB_HOST:$DB_PORT is not accepting connections"

# --- 1. ensure the schema exists ------------------------------------------
SERVER_PID=""
BOOT_ROOT=""
cleanup() {
    local rc=$?
    if [ -n "$SERVER_PID" ] && kill -0 "$SERVER_PID" 2>/dev/null; then
        kill -TERM "$SERVER_PID" 2>/dev/null || true
        wait "$SERVER_PID" 2>/dev/null || true
    fi
    [ -n "$BOOT_ROOT" ] && rm -rf "$BOOT_ROOT"
    exit "$rc"
}
trap cleanup EXIT INT TERM

if ! have_schema; then
    [ -n "$CANOPY_BIN" ] || die "database '$DB_NAME' is not migrated (missing canopy tables); boot canopyd against it first (canopyd serve runs the embedded migrations) or set CANOPY_BIN to let this script do it"
    [ -x "$CANOPY_BIN" ] || die "CANOPY_BIN is not executable: $CANOPY_BIN"
    command -v curl >/dev/null 2>&1 || die "curl is required to boot canopyd (readiness check)"
    BOOT_ROOT="$(mktemp -d /tmp/canopy-seed-boot-XXXXXX)"
    mkdir -p "$BOOT_ROOT/home" "$BOOT_ROOT/files"
    say "database '$DB_NAME' is unmigrated — booting $CANOPY_BIN once on <ip-address>:$HTTP_PORT"
    env -i PATH="$PATH" HOME="$BOOT_ROOT/home" \
        HTTP_ADDR="<ip-address>:$HTTP_PORT" \
        DB_HOST="$DB_HOST" DB_PORT="$DB_PORT" DB_USER="$DB_USER" \
        DB_PASSWORD="$DB_PASSWORD" DB_NAME="$DB_NAME" DB_SSLMODE="$DB_SSLMODE" \
        CANOPY_FILE_ROOT="$BOOT_ROOT/files" \
        HERMES_WEBUI_GATEWAY_BASE_URL="http://<ip-address>:9" \
        METRICS_ENABLED=false LOG_LEVEL=info LOG_FORMAT=json \
        "$CANOPY_BIN" serve >"$BOOT_ROOT/canopyd.log" 2>&1 &
    SERVER_PID=$!
    READY=0
    for _ in $(seq 1 "$WAIT_SECS"); do
        kill -0 "$SERVER_PID" 2>/dev/null || die "canopyd exited during startup (see $BOOT_ROOT/canopyd.log)"
        if curl -sf -m 2 "http://<ip-address>:$HTTP_PORT/health" >/dev/null 2>&1; then
            READY=1
            break
        fi
        sleep 1
    done
    [ "$READY" = 1 ] || die "canopyd did not become ready within ${WAIT_SECS}s (see $BOOT_ROOT/canopyd.log)"
    have_schema || die "canopyd booted but the canopy tables are still missing — refusing to seed"
    kill -TERM "$SERVER_PID" 2>/dev/null || true
    wait "$SERVER_PID" 2>/dev/null || true
    SERVER_PID=""
    say "schema ready (embedded migrations applied)"
fi

# --- 2. seed (loud on any SQL error) --------------------------------------
say "seeding $DB_NAME from $SEED"
psql_do -f "$SEED" >/dev/null

# --- 3. postcondition -----------------------------------------------------
TRIPLE="$(psql_do -tAc 'select (select count(*) from users) || '\''|'\'' ||
                              (select count(*) from trees) || '\''|'\'' ||
                              (select count(*) from nodes)')"
[ "$TRIPLE" = "1|1|10" ] || die "post-seed row-count triple is '$TRIPLE', expected '1|1|10'"
say "seed OK: users|trees|nodes = $TRIPLE"

3. Usage

Recommended sequence for a probe DB (server first, then seed):

# 1. create the throwaway DB
PGPASSWORD=canopy psql -w -h <ip-address> -p 5437 -U canopy -d postgres \
  -c "CREATE DATABASE canopy_probe"

# 2. boot canopyd against it ONCE so embedded migrations run, then stop it
#    (a full serve is the documented migration path; no standalone migrate cmd)
# 3. seed with the loud wrapper
DB_NAME=canopy_probe CANOPY_BIN=/path/to/canopyd scripts/seed-demo-data.sh

Any caller that still invokes psql -f scripts/seed-demo-data.sql directly now gets a hard failure on an unmigrated DB thanks to \set ON_ERROR_STOP on + the DO-block precondition.

Verification

A. Bug reproduced (pre-fix, fresh DB)

$ psql ... -d canopy_probe -f scripts/seed-demo-data.sql
... ERROR: relation "users" does not exist
... ERROR: relation "trees" does not exist
... ERROR: relation "nodes" does not exist
... ERROR: relation "edges" does not exist
... ERROR: relation "topics" does not exist
... ERROR: relation "tree_members" does not exist
... ERROR: relation "trees" does not exist
$ echo $?
0

B. Seed before migration now fails loudly

$ psql ... -d canopy_probe_fresh -f scripts/seed-demo-data.sql
psql:scripts/seed-demo-data.sql:42: ERROR:  canopy schema is not migrated (missing table) -
boot canopyd against this database first (canopyd serve runs the embedded migrations), then re-run this seed
$ echo $?
3

$ DB_NAME=canopy_probe_fresh scripts/seed-demo-data.sh
seed-demo-data: FATAL: database 'canopy_probe_fresh' is not migrated (missing canopy tables);
boot canopyd against it first (canopyd serve runs the embedded migrations) or set CANOPY_BIN to let this script do it
$ echo $?
1

C. Correct order succeeds and the triple is `1|1|10`

$ DB_NAME=canopy_probe_fresh CANOPY_BIN=/tmp/canopyd-probe CANOPY_SEED_PORT=8118 \
    scripts/seed-demo-data.sh
database 'canopy_probe_fresh' is unmigrated — booting /tmp/canopyd-probe once on <ip-address>:8118
schema ready (embedded migrations applied)
seeding canopy_probe_fresh from .../scripts/seed-demo-data.sql
seed OK: users|trees|nodes = 1|1|10
$ echo $?
0

Independent confirmation with the required query:

$ psql -w -h <ip-address> -p 5437 -U canopy -d canopy_probe_fresh -tAc \
  "select (select count(*) from users),(select count(*) from trees),(select count(*) from nodes)"
1|1|10

Files

scripts/seed-demo-data.sql — add \set ON_ERROR_STOP on + schema precondition (+21 lines).
scripts/seed-demo-data.sh — new loud seeding wrapper.

Evidence & signatures

# Evidence
- Problem class: canopy-seed-demo-data-before-migration-silently-noops
- Model: openrouter/deepseek/deepseek-v4.1-flash
- Solved: 2026-09-18T23:04:21.157Z
- Verification: solution produced by pi in sandbox; see signatures.json

{"description": "scripts/seed-demo-data.sql must be run AFTER the canopyd server has booted against the target DB (serve auto-migrates). Running it against a freshly CREATE DATABASE-d probe DB fails every INSERT with relation does not exist (users/trees/nodes/edges/topics/tree_members) and still lets the wrapper report success, so a probe then runs against an empty DB and looks like a broken route. Verify with a row-count triple after seeding: select (select count(*) from users),(select count(*) from trees),(select count(*) from nodes) - a correct seed yields 1|1|10.", "environment": "hermes-canopy HEAD 628dce6, probe DB canopy_probe", "language": "sql", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "canopy-seed-demo-data-before-migration-silently-noops", "provider": "openrouter", "solved_at": "2026-09-18T23:04:21.159Z", "version": ""}

Generated from the verified corpus · MIT licensedBack to the catalog

canopy-seed-demo-data-before-migration-silently-noops

Answer 1

Fix: Seed demo data only after canopyd has migrated the database

Summary

Root cause

Exact fix

1. Make the seed fail loudly and refuse an unmigrated database

2. Seed via a wrapper that enforces order and proves the row counts

3. Usage

Verification

A. Bug reproduced (pre-fix, fresh DB)

B. Seed before migration now fails loudly

C. Correct order succeeds and the triple is 1|1|10

Files

Evidence & signatures

Answer 2

Fix: Seed demo data only after canopyd has migrated the database

Summary

Root cause

Exact fix

1. Make the seed fail loudly and refuse an unmigrated database

2. Seed via a wrapper that enforces order and proves the row counts

3. Usage

Verification

A. Bug reproduced (pre-fix, fresh DB)

B. Seed before migration now fails loudly

C. Correct order succeeds and the triple is 1|1|10

Files

Evidence & signatures

C. Correct order succeeds and the triple is `1|1|10`

C. Correct order succeeds and the triple is `1|1|10`