Document Submissions API

A small REST API for a Document Submissions service, built with Node.js, Express, TypeScript, PostgreSQL and TypeORM. It covers safe retries, concurrency-safe edits, an audit trail, transactional integrity, and real-time notifications.

Tech stack

• Node.js + TypeScript
• Express 5
• PostgreSQL (with the pg_trgm extension for keyword search)
• TypeORM (entities + migrations)
• class-validator / class-transformer for request validation

Prerequisites

• Node.js 20+ (developed on 24)
• PostgreSQL 13+ (13+ ships gen_random_uuid() in core)

Setup and run

# 1. install dependencies
npm install

# 2. create the database
createdb submissions_dev

# 3. configure environment
cp .env.example .env
# edit TYPEORM_URL in .env to match your Postgres credentials

# 4. start the service (migrations run automatically on boot)
npm run dev

The server starts on the port from PORT (default 4000). On startup it connects to Postgres and applies any pending migrations before accepting traffic, so the schema is always current — there is no separate migration step to run.

If you do not have Postgres locally, a container is the fastest option:

docker run --name submissions-db -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=submissions_dev -p 5432:5432 -d postgres:16

Environment variables

Variable	Description	Example
PORT	HTTP port	4000
NODE_ENV	environment	development
TYPEORM_URL	Postgres connection string	postgres://user:pass@localhost:5432/submissions_dev
TYPEORM_SYNCHRONIZE	TypeORM schema auto-sync (kept off)	false
TYPEORM_MIGRATION_RUN	run migrations on boot	true
TYPEORM_POOL_MAX	max pool connections	10
TYPEORM_POOL_MIN	min pool connections	2
TYPEORM_IDLE_TIMEOUT	idle connection timeout (ms)	30000
TYPEORM_CONNECTION_TIMEOUT	connection acquire timeout (ms)	2000

API

Base URL: http://localhost:4000

Create a submission

POST /submissions — requires an Idempotency-Key header.

curl -i -X POST http://localhost:4000/submissions \
  -H 'Content-Type: application/json' \
  -H 'Idempotency-Key: key-123' \
  -d '{"title":"Permit application","category":"permits","body":"...","reference_date":"2026-01-15"}'

• 201 on first creation, 200 on a retry with the same Idempotency-Key (returns the original record).
• 400 on missing Idempotency-Key, invalid body, or unknown fields.

Read a submission

GET /submissions/:id

curl -i http://localhost:4000/submissions/<id>

• 200 when found, 404 when not, 400 for a malformed id.

Change status

PATCH /submissions/:id/status — staff only (X-Role: staff).

curl -i -X PATCH http://localhost:4000/submissions/<id>/status \
  -H 'Content-Type: application/json' \
  -H 'X-Role: staff' \
  -H 'X-Actor: alice' \
  -d '{"status":"approved","version":1}'

• version is the value the caller last read (optimistic lock).
• 200 on success, 409 when the submission changed since it was read, 403 without the staff role, 400 on invalid status/version, 404 when not found.
• X-Actor is recorded as the actor in the audit trail (defaults to staff).

Search

GET /search?q=<keyword>&category=<category>&cursor=<cursor>&limit=<n>

curl -s 'http://localhost:4000/search?q=permit&category=permits&limit=20'

Response:

{ "items": [ ... ], "next_cursor": "eyJ...", "has_more": true }

• Keyword matches title and body, category filters exactly.
• Pagination is cursor-based; pass next_cursor back as cursor for the next page.
• limit defaults to 20 and is capped at 100.

Real-time notifications

GET /events — staff only, Server-Sent Events stream.

# terminal 1: subscribe (leave open)
curl -N -H 'X-Role: staff' http://localhost:4000/events

# terminal 2: change a status, terminal 1 receives the event
curl -s -X PATCH http://localhost:4000/submissions/<id>/status \
  -H 'Content-Type: application/json' -H 'X-Role: staff' \
  -d '{"status":"approved","version":1}'

The stream emits a status.changed event on every status change:

event: status.changed
data: {"id":"...","oldStatus":"pending","newStatus":"approved","version":2,"changedBy":"alice","at":"..."}

