Archetype 3 — Append-Only Child Object #

What this archetype is #

A child object is created under a parent and accumulates over time. The common operations are append, paginate, moderate, and soft delete.

Examples: comments, chat messages, reviews.

We will use comments on a post as the running example.

Layer 1: Entities and Postgres table design #

CommentState
  -> source truth for each comment
ThreadView
  -> read ordered by parent and time

create table comments (
  comment_id uuid primary key,
  post_id bigint not null,
  author_id bigint not null,
  body text not null,
  status text not null default 'ACTIVE',
  version bigint not null default 1,
  idempotency_key uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index comments_author_idem_idx
on comments (author_id, idempotency_key)
where idempotency_key is not null;

create index comments_post_created_idx
on comments (post_id, created_at asc, comment_id);

Layer 2: Write path mechanics #

Create comment #

insert into comments (
  comment_id, post_id, author_id, body, status, idempotency_key
) values ($1, $2, $3, $4, 'ACTIVE', $5);

If retries are possible, rely on the unique (author_id, idempotency_key) index.

Edit comment #

update comments
set body = $2,
    status = 'EDITED',
    version = version + 1,
    updated_at = now()
where comment_id = $1
  and version = $3
  and status = 'ACTIVE';

Soft delete #

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

Read thread #

select *
from comments
where post_id = $1
  and status = 'ACTIVE'
order by created_at asc, comment_id asc
limit 50;

Layer 3: Fault tolerance #

• duplicate child creation on retry -> idempotency key or deterministic ID
• concurrent edit/delete race -> guarded by status and version
• moderation/removal lag -> downstream indexes and notifications can lag
• projection/search lag after append -> if comments feed a search index

Layer 4: Scale #

Default hotspots:

• hot parent append rate
• hot thread reads
• long history scans

Common mitigations:

• partition large tables by parent or time
• keep append path narrow and index only needed reads
• archive old comments to cold storage / history tables