Production REST API

Persistence and authentication

Build migration, transaction, password, session, and ownership rules for the production API.

Persistence and authentication

What you will learn

  • enforce durable invariants with constraints;
  • place multi-step operations in transactions;
  • store password verifiers and session-token digests safely;
  • enforce ownership in queries;
  • test repositories against an isolated real database.

Schema rules

CREATE TABLE users (
    id uuid PRIMARY KEY,
    email text NOT NULL,
    password_hash bytea NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX users_email_unique ON users (lower(email));

CREATE TABLE tasks (
    id uuid PRIMARY KEY,
    owner_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title text NOT NULL CHECK (char_length(title) BETWEEN 1 AND 200),
    completed boolean NOT NULL DEFAULT false,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

Application validation provides friendly errors; constraints preserve data when races or other paths bypass it. Test migrations against the same database family and major version used in deployment.

Transactions and identity

Registration may create both a user and session. Begin a transaction, defer tx.Rollback(), perform every operation through tx, and commit only after all succeed. Deferred rollback after commit harmlessly returns sql.ErrTxDone.

  • Use a maintained password-hashing implementation with a reviewed cost; never a fast general hash alone.
  • Generate session tokens with crypto/rand, send raw tokens only over TLS, and store a cryptographic digest.
  • Define expiry, revocation, and rotation.
  • Never log passwords, raw tokens, or authorization headers.
  • Include ownership in protected queries: WHERE id = $3 AND owner_id = $4.
  • Parameterize every untrusted SQL value.

Exact password parameters change with hardware and guidance. Follow the selected library's current documentation and benchmark on representative deployment hardware.

Practice

Why store a digest of a random session token but a slow password-specific hash of a password?

Summary

  • Combine application validation with database constraints.
  • Use one transaction for one all-or-nothing operation.
  • Treat passwords and random tokens according to different threat models.
  • Put ownership predicates inside protected data operations.

Ready to wrap up?

Mark this complete when you can explain the main idea and finish the practice task.

Copyright © 2026