Stage 7 · Master
Phase 10 — Complaint Service
Timeline
Capture every status change, assignment action, and human comment as immutable complaint events so disputes can be reconstructed from append-only facts instead of mutable rows.
Why can the complaints row not answer dispute questions by itself?
The complaints table is intentionally mutable because the queue needs current truth: current status, current deadline, current timestamps. But dispute resolution asks historical questions: who acknowledged this, who reassigned it, when did staff say it was resolved, and what exact resident comment came after that. A mutable row cannot answer those questions because every update overwrites the previous state. HOA platforms need that history for more than debugging; residents and management may use it to settle billing disputes, maintenance blame, or repeated-service failures.
That is why timeline data belongs in an append-only event table. An event is a durable fact: status_changed, assigned, unassigned, comment_added. Once written, it does not get edited or deleted, because doing so would let the platform silently rewrite accountability after a disagreement has started.
You need both. The queue would be painful if every open-ticket screen replayed the full event stream, and the audit trail would be useless if every change only touched the mutable complaint row.
How do you enforce immutable complaint events at the database layer?
A social or legal dispute is exactly when people are tempted to 'clean up' history. Relying on code review alone is not enough. The migration below creates complaint_events as an append-only table and adds database triggers that reject UPDATE and DELETE outright. Using a BIGSERIAL primary key also gives the timeline a monotonic sequence number, which becomes useful when spotting suspicious rows whose created_at ordering does not match insertion order.
CREATE TABLE complaint_events (
id BIGSERIAL PRIMARY KEY,
org_id UUID NOT NULL,
complaint_id UUID NOT NULL REFERENCES complaints(id) ON DELETE CASCADE,
event_type TEXT NOT NULL CHECK (event_type IN ('status_changed', 'assigned', 'unassigned', 'comment_added')),
actor_user_id UUID NOT NULL,
payload JSONB NOT NULL DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX complaint_events_org_complaint_created_idx
ON complaint_events (org_id, complaint_id, created_at ASC, id ASC);
CREATE FUNCTION forbid_complaint_event_mutation() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
RAISE EXCEPTION 'complaint_events is append-only';
END;
$$;
CREATE TRIGGER complaint_events_no_update
BEFORE UPDATE ON complaint_events
FOR EACH ROW
EXECUTE FUNCTION forbid_complaint_event_mutation();
CREATE TRIGGER complaint_events_no_delete
BEFORE DELETE ON complaint_events
FOR EACH ROW
EXECUTE FUNCTION forbid_complaint_event_mutation();The trigger is not decorative. It ensures the database itself refuses history rewrites even if a handler bug or manual script attempts one.
DROP TRIGGER IF EXISTS complaint_events_no_delete ON complaint_events;
DROP TRIGGER IF EXISTS complaint_events_no_update ON complaint_events;
DROP FUNCTION IF EXISTS forbid_complaint_event_mutation();
DROP TABLE IF EXISTS complaint_events;package complaint
import (
"context"
"encoding/json"
"time"
"github.com/google/uuid"
)
type EventType string
const (
EventTypeStatusChanged EventType = "status_changed"
EventTypeAssigned EventType = "assigned"
EventTypeUnassigned EventType = "unassigned"
EventTypeCommentAdded EventType = "comment_added"
)
type ComplaintEvent struct {
ID int64
OrgID uuid.UUID
ComplaintID uuid.UUID
Type EventType
ActorUserID uuid.UUID
Payload json.RawMessage
CreatedAt time.Time
}
type TimelineRepository interface {
AppendEvent(ctx context.Context, event ComplaintEvent) error
ListEvents(ctx context.Context, orgID, complaintID uuid.UUID) ([]ComplaintEvent, error)
}
const InsertEventSQL = `INSERT INTO complaint_events (org_id, complaint_id, event_type, actor_user_id, payload)
VALUES ($1, $2, $3, $4, $5::jsonb)`
const TimelineSQL = `SELECT id, org_id, complaint_id, event_type, actor_user_id, payload, created_at
FROM complaint_events
WHERE org_id = $1 AND complaint_id = $2
ORDER BY created_at ASC, id ASC`
type TimelineItem struct {
Kind string `json:"kind"`
ActorUserID uuid.UUID `json:"actor_user_id"`
Payload map[string]any `json:"payload"`
CreatedAt time.Time `json:"created_at"`
}
type TimelineService struct {
repo TimelineRepository
}
func NewTimelineService(repo TimelineRepository) *TimelineService {
return &TimelineService{repo: repo}
}
func (s *TimelineService) Render(ctx context.Context, orgID, complaintID uuid.UUID) ([]TimelineItem, error) {
events, err := s.repo.ListEvents(ctx, orgID, complaintID)
if err != nil {
return nil, err
}
items := make([]TimelineItem, 0, len(events))
for _, event := range events {
payload := map[string]any{}
if len(event.Payload) > 0 {
if err := json.Unmarshal(event.Payload, &payload); err != nil {
return nil, err
}
}
items = append(items, TimelineItem{
Kind: string(event.Type),
ActorUserID: event.ActorUserID,
Payload: payload,
CreatedAt: event.CreatedAt,
})
}
return items, nil
}Because every transition, assignment action, and comment is normalized into complaint_events, the API can build one ordered timeline with a single query instead of trying to reconstruct history from mutable tables.
How does the API render one ordered timeline, and what anomalies should make you suspicious?
The merged timeline view is simply the ordered event stream for one complaint, sorted by created_at and then id for deterministic ties. That second key matters when two events land in the same timestamp granularity, such as an assignment immediately followed by a status change in the same transaction. Stable ordering keeps the UI from flickering between two equally-timed rows.
The failure mode to watch is tampering or bad writes. If a row has a higher monotonic id but an earlier created_at than the event that supposedly happened before it, investigate. It might be clock skew from a bad insert path, or it might be someone bypassing normal writes. Append-only tables do not magically guarantee honesty; they guarantee that once a suspicious row exists, it is still there to inspect.
Which authorization and local verification steps keep the timeline trustworthy?
Timeline reads are still tenant-scoped reads. Never accept complaint_id from the path and then list events without org_id, because append-only history is often more sensitive than the current row: comments may include phone numbers, unit details, or incident descriptions. On writes, never trust actor_user_id from the request body. Derive it from the authenticated caller and serialize exactly what the server observed.
Applied exercise
Sort a messy complaint timeline and spot the forged-looking row
You receive five timeline rows for one complaint out of order: id 41 at 10:02 assigned, id 42 at 10:04 status_changed, id 43 at 10:03 comment_added, id 44 at 10:06 status_changed, and id 45 at 10:01 comment_added entered through a suspicious admin script.
- Render the rows in the order the API should display them using created_at and then id as the tie-break.
- Identify which row looks suspicious when you compare its monotonic id with its created_at position.
- Explain whether the anomaly is more likely a clock problem, a backfilled insert path, or deliberate tampering, and what evidence you would check next.
- Describe how append-only storage helps even when a suspicious row has already been written.
Deliverable
A brief ordered timeline plus one paragraph explaining why the suspicious row deserves investigation.
Completion checks
- The rendered order uses created_at first and id second.
- The suspicious row is identified by its late id combined with an earlier timestamp than surrounding facts.
- The answer explains that append-only design preserves evidence instead of hiding it.
Why is an append-only complaint_events table necessary when complaints.status already exists?
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.