Remove stale memory/schema.sql (311K, Feb 28) — superseded by memory/schema/schema.sql

[NOVA-Openclaw]

Problem

There are two schema files in the repo:

• memory/schema/schema.sql (157K) — active, used by agent-install.sh via pgschema plan/apply against $SCHEMA_DIR/schema.sql
• memory/schema.sql (311K) — stale, last modified 2026-02-28, predates the pgschema migration

Per the 2026-02-28 changelog: "Schema source of truth is now schema/schema.sql, generated by pgschema dump". The old memory/schema.sql is a legacy full pg_dump that's no longer authoritative.

Risk

Currently low — but the stale file:

1. Confuses contributors ("which schema is canonical?")
2. Silently drifts from the live schema (was unchanged Feb 28 → today, while memory/schema/schema.sql has multiple updates)
3. Is the only thing referenced by memory/tests/fixtures/load-test-data.sh:50-55. Coincidentally this file currently has the same fixed get_agent_bootstrap function as the active schema, so test fixtures still load correctly — but that's an accidental match, not a guarantee. Future schema changes will desync.

Proposed fix

1. Delete memory/schema.sql
2. Update memory/tests/fixtures/load-test-data.sh to either:
   • Read memory/schema/schema.sql instead, OR
   • Apply schema via pgschema apply (consistent with the production install path)

Related

• Discovered while debugging the May 8 get_agent_bootstrap schema drift (workflow_steps.agent_id removal). Function fix landed in commit 5b20b40.
• The active schema/schema.sql + pgschema flow is working correctly for production installs; this is purely cleanup.

Owner

Database / Agent Design domain (newhart) for the SQL file deletion + Software Engineering domain for the test-fixture script update.

Filed by NOVA on behalf of I)ruid.

[NOVA-Openclaw]

Update from I)ruid (2026-05-08 22:49 UTC):

database/schema.sql is the canonical, auto-dumped source of truth. The pgschema-based installer reads from there.

Two stale schema files in the repo that should be removed:

1. memory/schema/schema.sql (157K, generated 2026-02-28) — deprecated. Was the pgschema source of truth before the move to top-level database/schema.sql. Now redundant.
2. memory/schema.sql (311K, generated 2026-02-28) — also deprecated, original full pg_dump.

Plus the subpackage installer's own copy:

3. cognition/focus/bootstrap-context/sql/management-functions.sql — duplicates the get_agent_bootstrap function definition. Already caused one regression on 2026-05-08 when its stale copy clobbered Newhart's Remove stale memory/schema.sql (311K, Feb 28) — superseded by memory/schema/schema.sql #171 fix on the live DB. Pre-flight + smoke-test safeguards added in commit 0bc2ee2 (branch fix/db-bootstrap-context-shape-and-fallthrough), but the long-term fix is to retire this file and have the bootstrap-context installer rely on the function having been applied via the main pgschema flow.

Proposed scope expansion for this issue:

1. Delete memory/schema.sql
2. Delete memory/schema/schema.sql
3. Update memory/agent-install.sh to read from database/schema.sql (currently reads from memory/schema/schema.sql)
4. Update memory/tests/fixtures/load-test-data.sh to read from database/schema.sql (currently reads from memory/schema.sql)
5. Retire cognition/focus/bootstrap-context/sql/management-functions.sql and refactor that subpackage's install.sh to skip the function-apply step (it's already handled by pgschema apply on the main schema)

— NOVA

[NOVA-Openclaw]

Removed — stale memory/schema.sql and memory/schema/schema.sql deleted. Canonical schema is database/schema.sql.