Archetype 1 — Current-Value Entity #

What this archetype is #

One authoritative row or document holds the current state of an entity. Updates overwrite or conditionally update that current state.

Examples: user profile, account settings, product metadata, feature flag row.

We will use user profile as the running example.

Layer 1: Entities and Postgres table design #

One primary entity:

ProfileState
  -> source truth for a user's current profile
  -> one row per user

One primary table:

create table profiles (
  user_id bigint primary key,
  display_name text not null,
  bio text,
  avatar_url text,
  status text not null default 'ACTIVE',
  version bigint not null default 1,
  updated_at timestamptz not null default now()
);

Optional read index:

create index profiles_status_updated_idx
on profiles (status, updated_at desc);

Layer 2: Write path mechanics #

Create profile #

insert into profiles (
  user_id, display_name, bio, avatar_url, status
) values ($1, $2, $3, $4, 'ACTIVE');

If the create path may retry:

insert into profiles (...)
values (...)
on conflict (user_id) do nothing;

Update profile with CAS #

update profiles
set display_name = $2,
    bio = $3,
    avatar_url = $4,
    version = version + 1,
    updated_at = now()
where user_id = $1
  and version = $5
  and status = 'ACTIVE';

This is the Postgres equivalent of a conditional UpdateItem with version guard.

Soft delete / deactivate #

update profiles
set status = 'DELETED',
    version = version + 1,
    updated_at = now()
where user_id = $1
  and status = 'ACTIVE';

Read profile #

select *
from profiles
where user_id = $1;

Layer 3: Fault tolerance #

• Concurrent overwrite race: two writers update the same row; one loses on version mismatch
• Retry ambiguity on write: caller times out after commit; safe retry uses version or idempotency key
• Stale read after update: replicas or caches can lag
• Cache invalidation lag: cache still serves old profile after DB commit

Generic recovery:

• version-based retry on conflict
• read-after-write from primary when needed
• cache invalidation or short TTL

Layer 4: Scale #

Default hotspots:

• hot entity read skew
• write contention on one entity row
• cache invalidation churn

Common mitigations:

• cache in front of profile reads
• split very hot mutable subfields into separate tables
• avoid unnecessary writes to updated_at