AI Database Architect

Turn a prompt, a document, or an existing database into a live AWS backend — relational schema, CRUD APIs, and an admin dashboard — in seconds.

AI Database Architect takes whatever you already have — a sentence, a messy spreadsheet, or a legacy database — and produces a production-shaped relational backend: it models the data, deploys an isolated schema to Amazon Aurora PostgreSQL (Serverless v2), loads your real rows, and generates a full CRUD REST API plus an admin dashboard. A round-trip verification gate guarantees a lossy schema can never deploy.

Every path then generates:

• an ordered SQL migration, gated by a round-trip verifier (parse the DDL back into a model and structurally diff it — fail closed on any drift);
• an isolated gen_<id> schema deployed to Amazon Aurora PostgreSQL;
• a CRUD REST API + admin dashboard that read and write the real database;
• a structure (ER) diagram and a REST API panel with copy-paste curl.

Everything is a deterministic projection of a single dialect-independent data-model IR ("model first, generate second"):

Input ─► Modeling Engine ─► Refinement ─► Schema Generator ─► Round-Trip Verifier (deploy gate)
                                                                      │
                                              Provisioner (RDS) ◄─────┘
                                                     │
                              API Generator + Auth + Admin Dashboard

• Modeling Engine — prompt path via AWS Bedrock; document path via deterministic, LLM-free relational decomposition (detects repeating field groups and extracts them into their own entities).
• Schema Generator — projects the IR to ordered PostgreSQL DDL (topological order, FK indexes, constraints).
• Round-Trip Verifier — reconstructs the IR from the generated DDL and diffs it against the source; blocks deploy on any difference.
• Provisioner — transactional migration runner; one isolated schema per generation on Amazon Aurora PostgreSQL.
• API / Dashboard generators + Auth Service + Orchestrator state machine.

Validated with property-based testing (fast-check): 211 tests covering 46 correctness properties.

src/                Backend engine (TypeScript, ESM)
  model/            The Data_Model IR + invariants
  modeling/         Prompt + document modeling, Bedrock client
  schema/           DDL generation + round-trip verifier + targets
  provisioner/      Transactional Aurora migration runner + data seeding + indexing
  api/              CRUD API surface + runtime
  auth/             Role-based auth (hashed passwords, JWT)
  dashboard/        Dashboard descriptor + query logic
  import/           PostgreSQL / MySQL introspection + data copy
  orchestrator/     Pipeline state machine
  pipeline/         Wires it all together (+ env-driven factory)
test/               Vitest + fast-check test suite (211 tests)
web/                Next.js frontend (consumes the engine as a library)
samples/            Example CSV / Excel / PDF files
scripts/            Helper scripts (schema cleanup, import-source setup)
.kiro/specs/        The spec (requirements, design, tasks) this was built from

• Node.js ≥ 18
• Optional (for live mode): an AWS account with Bedrock access and an Amazon Aurora PostgreSQL cluster (Serverless v2 works great).

npm install
npm run build      # compiles src/ -> dist/
npm test           # optional: run the 211-test suite

cd web
npm install
npm run dev        # http://localhost:3000

The web app imports the compiled engine from ../dist, so run npm run build at the repo root before npm run dev.

The app works in two modes, selected by environment variables (see below):

• Local / demo mode (no AWS needed): uses a deterministic offline model generator and an in-memory database. Great for trying the UI and the modeling instantly.
• Live mode: real AWS Bedrock for prompt modeling + real Amazon Aurora PostgreSQL for deployment and data.

Configuration is read from environment variables. For the web app, put them in web/.env.local (this file is gitignored — never commit it). Copy .env.example as a starting point.

AWS credentials for Bedrock come from your standard AWS chain (e.g. aws configure, env vars, or an IAM role) — not from this repo.

Each generation is deployed into its own gen_<id> schema on the Aurora cluster, so repeated runs never collide.

# Generate from a prompt using the env-configured pipeline
npm run generate -- "A hotel booking system with rooms, guests, and bookings"

# List / clean up the gen_* schemas in your live database (safe by default — lists only)
node scripts/cleanup-generations.mjs                 # list
node scripts/cleanup-generations.mjs --keep 3 --yes  # drop all but newest 3

# Create a sample "legacy store" database to test the Import flow
node scripts/setup-import-source.mjs

Sample files live in samples/:

• student-attendance.csv — flat sheet → Record + branch (intelligent decomposition)
• invoices.csv — → invoice, customer, productName, line-item Record
• store.xlsx — multi-sheet workbook → related customers + orders
• students.pdf — table-in-PDF extraction

Upload any of them in the Document → Backend tab and watch the structure diagram + generated API appear.

For every entity the runtime serves:

Validation (required, unique, email format, numeric range, foreign-key existence) is derived from the model and enforced on every write.

• Secrets (.aida-db-pw.txt, web/.env.local, .env*) are gitignored and never committed. AWS credentials are sourced from your local AWS configuration.
• This is a hackathon/demo project: the session store is in-memory and the dashboard is unauthenticated by default. Add the included auth layer and a persistent session store before any production use.

TypeScript · Node.js · Next.js / React · AWS Bedrock (Amazon Nova) · Amazon Aurora PostgreSQL (Serverless v2) · node-postgres (pg) · mysql2 · SheetJS (xlsx) · pdf-parse / pdfjs-dist · Vitest · fast-check (property-based testing) · built spec-first with Kiro.