One Table, Not Daily Silos

"Model the thing that exists; derive the calendar views."

What actually persists

A daily practice tempts developers to model each date as a container. That makes Today easy and everything else awkward. The durable thing is the entry; dates, tags, and completion state are properties used to form views.

Views are queries

Use one entries table with stable identity, original text, origin language, entry date, timestamps, and state. Today, recent windows, calendar, and search all query the same rows instead of copying them into day buckets.

What ambiguity costs

Per-day files make moving, correcting, or searching entries a cross-container operation. They also encourage the false idea that a blank day needs a record. A collection stores writing, not obligations.

Collection principle. Model the thing that exists; derive the calendar views.

See one entry through four windows

Test “One Table, Not Daily Silos” by viewing one entry through Today, 7 days, 30 days, and calendar. State whether each view copies data or applies another predicate, and separate the questions answered by device-entry date and absolute timestamp. Creating fake rows for blank days means the model has begun storing obligation.

Query it

Write queries for Today and calendar dots using the same entries table. Include month end, year end, near-midnight creation, multiple entries on one day, and status = 'live'. Explain the predicates and indexes that produce the answer, not only the final count.

Memory should lead back

Dates and keywords make return paths without judging the writing. When search reads the same original and transcribed state and tags retain prompt provenance, one collection truly owns its history.

Code

Keep one durable collection·sql

CREATE TABLE entries (
  id TEXT PRIMARY KEY,
  entry_date TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  origin_lang TEXT NOT NULL,
  length_class TEXT NOT NULL,
  brain TEXT NOT NULL,
  soul_id TEXT,
  review_brain TEXT,
  original_text TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'live',
  transcribed_at TEXT
);
CREATE INDEX idx_entries_date ON entries(entry_date, created_at);

What actually persists

Views are queries

What ambiguity costs

See one entry through four windows

Query it

Memory should lead back

Code

External links

Exercise

Progress

Comments 0