Use database constraints to protect business correctness

One inventory unit remains. Two requests each read that it is available, then each tries to decrement stock. An application check can still race. Constraints, conditional updates, and transactions must enforce the rule across writers and retries.

The synthetic case consumes one unit per successful reservation, rejects repeated request IDs, and leaves no success record after a sold-out failure. The example runs in in-memory SQLite. It does not establish a tested PostgreSQL or Supabase deployment.

Encode invariants in the schema

NOT NULL rejects missing values, CHECK restricts allowed values, UNIQUE or primary keys reject duplicates, and foreign keys express references. These rules cover writes through several callers, but must match the business requirement.

Use explicit integer units and nonnegative stock. A unique request ID prevents one reservation from taking effect twice. A real idempotency record should also associate identity, resource, input, and result, so reused IDs with different requests cannot silently receive unrelated old results.

Enable SQLite foreign-key enforcement on connections when relying on it. This example has no foreign key and does not test reference integrity. Test valid and invalid writes after migrations rather than inspect declarations alone.

Commit related changes together

A transaction commits or rolls back a group of changes. Recording a reservation and decrementing stock separately can leave a half-completed outcome. Put them in one transaction when they belong to one atomic operation.

Isolation determines concurrent visibility. Using a transaction does not guarantee every operation succeeds as though automatically serialized. Locks, deadlocks, or serialization failures may require bounded retries under the selected database's rules. Retrying needs the complete use case and idempotency conditions.

Avoid the check-then-write gap with a conditional update, suitable locking, or constraints. The example decrements only where n>0 and checks affected rows. Zero rows means no stock was consumed and cannot be treated as success.

Indexes have access and maintenance costs

An index organizes access by keys and can reduce lookup or sorting work while increasing storage and write maintenance. Primary and unique constraints commonly involve indexing, but correctness and query optimization remain distinct purposes.

Choose indexes from actual predicates, ordering, and data distribution. Composite-column order affects supported access patterns. Indexing every field does not guarantee useful plans. Representative data and selectivity matter, and scanning a tiny table is not automatically a failure.

SQLite EXPLAIN QUERY PLAN can expose access paths; statistics influence planning. Plans do not establish every runtime duration. The following example contains no benchmark or measured speedup.

Execute reservations and rollback

The request primary key rejects duplicates before stock changes. A sold-out exception rolls back the newly inserted request record. The connection context commits success or rolls back on exceptions; it does not demonstrate connection closure.

import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("CREATE TABLE stock(id TEXT PRIMARY KEY, n INTEGER CHECK(n>=0)); CREATE TABLE requests(id TEXT PRIMARY KEY); INSERT INTO stock VALUES('S-1',2);")
def reserve(request_id):
    try:
        with con:
            con.execute("INSERT INTO requests VALUES(?)", (request_id,))
            cursor = con.execute("UPDATE stock SET n=n-1 WHERE id='S-1' AND n>0")
            if cursor.rowcount != 1:
                raise ValueError("sold out")
        return True
    except (sqlite3.IntegrityError, ValueError):
        return False
assert reserve("R-1")
assert not reserve("R-1")
assert reserve("R-2")
assert not reserve("R-3")
assert con.execute("SELECT n FROM stock").fetchone()[0] == 0
print(con.execute("SELECT n FROM stock").fetchone()[0])

The output is 0. R-1 succeeds once; its duplicate fails without decrementing. R-2 consumes the second unit. R-3 fails and its inserted request is rolled back. The final assertion checks zero stock. These are sequential calls on one connection, not a concurrent race test.

The quantity column does not declare NOT NULL, and this is not a strict table. SQLite type affinity and CHECK therefore do not reject every missing or incorrectly typed value. A real inventory schema also needs explicit non-null and integer rules, and request IDs must reject nulls. This example tests non-null string IDs and integer stock only.

False combines duplicate and sold-out outcomes for teaching. A real API should distinguish them and return a stored result for repeated successful requests. Whether a failed request retains its idempotency ID is a separate contract decision; rolling it back here is not a universal policy.

Row access is a separate layer

Supabase RLS controls rows under request identity. Data API schema exposure and table GRANT determine accessibility separately. TO authenticated alone does not establish ownership; policies still need resource predicates.

Updates require appropriate reading access and policies. USING checks existing rows and WITH CHECK checks resulting rows. Test that an owner cannot transfer a row to another user. A secret-key request without a user access token uses service_role and bypasses RLS; with a user token it follows that user's policies. Initialization alone does not determine every request's behavior.

Keep secret and service-role credentials on the server. RLS does not replace inventory constraints, and correct stock does not prove correct permissions. Verify owner, other user, unauthenticated, and administrative cases separately in a real integration.

Do not hold a reservation transaction open across a lengthy model call, upload, or user interaction. Longer transactions can occupy connections and locks and complicate recovery. Prepare independent inputs outside, perform required database changes inside, and coordinate external effects separately through jobs or compensation.

For a plan exercise, compare stock lookup by resource ID with reservation history filtered by user. The first may use a primary key; the second needs an index chosen from its filter and order. Record representative size, warm or cold cache conditions, repeated measurements, and write costs rather than one timing.

Preserve failure categories. Constraint violations, lock timeouts, and disconnected clients should not all become sold out. Return safe API codes and retain non-sensitive diagnostic causes. Retry only eligible failures within a budget; permanent invalid input should not be repeatedly sent to the database.

Exercise: inspect the complete outcome

Extend reservations with resource ID, quantity, and saved result. Add and enable references, then test missing resources, negative quantities, duplicates, and sold-out requests. Query stock and request records after every failure to detect half-completed changes.

Stock must remain nonnegative, repeated success must return its original result without another decrement, and a mid-transaction failure must leave no partial outcome. Inspect the real query plan. Then test concurrent decrements with two connections in the selected database, recording environment and isolation conditions. Sequential in-memory assertions cannot substitute for that experiment.