Model Application State

1. A namespace and the first table

Everything the tracker owns lives in one namespace. One namespace per application domain is the right default: it keeps names short, and dropping or inspecting the whole app stays a one-liner. The first entity is projects. Its id column carries with { auto_increment }, so the database owns ID assignment - your application never coordinates counters:

Because the database assigns IDs, you read them back at insert time with returning instead of guessing:

2. A closed value set: enum

Task priority is a fixed set known at design time. That is exactly what an enum is for: the schema enforces the set, so a typo like "hgih" is a schema error instead of silent bad data. Declare the variants once:

3. An open value set: dictionary

Assignee names are the opposite case: an open set you discover at runtime. A dictionary interns each distinct string once and stores a compact integer everywhere it is used. You still read and write plain strings; the encoding is the engine's problem:

4. The tasks table ties it together

Now the central entity. Each column type is a decision you just made: an auto-increment ID, a plain int8 foreign reference to projects, the enum for priority, and the dictionary-encoded assignee:

5. Derived state is a view, not a cache

"How many open tasks does each person have?" is the kind of read shape that usually ends up in a cache with invalidation bugs. Here it is a transactional view: declared once, maintained incrementally inside the same transaction as every write that affects it. Define it before inserting data, so it tracks changes from the start:

6. Recent activity is a ring buffer

An activity feed only ever shows the last few entries, so storing unbounded history is waste. A ring buffer holds a fixed number of rows and evicts the oldest on overflow - retention is schema, not a cleanup job. The tiny capacity here is just to make eviction visible on this page:

7. Load the data

The schema is complete; now exercise it. Enum values are written as variant paths, and assignees are written as plain strings - the dictionary interns new names on the fly:

Every auto-increment column is backed by a sequence you can reposition, for example to keep public-facing IDs away from the small numbers used while prototyping. The next insert continues from the new value:

8. Read it back

A plain read shows how each choice is stored. The enum column comes back as its compact tag (priority_tag: declaration order, so Low is 0 and High is 2), while the dictionary-encoded assignee is decoded back to a string for you:

The dictionary itself is queryable, and it interned exactly the two distinct names it saw:

9. Query the derived state

The workload view is already populated. Nothing recomputed it - the task inserts maintained it as they committed:

Ad-hoc read shapes stay ordinary queries. Joined columns are prefixed with the join alias, so the project's name arrives as p_name:

10. Change the data

Complete a task and record the action in the activity buffer:

The completed task no longer matches the view's filter, so its assignee's count dropped by one. There is no cache to invalidate, because there is no cache:

Meanwhile the activity buffer enforces its own retention. It held one row; three more inserts push it past its capacity of 3, and the oldest entry is evicted:

What you modeled

• --Authoritative state: two tables with database-assigned IDs.
• --Constrained values: an enum for the closed set, a dictionary for the open one - both enforced or encoded by the schema, not by convention.
• --Derived state: a transactional view that every write keeps current.
• --Bounded history: a ring buffer whose retention is part of the schema.

Where next

• --The data model - every primitive used here, in reference depth
• --Transactions - why the view and the write can never disagree
• --RQL in five minutes - the query language behind every snippet on this page