Design the SQLite schema

In-memory arrays disappear on restart and cannot coordinate several server processes. SQLite gives the local API durable transactional storage.

Use a text primary key, required title and author fields, and timestamps stored in one consistent format. Database constraints are a second line of defense; they protect writes made outside the HTTP handler too.

CREATE TABLE books (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL CHECK (length(title) > 0),
  author TEXT NOT NULL CHECK (length(author) > 0),
  published_year INTEGER,
  created_at TEXT NOT NULL
);

The schema is the last local authority for data integrity. HTTP validation gives clients friendly errors; constraints prevent a background script, test helper, or future route from inserting impossible rows. Keep timestamps in one UTC representation and generate server-owned values in one place.

Treat schema changes as versioned migrations, not startup-time guesses. Apply the same migration files in development, tests, and deployment, and record which ones completed. For a write that spans several statements, use a transaction so either the entire state change commits or none of it does. SQLite allows concurrent readers but coordinates writes, so keep transactions short and handle a busy or constraint failure without returning raw database text.

Create the database and table, then insert one valid row and one row that violates a constraint.