Drizzle generator should emit async validation for NOT VALID foreign keys

[TheHolyWaffle]

Component

node/drizzle (@aws/aurora-dsql-drizzle 0.1.0)

Problem

When aurora-dsql-drizzle generate transforms a generated foreign-key statement into a DSQL-compatible constraint ending in NOT VALID, the migration contains only the constraint creation:

ALTER TABLE "child"
  ADD CONSTRAINT "child_parent_id_parent_id_fk"
  FOREIGN KEY ("parent_id") REFERENCES "parent"("id")
  NOT VALID;

The constraint protects new writes, but existing rows remain unvalidated (pg_constraint.convalidated = false) until a separate operation explicitly starts and waits for DSQL constraint validation.

The package migrator already recognizes ALTER TABLE ASYNC statements, waits for the returned DSQL job, and records the statement only after the job succeeds. The missing piece appears to be generation of the companion validation statement.

Requested behavior

When generation retains a foreign key as NOT VALID, also emit an explicit migration statement separated by a Drizzle breakpoint:

ALTER TABLE "child"
  ADD CONSTRAINT "child_parent_id_parent_id_fk"
  FOREIGN KEY ("parent_id") REFERENCES "parent"("id")
  NOT VALID;
--> statement-breakpoint
ALTER TABLE ASYNC "child"
  VALIDATE CONSTRAINT "child_parent_id_parent_id_fk";

This could be the default behavior or an opt-in generator option if automatically validating large existing tables is considered too expensive for every deployment.

Why put this in the generated migration?

• Existing rows receive the same referential-integrity guarantee as new writes.
• Validation becomes visible and reviewable in committed migration SQL.
• The existing statement-level migration tracking makes validation resumable and auditable.
• The existing async-job handling already provides the required wait and failure behavior.
• Applications do not need a separate catalog-scanning post-migration script.

Alternative currently required

Applications must discover unvalidated constraints through pg_constraint, issue ALTER TABLE ASYNC ... VALIDATE CONSTRAINT themselves, wait for each job, and verify convalidated afterward.