Skip to content
build-3000
databases

Databases and CRUD

Making data outlive the browser - tables, rows and the four verbs of every app.

Before this: APIs and JSON

Objective

Understand persistent storage, direct an agent to build a Create-Read-Update-Delete application, and verify data truly survives restarts.

Vocabulary

The words to use when directing your agent.

database
Software that stores data durably and retrieves it efficiently.
table
A collection of same-shaped records - like a spreadsheet with enforced columns.
row / record
One entry in a table - one expense, one user.
CRUD
Create, Read, Update, Delete - the four operations nearly every app performs on data.
schema
The declared shape of your data: which fields exist and their types.
query
A request for data meeting certain conditions.

Understand it

Schema first, always

Decide the shape before the screens: an expense has amount (number), category (text), date (date), note (optional text). Four fields - that decision drives storage, forms, display and validation. Schema-first keeps the agent honest and your reviews fast.

Where the database lives at Level 1

Local options (SQLite file, browser storage backed by a tiny library) are fine now. The multi-user questions - who can touch whose rows - arrive with Level 2's servers. Do not solve sharing problems before having the sharing problem.

Core concepts

Persistence changes everything

Until now, closing the tab erased your app's memory. A database writes data to disk: restart, reboot, revisit tomorrow - it is still there. Persistence is the line between toys and tools.

CRUD is the grammar of apps

Notes apps: create notes, read them, edit, delete. Shops: products and orders. Feeds: posts. Once you see CRUD, you see that most apps differ in domain, not mechanics - which is why agents build them so well WHEN you specify the schema.

Examples

The restart test

Create an expense, close everything, reopen. Still there? Persistence verified. This thirty-second ritual is your database's most important test.

Update versus create confusion

Editing an existing expense must NOT insert a duplicate row. If your app grows a second identical entry after an edit, the update path is really creating - a classic agent bug worth hunting.

Common mistakes

Common mistake

Skipping the schema conversation and letting the agent improvise fields.

Fix

Declare the schema explicitly in your brief; make the agent echo it back before coding.

Common mistake

Trusting the UI as proof of saving.

Fix

Verify persistence by restarting, not by watching the list update on screen.

Working with your AI agent

Standing rule for CRUD projects: 'Echo the schema before implementing. After implementing, demonstrate each CRUD operation against the actual store and show me the evidence.' Screenshots of UI prove nothing; queries against data prove everything.

Build it

The module’s real project. Your agent builds; you direct and inspect.

Project: Expense Tracker v1

The flagship Level 1 build: a single-user expense tracker with full CRUD on a real persistent store, schema declared by you, verified by restarts - not screenshots.

Requirements

  • Declared schema: amount (number), category (text), date (date), note (optional text)
  • Full CRUD: add, view list sorted newest first, edit existing, delete with confirmation
  • Data persists across full app restarts
  • Total of all expenses visible and correct
  • Validation from previous module applied: amount must be positive number, category required

Context for your agent

Build a single-user expense tracker with a persistent local store (SQLite file or equivalent durable option - propose options with trade-offs first). Schema fixed: amount positive number, category required text, date, optional note. Features: add, list newest-first, edit, delete-with-confirm, and a correct running total. Echo the schema and operation list before coding; after coding, demonstrate create/read/update/delete against the store itself, not the UI.

Verify it - twice

Check every item yourself first. Then hand your agent the prompt and compare verdicts.

Self-verified0/13
Functional
Architecture
Security
Testing
Production

Verification prompt - paste into your AI agent

Audit my Expense Tracker v1 as a skeptical senior engineer. Do not trust the UI - interrogate the store. Verify PASS or FAIL with evidence: (1) schema matches amount/category/date/note exactly, (2) all CRUD operations demonstrably work against persistence (perform them yourself if you can), (3) editing never duplicates rows, (4) totals are computed from stored data, not tracked separately, (5) validation rejects negative amounts and empty categories, (6) store access is parameterized, not string-concatenated. Then perform the restart test yourself and report the result honestly.

Run this against “Databases and CRUD” after your own checklist pass. Where the agent and you disagree, investigate before moving on.

Review questions

Answer out loud. If you cannot, revisit before moving on.

  1. State your schema from memory and justify each field's type.

  2. Why is the restart test stronger than checking the on-screen list?

  3. What went wrong if editing creates a second identical row?

You can now

  • Declare schemas and hold agents to them
  • Build and verify full CRUD on persistent storage
  • Prove persistence with restarts and store-level checks

Up next

Shipping Your First App