Greenfield task

Assignment

Build a small PostgreSQL order-desk business application from an empty application directory. The application must provide one read operation for a paged order list and one write operation that assigns an order to an agent. The transport can be HTTP, a CLI, or another documented application boundary; the choice is part of the agent's design. The implementation must be usable from a repeatable test command and must explain how a reviewer runs it.

The application may use TypeScript/JavaScript, another language, or a library of the agent's choice. Do not assume that Ashiba owns the application architecture. Keep SQL and transaction decisions easy for a reviewer to find.

Starting database contract

The fixture runner creates these tables and seeds at least the rows below. An agent may add indexes or a migration, but may not change the meaning of these columns.

CREATE TABLE customers (
  id BIGINT PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  order_no TEXT NOT NULL UNIQUE,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  status TEXT NOT NULL CHECK (status IN ('open', 'assigned', 'closed')),
  priority INTEGER NOT NULL CHECK (priority BETWEEN 1 AND 5),
  total_cents BIGINT NOT NULL CHECK (total_cents >= 0),
  assigned_to TEXT NULL,
  created_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE order_events (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(id),
  event_type TEXT NOT NULL,
  metadata JSONB NOT NULL CHECK (jsonb_typeof(metadata) = 'object'),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

The seed data must include at least three customers and four orders with different priorities, statuses, and timestamps. At least one order has two events with different timestamps. The runner records the actual seed rows in the run evidence; tests must not rely on accidental rows left by another run.

Required behavior

1. Canonical list SQL. Keep one human-reviewable canonical SQL statement for the order list. It joins the customer, optionally filters by a search term over customer name or order number, returns a JSONB event summary, and has deterministic ordering, limit, and offset. The canonical statement must remain independently executable with the documented parameter values; it may be a SQL file, a literal, or another source form that preserves the SQL text.
2. Bound inputs. Search text, status, sort choice, direction, limit, and offset come from the application boundary as values. They must not be interpolated into SQL syntax. Sorting must use a finite, reviewed set of keys (at minimum created_at and priority) and directions, with a stable tie-breaker. Reject or safely normalize unknown choices.
3. Optional search and pagination. Omitted search means all eligible orders. A supplied term searches customer name and order number. Limit and offset are validated and bounded before the query is sent.
4. Explicit transaction. Assigning an order updates orders.assigned_to (and its status) and appends an order_events row in one transaction on one connection. A failure after the update but before the event insert must leave both tables unchanged. The metadata check constraint provides a deterministic database failure input for this rollback test.
5. Non-trivial PostgreSQL behavior. Preserve PostgreSQL JSONB aggregation (including event ordering and the empty-event case), ILIKE search, and TIMESTAMPTZ ordering. Equivalent SQL is acceptable only when the observed behavior and canonical source remain reviewable.
6. Schema/driver boundary. Keep the raw database row type separate from the application/domain result. Document and test the driver representations of BIGINT, JSONB, and TIMESTAMPTZ; convert or validate them at one explicit boundary. Do not hide a potentially lossy any conversion.

Required evidence

The agent supplies the canonical SQL, the input-to-parameter mapping, the transaction boundary, the raw-row mapper, and commands for the focused unit and live PostgreSQL checks. The task does not require a particular source path or generated file name.