Where: astrodbkit.astrodb.Database.load_table() does conn.execute(self.metadata.tables[table].insert().values(data)) where data comes straight from json.load(). If the JSON file's top-level array is empty ([]), SQLAlchemy's insert().values([]) is interpreted as "insert one row using column defaults" (a known SQLAlchemy quirk: an empty list to a multi-row .values() isn't a no-op), not "insert nothing."
What happened: After emptying Publications.json, SourceTypeList.json, and ParameterList.json to [] (clearing stale template-example placeholder rows before real data existed), build_db_from_json() failed with sqlite3.IntegrityError: NOT NULL constraint failed: Publications.reference — a single default-valued row was attempted, which violated the non-nullable reference column. The error message gives no hint that the root cause was an empty-array JSON file rather than a real data problem.
Workaround: Deleted the three now-empty JSON files entirely instead of leaving them as [] — load_table()'s existing if os.path.exists(filename): guard cleanly skips a missing file (with an optional verbose "not found" message), which is the actual correct way to represent "no data yet" for a reference table.
Suggested change: Database.load_table() should special-case if not data: return before calling insert().values(data), so an empty-array reference file is a documented no-op instead of an inserted row of column defaults that then trips downstream NOT NULL constraints.
Cross-filed: Also filed against the astrodb-bot skills repo as astrodbtoolkit/astrodb-bot#99, which documents the same gotcha from the skill-user workaround side; this issue is for the actual astrodbkit fix.
Reported from a gotchas.md log filed by a skill user (2026-08-28).
Where:
astrodbkit.astrodb.Database.load_table()doesconn.execute(self.metadata.tables[table].insert().values(data))wheredatacomes straight fromjson.load(). If the JSON file's top-level array is empty ([]), SQLAlchemy'sinsert().values([])is interpreted as "insert one row using column defaults" (a known SQLAlchemy quirk: an empty list to a multi-row.values()isn't a no-op), not "insert nothing."What happened: After emptying
Publications.json,SourceTypeList.json, andParameterList.jsonto[](clearing stale template-example placeholder rows before real data existed),build_db_from_json()failed withsqlite3.IntegrityError: NOT NULL constraint failed: Publications.reference— a single default-valued row was attempted, which violated the non-nullablereferencecolumn. The error message gives no hint that the root cause was an empty-array JSON file rather than a real data problem.Workaround: Deleted the three now-empty JSON files entirely instead of leaving them as
[]—load_table()'s existingif os.path.exists(filename):guard cleanly skips a missing file (with an optional verbose "not found" message), which is the actual correct way to represent "no data yet" for a reference table.Suggested change:
Database.load_table()should special-caseif not data: returnbefore callinginsert().values(data), so an empty-array reference file is a documented no-op instead of an inserted row of column defaults that then trips downstream NOT NULL constraints.Cross-filed: Also filed against the astrodb-bot skills repo as astrodbtoolkit/astrodb-bot#99, which documents the same gotcha from the skill-user workaround side; this issue is for the actual
astrodbkitfix.Reported from a gotchas.md log filed by a skill user (2026-08-28).