A client must be connected before a change occurs to receive it; there is no replay of missed events.

Health

GET /health returns { "status": "ok" }.

Design decisions

Idempotent create

Each submission stores an Idempotency-Key in a column with a unique constraint. On create, the row is inserted; if the key already exists Postgres raises a unique-violation (23505), which is caught and the original record is returned instead of creating a duplicate. The header is required so retry-safety is guaranteed for every create. This handles the common case of a client retrying after a network timeout.

Preventing lost updates

Status changes use optimistic locking via an integer version column. The caller sends the version it last read; the update runs as UPDATE ... WHERE id = ? AND version = ? and increments the version in the same statement. If another writer already changed the row, zero rows match and the request is rejected with 409 Conflict rather than silently overwriting. This avoids holding locks across the read-then-write cycle of an HTTP request.

Transactional status change and audit

The status update and its audit record are written in a single transaction using a TypeORM QueryRunner, which pins every statement to one pooled connection. The read, the guarded update, the audit insert, and the commit all run on that connection; any failure triggers a rollback, so the status change and its audit entry can never drift apart. The notification is sent only after the transaction commits.

Stable pagination

Search uses keyset (cursor) pagination ordered by (created_at, id), rather than the page=<n> offset in the original brief. Offset pagination can skip or duplicate rows when data changes between page requests; keyset pagination is stable because the cursor encodes the last row seen and the query returns everything strictly after it. The cursor is an opaque base64 token; an invalid cursor returns 400. This ordering is backed by a composite index and is also the foundation for scaling search later.

Real-time notifications and scaling

Notifications use Server-Sent Events. SSE is a good fit because the flow is one-directional (server to staff), works over plain HTTP, is trivial to consume with curl, and needs no additional protocol.

The current notification hub is in-process: it only reaches clients connected to the same instance. Behind a load balancer with multiple instances, a change handled by instance A would not reach a staff member subscribed on instance B. To scale, the broadcast would move to a shared publish/subscribe channel — Postgres LISTEN/NOTIFY (no new infrastructure) or Redis pub/sub — where each instance subscribes and re-broadcasts to its own connected clients.

Input validation and error handling

Request bodies are validated with class-validator DTOs; unknown fields are rejected. All errors pass through a single error-handling middleware that maps known application errors to their status codes and returns a generic message for everything else, so internal details (database errors, stack traces, paths) never reach the client. Server-side logs retain the full detail.

SQL injection

All database access goes through TypeORM with parameterized queries; no user input is concatenated into SQL. Keyword search binds the search term as a parameter, so inputs such as '; DROP TABLE submissions;-- are treated as literal search text.

Assumptions

• "Document" refers to the submission record, not an uploaded file; the brief mentions file handling only as an optional stretch, so no file upload is implemented.
• page=<n> is replaced by an opaque cursor for stable pagination (see above).
• Role is supplied via an X-Role header; real authentication is out of scope.
• Idempotency-Key is required on create.
• Any valid status transition is allowed; there is no restriction on moving between specific statuses.

Testing

These are real integration tests — they run the actual Express app and a real PostgreSQL database, with no mocks, stubs, or in-memory fakes. This is deliberate: the four behaviours below are all database-level guarantees (unique constraints, transactions, row locking, parameterized SQL) that can only be proven honestly against a real Postgres.

Automated tests (Jest + supertest) cover the four most important behaviours:

• Idempotent create — the same Idempotency-Key returns the original record and creates no duplicate row.
• Concurrency conflict — two concurrent status changes at the same version resolve to one 200 and one 409.
• Transactional status and audit — a successful change writes exactly one audit row; a conflicting change writes none and leaves the status unchanged (the two are committed or rolled back together).
• Injection-safe search — a malicious q returns normally and the table is intact.

Run them with:

npm test

The tests exercise the real HTTP and database stack (these guarantees can only be verified against a real Postgres, not mocks). They are self-scoped — each test uses unique keys and categories and asserts only on the records it creates — so they run non-destructively against the configured database. A dedicated test database is recommended; point TYPEORM_URL at it before running.