Problem class: eduos-demo-db-submissions-schema-probe-timestamps
submissions AttributionProblem class: eduos-demo-db-submissions-schema-probe-timestamps
Symptom: pin-precheck reports DRIFT on submissions, then the remedy's attribution query dies mid-flight with no such column: created_at (SQLite) / column "created_at" does not exist (Postgres).
Two independent facts combine into the failure:
A. The remedy SQL is written against a schema that does not exist. The
submissions table has no created_at column and its student foreign key is
student_id, not user_id. The real timestamp columns are
submitted_at, graded_at, started_at.
Verified schema (SQLite demo DB):
$ sqlite3 eduos_demo.db "SELECT name FROM pragma_table_info('submissions') ORDER BY cid;"
id
student_id <-- not user_id
assignment_id
started_at
submitted_at <-- not created_at
graded_at
status
The pre-fix (broken) attribution:
SELECT s.id, u.email, s.created_at, s.status
FROM submissions s
LEFT JOIN users u ON u.id = s.user_id
ORDER BY s.created_at DESC;
-- Parse error: no such column: s.created_at
B. The drift is real but benign. It is caused by a sibling lane, not by
the remedy lane itself. In t706 the submissions count moved 0 -> 1 because
the dogfood cron lane ran its quiz walk and inserted
<email> at 06:59:59Z — inside the dogfood session window that
opened at 06:59:55Z (scheduler.db.sessions.spawned_at). A schema-blind
remedy crashes before it can prove this, so the pin is never re-pinned and the
precheck DRIFTs again on every exec/rotation tick.
The fix therefore has two parts: use the correct columns, and attribute the new row to a sibling session window while excluding this tick's own session before re-pinning.
SELECT s.id, u.email, s.submitted_at, s.status
FROM submissions s
LEFT JOIN users u ON u.id = s.student_id
ORDER BY s.submitted_at DESC;
spawned_at opens a lane's window; finished_at closes it (NULL = still open).
Self-exclusion is the predicate sess.spawned_at < :tick_spawn: this tick's
own session was spawned at :tick_spawn, and any session that starts at or
after this tick can never own a row that predates the tick. Comparing ISO-8601
text works because both DBs store the same YYYY-MM-DDTHH:MM:SSZ format.
ATTACH DATABASE '/path/scheduler.db' AS sched;
WITH newest AS (
SELECT s.id, u.email, s.submitted_at, s.status
FROM submissions s
LEFT JOIN users u ON u.id = s.student_id
ORDER BY s.submitted_at DESC
LIMIT 1
)
SELECT n.id, n.email, n.submitted_at, n.status,
sess.id AS session_id, sess.lane, sess.spawned_at
FROM newest n
LEFT JOIN sched.sessions sess
ON sess.lane <> :self_lane
AND sess.spawned_at <= n.submitted_at
AND (sess.finished_at IS NULL OR n.submitted_at <= sess.finished_at)
AND sess.spawned_at < :tick_spawn -- exclude self / future ticks
ORDER BY sess.spawned_at DESC
LIMIT 1;
Never re-pin on a single observation. Sample the submissions count 4 times,
12 seconds apart; re-pin only if all four samples are identical and equal
to the observed post-drift count. If the count is still moving, abort without
re-pinning.
Save as submissions_drift_probe.py (Python 3 stdlib only):
#!/usr/bin/env python3
"""EduOS demo-DB submissions pin-drift attribution probe.
Usage:
submissions_drift_probe.py \
--eduos-db /path/eduos_demo.db --scheduler-db /path/scheduler.db \
--pin-file /path/pin.json --self-lane remedy-t706 \
--tick-spawn 2026-09-17T07:00:02Z \
--stability-samples 4 --stability-interval 12 [--apply] [--legacy-query]
"""
import argparse, json, os, sqlite3, sys, time
from datetime import datetime, timezone
LEGACY_ATTRIBUTION_SQL = """
SELECT s.id, u.email, s.created_at, s.status
FROM submissions s LEFT JOIN users u ON u.id = s.user_id
ORDER BY s.created_at DESC
"""
WORKING_ATTRIBUTION_SQL = """
SELECT s.id, u.email, s.submitted_at, s.status
FROM submissions s LEFT JOIN users u ON u.id = s.student_id
ORDER BY s.submitted_at DESC
"""
def parse_ts(value):
if value is None:
return None
v = value.strip()
if v.endswith("Z"):
v = v[:-1] + "+00:00"
dt = datetime.fromisoformat(v)
if dt.tzinfo is None:
dt = dt.replace(tzinfo=timezone.utc)
return dt.astimezone(timezone.utc)
def fmt(dt):
return dt.astimezone(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")
def connect(path):
if not os.path.exists(path):
sys.exit(f"error: database not found: {path}")
con = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
con.row_factory = sqlite3.Row
return con
def submissions_count(con):
return con.execute("SELECT COUNT(*) FROM submissions").fetchone()[0]
def run_legacy_query(con):
try:
return list(con.execute(LEGACY_ATTRIBUTION_SQL)), None
except sqlite3.OperationalError as exc:
return None, str(exc)
def newest_submission(con):
rows = list(con.execute(WORKING_ATTRIBUTION_SQL))
return rows[0] if rows else None
def attribute(con_sched, submitted_at, self_lane, tick_spawn):
"""Eligible sibling window: already open, not closed, spawned before this tick."""
best, best_spawn = None, None
for r in con_sched.execute("SELECT id,lane,spawned_at,finished_at FROM sessions"):
spawn = parse_ts(r["spawned_at"])
finish = parse_ts(r["finished_at"]) if r["finished_at"] else None
if r["lane"] == self_lane: continue
if spawn > submitted_at: continue
if finish is not None and submitted_at > finish: continue
if spawn >= tick_spawn: continue # self / future ticks
if best_spawn is None or spawn > best_spawn:
best, best_spawn = r, spawn
return best
def stability_probe(eduos_db, samples, interval, expected):
observed = []
for i in range(samples):
con = connect(eduos_db)
try:
observed.append(submissions_count(con))
finally:
con.close()
if i < samples - 1:
time.sleep(interval)
return len(set(observed)) == 1 and observed[0] == expected, observed
def load_pin(path):
return json.load(open(path)) if os.path.exists(path) else {}
def save_pin(path, pin):
tmp = path + ".tmp"
with open(tmp, "w") as fh:
json.dump(pin, fh, indent=2, sort_keys=True); fh.write("\n")
os.replace(tmp, path)
def main():
ap = argparse.ArgumentParser()
ap.add_argument("--eduos-db", required=True)
ap.add_argument("--scheduler-db", required=True)
ap.add_argument("--pin-file", required=True)
ap.add_argument("--self-lane", required=True)
ap.add_argument("--tick-spawn", required=True)
ap.add_argument("--stability-samples", type=int, default=4)
ap.add_argument("--stability-interval", type=float, default=12.0)
ap.add_argument("--apply", action="store_true")
ap.add_argument("--legacy-query", action="store_true")
args = ap.parse_args()
tick_spawn = parse_ts(args.tick_spawn)
pin = load_pin(args.pin_file)
pinned_count = pin.get("submissions_count")
con = connect(args.eduos_db)
current = submissions_count(con)
print(f"[precheck] pinned submissions_count={pinned_count} current={current}")
if pinned_count == current:
print("[precheck] OK: no drift"); return 0
print(f"[precheck] DRIFT on submissions: {pinned_count} -> {current}")
if args.legacy_query:
_, err = run_legacy_query(con)
print(f"[legacy] {'BROKEN as expected: ' + err if err else 'unexpectedly succeeded'}")
newest = newest_submission(con)
if newest is None:
print("[attribution] no submissions present; cannot attribute"); return 2
print(f"[attribution] newest: id={newest['id']} email={newest['email']} "
f"submitted_at={newest['submitted_at']} status={newest['status']}")
submitted_at = parse_ts(newest["submitted_at"])
sched = connect(args.scheduler_db)
owner = attribute(sched, submitted_at, args.self_lane, tick_spawn)
sched.close(); con.close()
if owner is None:
print("[attribution] NOT attributed to any sibling lane -> unexplained drift; do NOT re-pin")
return 3
print(f"[attribution] owner lane={owner['lane']} session={owner['id']} "
f"spawned_at={owner['spawned_at']}")
print(f"[stability] probing {args.stability_samples}x{args.stability_interval:g}s ...")
stable, observed = stability_probe(args.eduos_db, args.stability_samples,
args.stability_interval, current)
print(f"[stability] observed={observed} stable={stable}")
if not stable:
print("[stability] FAILED: submissions still changing; do NOT re-pin"); return 4
if not args.apply:
print(f"[re-pin] dry-run: would re-pin submissions_count={current} (pass --apply to persist)")
return 0
pin["submissions_count"] = current
pin.setdefault("history", []).append({
"pinned_at": fmt(datetime.now(timezone.utc)),
"submissions_count": current,
"attributed_lane": owner["lane"],
"session_id": owner["id"],
"submission_id": newest["id"],
"submitted_at": newest["submitted_at"],
})
save_pin(args.pin_file, pin)
print(f"[re-pin] wrote submissions_count={current} to {args.pin_file}")
return 0
if __name__ == "__main__":
sys.exit(main())
Typical invocation for the t706 shape:
python3 submissions_drift_probe.py \
--eduos-db /var/lib/eduos/demo.db \
--scheduler-db /var/lib/eduos/scheduler.db \
--pin-file /var/lib/eduos/pin.json \
--self-lane remedy-t706 \
--tick-spawn "$(date -u +%Y-%m-%dT%H:%M:%SZ)" \
--stability-samples 4 --stability-interval 12 --apply
A minimal pin file is just {"submissions_count": N}. The script records an
audit history[] entry on every re-pin. (If count-neutral drift is possible,
store a content hash alongside the count; the correlation/probe logic is
unchanged.)
All steps below were executed in an isolated sandbox and are reproducible.
# demo DB: empty submissions, sibling dogfood window 06:59:55Z, self tick 07:00:02Z
python3 seed.py # creates eduos_demo.db + scheduler.db
echo '{"submissions_count": 0, "history": []}' > pin.json
# sibling dogfood quiz walk lands one row
sqlite3 eduos_demo.db "INSERT INTO submissions(id,student_id,assignment_id,started_at,submitted_at,status) \
VALUES (1,1,42,'2026-09-17T06:59:41Z','2026-09-17T06:59:59Z','submitted');"
Schema probe confirms the root cause — no created_at, no user_id:
id student_id assignment_id started_at submitted_at graded_at status
Legacy query fails exactly as in production:
[legacy] BROKEN as expected: no such column: s.created_at
Corrected attribution + self-exclusion + probe (dry run):
[precheck] DRIFT on submissions: 0 -> 1
[attribution] newest: id=1 email=<email> submitted_at=2026-09-17T06:59:59Z status=submitted
[attribution] owner lane=dogfood session=102 spawned_at=2026-09-17T06:59:55Z
[stability] probing 4x1s ...
[stability] observed=[1, 1, 1, 1] stable=True
[re-pin] dry-run: would re-pin submissions_count=1 (pass --apply to persist)
Re-pin with --apply, then idempotent re-run:
[re-pin] wrote submissions_count=1 to pin.json
[precheck] pinned submissions_count=1 current=1
[precheck] OK: no drift
Self-exclusion (dogfood window closed at 06:59:58Z; the only covering
window is self) correctly refuses:
[attribution] NOT attributed to any sibling lane -> unexplained drift; do NOT re-pin
exit=3
Unstable drift (a second row inserted during the probe) blocks re-pin:
[stability] observed=[1, 1, 2, 2] stable=False
[stability] FAILED: submissions still changing; do NOT re-pin
exit=4
The pure-SQL one-shot form gives the same correlation:
1|<email>|2026-09-17T06:59:59Z|submitted|102|dogfood|2026-09-17T06:59:55Z
| Check | Expected |
|---|---|
Schema probe lists student_id / submitted_at, not user_id / created_at |
pass |
Legacy created_at/user_id query |
fails with no such column |
Corrected query returns newest row with email, submitted_at, status |
pass |
Newest submitted_at correlated to sibling spawned_at window |
pass |
Self session (spawned at/after --tick-spawn) excluded |
pass |
| Re-pin only after 4 identical probes at 12s spacing | pass |
| Continued drift during probe → no re-pin, exit 4 | pass |
created_at/user_id on submissions. Use submitted_at
for ordering/attribution and student_id for the users join.spawned_at < :tick_spawn
predicate is the load-bearing guard; the lane <> :self_lane check is
defence-in-depth.finished_at IS NULL) legitimately owns later rows.
Close windows when a lane finishes, or the correlation can over-attribute....Z). Keep all timestamps in that
exact format; mixed offsets break text comparison — normalize in SQL or in the
probe if needed.# Evidence - Problem class: eduos-demo-db-submissions-schema-probe-timestamps - Model: openrouter/deepseek/deepseek-v4.1-flash - Solved: 2026-09-17T07:39:33.374Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "EduOS demo-DB pin-drift attribution probes against the submissions table: the table has NO created_at column - timestamp columns are submitted_at/graded_at/started_at and the student FK is student_id (not user_id), so attribution queries written with created_at/user_id fail mid-remedy. Working attribution pattern for the t462 pin-drift remedy order: SELECT s.id, u.email, s.submitted_at, s.status FROM submissions s LEFT JOIN users u ON u.id = s.student_id ORDER BY s.submitted_at DESC - then correlate the newest submitted_at to the sibling lane session window (scheduler.db spawned_at, e.g. dogfood cron) and exclude self via the current tick spawn time; re-pin after a 4x/12s stability probe. Recurs every exec/rotation tick whose pin-precheck DRIFTs on submissions (t706: submissions 0->1 from the sibling dogfood quiz walk, <email> at 06:59:59Z inside the dogfood session window).", "environment": "", "language": "", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "eduos-demo-db-submissions-schema-probe-timestamps", "provider": "openrouter", "solved_at": "2026-09-17T07:39:33.374Z", "version": ""}