Model shared data and enforce changes with database constraints

Alice and Bob use the same reading list. Both can see bookmark 7, but each has separate reading progress. Alice marking it read should not remove it from Bob's unread list. A single is_read field on the shared bookmark would represent the wrong fact.

You will model that fact, retrieve it with a query, and protect changes that arrive concurrently. Then you will inspect an index's effect on the query. Start with the meaning of a row before choosing database products or optimizing a screen.

Give each fact a key

A key identifies the fact that one row represents. A bookmark ID identifies a shared item. Reading progress needs both a member and an item.

The local reading-list API implements bookmarks and per-member reading state. The membership table above illustrates an extension to real groups, not another table already supplied by that starter.

After Alice marks bookmark 7 read, the state can be:

If no reading-state row exists, define whether that means unread. Missing data needs a declared meaning. Do not infer a person's past reading history from a shared flag that never recorded who acted.

Use constraints to protect the model

This is an illustrative relational schema for the reading-state relationship. Adapt names and identity types to your application:

CREATE TABLE reading_state (
    member_id TEXT NOT NULL,
    bookmark_id INTEGER NOT NULL REFERENCES bookmarks(id),
    is_read BOOLEAN NOT NULL,
    PRIMARY KEY (member_id, bookmark_id)
);

The composite primary key prevents two rows describing the same member/bookmark pair. The foreign key requires the referenced bookmark to exist under the chosen database configuration. Membership and operation authorization still need enforcement. A foreign key does not decide whether Bob is allowed to act as Alice.

Normalization gives a fact one authoritative representation. Store a group's name on the group row rather than copying it into every bookmark. A deliberately copied count or search view is denormalized data. It can be useful, but it needs an update and repair rule.

Read shared items with the current member's status

A left join keeps bookmarks even when the member has no status row:

SELECT b.id, b.title, COALESCE(r.is_read, FALSE) AS is_read
FROM bookmarks AS b
LEFT JOIN reading_state AS r
  ON r.bookmark_id = b.id
 AND r.member_id = :authenticated_member
ORDER BY b.created_at DESC, b.id DESC
LIMIT 20;

This teaching query omits group filtering to focus on the join. A multi-group application must also restrict bookmarks to verified membership. The placeholder is a bound driver parameter derived from trusted identity.

Putting the member filter inside the join condition preserves bookmarks with no matching status. Moving it carelessly into a WHERE clause can remove those rows and change the query's meaning. COALESCE applies the declared missing-row policy here. It should not hide an unavailable database or an unknown business value.

The (created_at, id) order uses ID to break timestamp ties. A cursor must retain both values to continue deterministically. It also needs a documented policy for concurrent edits and deletions.

An index serves a particular access pattern

Suppose the real query is “list one owner's newest twenty bookmarks.” A candidate index is (owner_id, created_at, id) in an ordering appropriate to that query. Its value depends on filters, data distribution and the database's plan.

An index is not a guarantee that every query becomes fast. In PostgreSQL, a B-tree stores ordered keys separately from heap row versions. Choosing ordered IDs does not keep all heap rows physically sorted forever. Other index families serve different operators and access patterns.

Use the PostgreSQL lab for schema, sample data and actual query plans. Compare estimated and observed row counts, scans, sorting and buffer activity under representative data. A tiny table may reasonably use a sequential scan.

Atomic changes and concurrent decisions are different concerns

A transaction can store related changes together or roll them back together. The transaction's isolation and write rules determine what concurrent callers may observe and change.

Two buyers both see one remaining unit. If both independently decide it is available and then write zero, each may think it succeeded. A conditional decrement makes the availability decision part of the write:

UPDATE stock
SET available = available - 1
WHERE product_id = :product_id
  AND available > 0;

Check the affected-row count. One means this decrement succeeded. Zero means no unit was claimed by that operation. If order creation must succeed with the decrement, place both in the same appropriate transaction. For more complex invariants, use the required locking or isolation protocol and handle retries.

The two-session worksheet makes the interleaving observable. Read the actual SQL and transaction boundaries rather than treating the word transaction as the entire proof.

Make units, absence and time explicit

USD 1999 minor units means USD 19.99 under that currency convention. JPY 500 is a different unit, so adding the integers without a conversion policy is meaningless. Likewise, storing a UTC instant alone does not fully describe “9 a.m. every Monday in London.”

A natural key comes from the domain, such as an email address. A surrogate key is an assigned identity. Use a separate uniqueness constraint where a domain value must remain unique, while allowing the stable internal identity to survive a legitimate value change.

Change the model without inventing history

If the old schema used a shared read flag, adding a per-member table cannot reconstruct which person actually read each item. State that gap and choose an explicit migration policy, such as unknown historical status or a documented reset. Do not silently assign the shared value to everyone.

For a compatible schema change, add the new representation, copy recoverable data, keep live changes synchronized, reconcile and switch consumers before retiring the old path. The migration lesson follows newer writes and deletions arriving during the copy.

Your first deliverable is small: two members, one bookmark, two independent reading states and a query that returns the correct view for each. Then inspect one measured query plan and demonstrate one concurrent invariant. Those examples establish what your schema and index are actually doing.