[adm 5.3] Generate database migrations from canonical generated API resources

[artiphishle]

Goal

Consume the canonical generated API desired-state contract established by ankhorage/studio#120 and emit provider-owned Supabase/Postgres migration artifacts for generated collection resources.

This issue is the database-infrastructure bridge for generated API authoring. It does not generate an HTTP/GraphQL server, Fastify application, Docker image or Minikube API workload.

Dependency and release boundary

Blocked by the released Contracts/Data Sources phases of:

• ankhorage/studio#120 — [adm 5] Rebuild generated API authoring and canonical API source model

Do not implement against an unreleased workspace-only contract. Do not retain or add a compatibility path for the historical manifest.data.apis shape.

Architecture boundary

The generated API flow has two related outputs:

GeneratedApiDefinition
  canonical desired state authored by Studio
  resources, fields, CRUD selection, seeds, supported policy intent

        -> @ankhorage/data-sources

ApiDataSourceConfig
  normalized runtime/binding projection
  kind: api
  origin: generated
  protocol: rest for the initial restored CRUD flow
  endpoints, operations and schemas

        -> @ankhorage/infra

Database migration artifacts
  provider-owned tables, columns, constraints and supported policies

Infra consumes the canonical generated desired-state definition for physical database generation. It must not infer provider-specific SQL from the normalized runtime operation projection when the desired-state resource definition is the authoritative source.

Scope

For each supported generated collection resource, emit deterministic Supabase/Postgres migrations.

Minimum field mapping:

• uuid -> uuid
• text / canonical string type -> text
• number -> numeric
• boolean -> boolean
• datetime -> timestamptz
• json -> jsonb

Minimum behavior:

• honor canonical schema/table/collection name;
• honor primary key;
• honor field required;
• honor field unique;
• honor safe scalar defaults such as strings, numbers and booleans;
• preserve stable generated resource IDs in artifact metadata where Infra conventions support it;
• generate deterministic migration names/content/order;
• apply supported auth/policy intent only through current Infra/provider ownership;
• enable RLS and generate supported policies only when explicitly declared by the canonical generated definition;
• reject unsupported or conflicting definitions with structured diagnostics rather than silently weakening them.

Explicit non-goals

Do not implement in this issue:

• a Fastify API generator;
• a GraphQL server generator;
• an infra.backend contract;
• an API-generator adapter placeholder;
• Docker image generation for a generated API service;
• Kubernetes Deployment/Service resources for an API server;
• a legacy manifest.data.apis reader;
• dual old/new generated API contract support;
• Studio UI or data-source normalization logic.

A future real HTTP/GraphQL generator requires a separately agreed package owner and implementation issue before any generator-adapter reference is added to canonical contracts.

Ownership

@ankhorage/contracts
  generated API desired-state types

@ankhorage/data-sources
  validation and generated definition -> normalized API operations

@ankhorage/infra
  generated resource -> provider-specific migration/artifact mapping

@ankhorage/studio
  authoring and persistence only

Required investigation

Before implementation:

• inspect the released canonical generated API definition;
• inspect current Infra provider/migration ownership and artifact APIs;
• identify the current Supabase/Postgres field/default/policy helpers;
• confirm the canonical manifest location consumed by Infra;
• search for every remaining manifest.data.apis, AppGeneratedApiDefinition and old generated-dataset assumption;
• remove obsolete readers rather than preserving a fallback;
• confirm whether seeds are migration-owned, runtime bootstrap-owned or intentionally unsupported before implementing seed emission.

Tests

Cover at minimum:

• one generated resource -> one deterministic migration/table;
• multiple resources and deterministic ordering;
• schema/table/primary-key mapping;
• all supported field type mappings;
• required -> not null;
• unique constraints;
• safe scalar defaults;
• JSON and datetime fields;
• supported RLS/policy mapping;
• invalid identifiers and duplicate/conflicting resources/fields;
• idempotent artifact generation;
• no artifact for external API sources;
• no HTTP service/container/Kubernetes API workload generation;
• no historical manifest.data.apis compatibility path.

Use representative generated resources such as products, captures, challenges or scan events only as test fixtures; do not couple Infra to those application domains.

Acceptance criteria

1. Infra consumes the released canonical generated API desired-state contract from [adm 5].
2. Supported generated collection resources produce deterministic Supabase/Postgres migrations.
3. Schema, table, primary key, fields, required, unique and supported defaults are preserved.
4. Supported auth/RLS/policy intent is mapped only by the owning Infra/provider implementation.
5. Invalid/conflicting definitions produce useful diagnostics.
6. External API data sources do not produce database migrations.
7. No Fastify/GraphQL server, Docker API image, Minikube API workload or speculative generator adapter is added.
8. No legacy/compatibility reader for manifest.data.apis remains.
9. Tests, generated docs, changeset, build, lint, format, Knip and typecheck pass.
10. The resulting released Infra package can be consumed by the Studio phase of ankhorage/studio#120.