feat: constraint-preserving table copy for export (NOT NULL, PK, UNIQUE survive CTAS)

[StefanSteiner]

Summary

Add a constraint-preserving table copy capability to hyperdb-api (Rust API) and use it in hyperdb-mcp's export(format="hyper") path so that NOT NULL, PRIMARY KEY, UNIQUE, CHECK, and DEFAULT constraints survive a whole-database export/backup.

Problem

Today hyperdb-mcp's hyper-format export (export.rs:516) uses CREATE TABLE AS SELECT * FROM <source> to copy each user table into the backup .hyper file. This is a Hyper engine behavior (shared with PostgreSQL): CTAS infers the output schema from the query's result columns, which carry types but no constraints — so all columns in the exported file become nullable: true regardless of the source schema.

Observed: a persistent database with NOT NULL columns produces a backup where every column is nullable. The data is identical, but constraint metadata is lost.

Proposed solution

1. New Rust API method: Catalog::copy_table (or clone_table)

A method on hyperdb_api::Catalog (or a new module) that copies a table preserving its full schema, by:

1. Reading the source table's column definitions via Catalog::get_columns (already exists — returns column name + type + nullability).
2. Emitting an explicit CREATE TABLE <dest> (...) with the source's NOT NULL constraints (and any others that can be read from Hyper's catalog views).
3. Populating via INSERT INTO <dest> SELECT * FROM <source> (or the faster COPY path if the tables are in the same connection).

This separates schema creation from data transfer, so constraints are carried over explicitly rather than inferred from query output.

Open questions:

• Which constraints can Hyper's catalog expose? get_columns already gives nullability. PRIMARY KEY, UNIQUE, CHECK, and DEFAULT may need pg_catalog queries or additional Catalog methods. Document what Hyper supports and what must be deferred.
• Cross-database copy: the export path copies from the live persistent attachment into a freshly-created target database (different alias). The INSERT INTO ... SELECT must be cross-database qualified. Hyper supports this (INSERT INTO "target"."public"."t" SELECT * FROM "source"."public"."t") — confirmed working in the existing export code.

2. Use in HyperDB MCP export

Replace the CTAS loop in populate_export_target with calls to the new copy_table API. The MCP already enumerates user tables and qualifies them by database alias — the change is confined to swapping CREATE TABLE AS SELECT for CREATE TABLE (schema) + INSERT.

3. Scope / constraints to preserve

Constraint	Source	Priority
NOT NULL	Catalog::get_columns (already available)	P0 — most impactful, simplest
DEFAULT values	pg_catalog.pg_attrdef or equivalent Hyper view	P1
PRIMARY KEY	pg_catalog.pg_constraint (type='p')	P1
UNIQUE	pg_catalog.pg_constraint (type='u')	P2
CHECK	pg_catalog.pg_constraint (type='c')	P2

Minimum viable: preserve NOT NULL (P0). The rest can be follow-ups if Hyper's catalog exposes them.

Acceptance criteria

• hyperdb-api exposes a method that copies a table preserving at least NOT NULL constraints.
• hyperdb-mcp export(format="hyper") uses that method; a round-trip export→attach shows the same nullability as the source.
• Unit test: create a table with NOT NULL columns, export to hyper, attach the export, assert the columns are still NOT NULL.

Context

Discovered while verifying an on-demand backup of the HyperDB MCP persistent database: the backup's describe showed all columns as nullable even though the source had NOT NULL constraints. Data was correct; only schema metadata was relaxed.