Life Planner.ai: Your Private Workspace from Purpose to Task.

Life Planner.ai: Your Private Workspace from Purpose to Task #

A private, AI-assisted workspace that turns life purpose into goals, plans, and weekly tasks.

Overview #

Life Planner.ai is a standalone, single-user web application for people who want their daily work to be visibly connected to the reason they are doing it. It is not a task manager with a mission-statement field bolted on. It is built the other way round: you define why you live, turn that into life goals across every dimension of your life, break those into annual, quarterly and weekly commitments, and keep them alive through check-ins that produce tasks as their output.

Four commitments shape every decision in this document.

Only big goals belong here. Tasks are what falls out of a check-in, never the content you curate. The product is deliberately lean where project-management tools are rich: no dependencies, no Gantt charts, no resourcing, no teams.

Radical privacy by default. Nobody writes an honest eulogy into a shared workspace. Every document is private to its owner, there is no administrative path to read user content, the most sensitive classes are encrypted with per-user keys, and sharing is explicit, per-object and revocable. The product is hosted in Germany and designed against GDPR from the schema up.

AI drafts, the human decides. Assistance appears wherever a user would otherwise face an empty field or an unanswerable question — reformulating a vague goal into something vivid and measurable, proposing how to measure what resists measurement, mining a eulogy for the values hiding inside it. Every suggestion is marked, editable and refusable, nothing is ever written without an explicit accept, and the product is fully usable with AI switched off. Model access is bring-your-own-key: users connect their own provider, and the product never resells or bundles inference.

Slow answers are allowed. Purpose may stay blank for years without a single nudge. The finding is half the product, and a system that assumes you already know what you want has missed the point.

The product ships in German and English at launch, with German as the source language for all method content and English as a full peer. User content is never auto-translated, and the AI answers in the language of the content rather than the language of the interface.

How this document is organised #

Sections 1 through 3 establish what is being built and the vocabulary used to describe it. Sections 4 through 8 define the technical foundation: stack, conventions, schema, API and accounts. Sections 9 through 12 specify what the product actually does, module by module, including the AI layer. Sections 13 and 14 specify how it looks and behaves, screen by screen. Sections 15 through 20 cover language, security, sharing, infrastructure, testing and operations. Sections 21 and 22 sequence the build and instruct the agent or team executing it.

Each concern has exactly one canonical section. Other sections reference it by number rather than restating it, so there is one place to change any given decision. Every question this document could have left open has instead been decided and recorded, because an executor who has to stop and ask has been failed by the spec.

Table of Contents #

1. Before You Start: Customization Decisions #

This section exists so the executing agent never has to stop and ask a question. Every row has a DEFAULT safe to build against. If the operator has a real answer, they record it in the config file (1.2), overriding the default; if not, the default ships and the product is still correct, coherent, and launchable.

1.1 Decisions Table #

1.2 How to Use This Section #

The executing agent reads this table once, at build start, treating every DEFAULT as final unless overridden. Overrides live in exactly one file, never scattered across chat/code comments, so the whole system reads from a single source of truth.

File: /config/lifeplanner.config.yaml at the monorepo root. Loaded at build time by packages/shared (validates against a Zod schema, re-exports typed constants consumed by apps/web, apps/api, apps/worker) and at container start by the deploy script (Section 18), which injects values as environment variables.

Exact shape (every key optional; absence means "use the DEFAULT from 1.1"):

# /config/lifeplanner.config.yaml
product:
  name: "Life Planner.ai"
  marketingDomain: "lifeplanner.ai"
  appDomain: "app.lifeplanner.ai"
  apiDomain: "api.lifeplanner.ai"

brand:
  colors:
    primary: "#2A2820"         # Ink — primary text, 13.9:1 against background
    accent: "#985B31"          # Clay — non-text only: buttons, focus rings, icons
    statusGreenBase: "#437044" # Status Green — non-text only: shape/icon fill
    background: "#FAF8F5"      # Canvas
    secondaryText: "#6B6656"   # Ink Muted — secondary text, 5.4:1 against background
  typography:
    display: "Fraunces"
    body: "Inter"

locale:
  default: "de-DE"          # "de-DE" | "en-US"
  seedContentLanguage: "de" # "de" | "en"

modules:
  wheelOfLifeDefaultEnabled: true
  defaultCategoryPreset: "workbook-11" # "workbook-11" | "wheel-8"

billing:
  trialDays: 14
  trialRequiresCard: false
  priceMonthlyEur: 9.90
  priceAnnualEur: 89.00

ai:
  enabledProviders: ["anthropic", "openai", "gemini", "mistral", "openai-compatible"]
  recommendedProviders: ["anthropic", "openai"]

pwa:
  installPromptTrigger: "after_first_weekly_review" # | "first_load" | "never"

email:
  fromName: "Life Planner.ai"
  fromAddress: "hello@lifeplanner.ai"
  replyToAddress: "support@lifeplanner.ai"

legalEntity:
  companyName: "REPLACE_ME"
  legalForm: "REPLACE_ME"
  registeredAddress: "REPLACE_ME"
  registerCourt: "REPLACE_ME"
  registerNumber: "REPLACE_ME"
  vatId: "REPLACE_ME"
  managingDirector: "REPLACE_ME"

support:
  email: "support@lifeplanner.ai"

retention:
  trashDays: 30

security:
  totpMandatory: false

Validation rule (Zod schema in packages/shared, Section 5): boot in production fails if brand.colors.primary or brand.colors.secondaryText fails WCAG AA 4.5:1 against brand.colors.background (the only colours ever rendered as text). brand.colors.accent/statusGreenBase are checked against the weaker 3:1 non-text/UI gate instead (Section 13.5), since both are non-text accents only (1.1) and would never pass a 4.5:1 text check by design. Boot succeeds but logs a startup warning if any legalEntity.* field is still "REPLACE_ME", keeping row 14's placeholder rule enforceable in code.

2. Product Overview, Vision & Principles #

2.1 The Problem #

In the customer's own framing: task and goal apps track what to do but not why. Life-planning methods — eulogies, IKIGAI, values work, the Wheel of Life — live scattered across notebooks, documents, and separate apps, with no owned system connecting a stated purpose down to this week's tasks. People either over-plan trivial tasks or never translate purpose into action, and no existing tool treats purpose as something that may stay unanswered for years without nagging.

Concrete narrative. Mara, 41, runs a small consultancy, juggling a to-do app, a notebook with a one-time IKIGAI exercise, and a stalled "life stuff" Notes doc — twice a year an "I need to get my life together" evening ends with a few tasks added that reference nothing, feeling worse than before. No tool has ever asked her "what would your children say about this year, if you weren't here to hear it?" — a three-minute question worth more than a quarter of task-completion data. She needs one system that starts at "why" and doesn't let go of that thread down to Tuesday's task list, while letting the hardest questions stay blank as long as she needs.

2.2 What This Product Is — and Is Not #

Life Planner.ai is a private, AI-assisted workspace turning life purpose into goals, plans, and weekly tasks — a single owned system keeping the line from a person's stated "why" to their literal next action visible, walkable, and always optional to complete. Opinionated about the method (why → what → when → task), configurable about everything personal (categories, horizons, which modules are switched on).

It is emphatically NOT: a project-management tool in a lifestyle skin, a calendar, a Kanban filing cabinet for dreams, a social/comparative self-improvement feed, a quantified-self tracker, a native mobile app, a bundled AI subscription the vendor resells, or a system that assumes the user already knows what they want. Full non-goals list with rationale in 2.6.

2.3 The Eight Product Principles #

The whole design serves these eight; every later section is expected to comply. Where a principle and a convenience conflict, the principle wins.

1. Only big goals belong here — tasks are output, not content.

• Means: a small number of things that matter over years, not an inbox of everything; tasks serve a goal, never captured as freestanding "content."
• Forbids: a general-purpose to-do list, subtasks-of-subtasks nesting, or any UI treating task count as a success metric.
• Design consequence: a Task's goal_id may be null (3.6, orphans allowed), but there is no UI affordance to bulk-import an external task list — Section 17 keeps V3 external connectors read/write to existing linked goals only, never a bulk task dump.

2. Everything has a "Why," a vivid sensory description, and an image.

• Means: every first-class planning object (topic, goal, main goal, life goal) carries a why field and an image; AI assists help fill both in vivid, concrete language.
• Forbids: a goal-creation form where why/image hide behind a secondary tab or "advanced" toggle — they are primary, same-screen fields.
• Design consequence: on the Goal page (Section 14), why renders at the same visual weight as the title (2.5), never smaller or greyed out as secondary metadata.

3. Everything is rated — traffic light and/or % attainment.

• Means: every goal-level object carries both a four-color status (green/yellow/orange/red) and a 0-100 attainment percentage (arithmetic in 3.7).
• Forbids: a goal existing indefinitely with no status/attainment — fields are always present and defaulted, never nullable (3.3).
• Design consequence: Goal schema (Section 6) has NOT NULL on status and attainment_percent with defaults status='yellow', attainment_percent=0.

4. Everything is revisited — weekly, quarterly, annual retreat.

• Means: the rhythm layer (module 8) is the mechanism that keeps every rated object honest over time, not optional bookkeeping.
• Forbids: a "set and forget" goal with no re-rating touchpoint ever surfaced again.
• Design consequence: Section 11's Weekly Review must walk every active Priority Topic and every goal with status != archived — a computed item set, not a curated subset.

5. Radical privacy by default.

• Means: nobody, staff included, can read content unless the user takes an explicit, granular, revocable sharing action.
• Forbids: any admin content-read path, default-shared workspace, or telemetry payload containing user content.
• Design consequence: Section 16's zero-staff-access rule and Section 6's forced RLS on every user-content table are testable — an admin-role query against content must fail.

6. Slow answers allowed — Purpose may stay blank for years without nagging.

• Means: the hardest Foundation-layer questions (Purpose especially) may remain unanswered indefinitely with zero degradation of the product's usefulness.
• Forbids: onboarding gates, reminder emails, red badges, or blocked features tied to an unanswered Purpose, Vision, or Mission.
• Design consequence: Section 9's onboarding (module 0) marks every step skippable via a visible "skip for now" action, and Section 20's telemetry/notification rules must not include any "Purpose still empty" trigger.

7. AI drafts, the human decides.

• Means: AI-produced content is always a draft to accept, edit, or refuse — never an autonomous write.
• Forbids: any AI action persisting as final content without human acceptance, or any accepted AI suggestion visually indistinguishable from human-authored content.
• Design consequence: Section 12's origin field (ai/human/ai_edited) is written on every AI-touched record; the UI (Section 14) shows an "AI-drafted" mark until the user's first edit, verifiable via the record's origin transition.

8. Configurable where personal, opinionated where method.

• Means: categories, horizons, enabled modules are user-configurable; the method sequence (why → what → when → task) is fixed, not reorderable.
• Forbids: a settings screen that reorders Foundation → Goals → Execution, or removes the why field from goals.
• Design consequence: Section 6's category tables are user-scoped and freely editable (non-destructive per CONTEXT.md), while the Goal table's why column (3.3) has no "disable this field" setting anywhere in the schema.

2.4 Target Users — Four Personas #

Persona A — "The Structured Planner" (structured, private, long-term planning).

• Goals: a system that outlasts a resolution; sees purpose-to-this-week without app switching.
• Frustrations: bullet journals/spreadsheets abandoned within weeks — no living link between yearly plan and daily list, trust decays.
• Day in the life: Sunday, 20 min, full Weekly Review on laptop; Tuesday, 90 sec, checks "This week" on phone before school run.
• Surfaces: Weekly Review, This Week, Horizon, Goal page.

Persona B — "The Purpose-First Doer" (daily tasks visibly tied to deeper purpose).

• Goals: every task must visibly answer "does this matter and to what," or motivation drops.
• Frustrations: generic to-do apps feel like executing arbitrary commands; quit three apps because "the list didn't know why it existed."
• Day in the life: opens Home first (vision image, purpose in large type) before any task, then This Week where every line shows its parent goal.
• Surfaces: Home, This Week, Action Board (V2), Goal page.

Persona C — "The Framework Practitioner" (uses IKIGAI or Wheel of Life already).

• Goals: knows the frameworks; wants the method respected exactly, not flattened into generic goal fields.
• Frustrations: generic apps omit IKIGAI/Wheel or bolt them on as a one-time quiz never revisited or compared over time.
• Day in the life: fresh Wheel rating each quarter start, compares against last snapshot on the radar, sets one 7-day micro-goal from the keystone area same sitting.
• Surfaces: Map/radial (V2, hosts the Wheel), Priority Topics, Wheel snapshot history, Writing/Story view for IKIGAI journaling (V3).

Persona D — "The Coach or Partner" (future: limited explicit shared plan access).

• Goals: sees exactly the shared slice of a client's/partner's plan — enough to prepare a session, nothing more.
• Frustrations: today, coaching runs off screenshots or a shared doc that leaks too much, or nothing at all because the client keeps their plan private.
• Day in the life (V3, data model ready day one per CONTEXT.md): 15 min before a session, opens the read-only shared view of currently-shared goals and latest check-in insights; adds only session notes scoped to the share grant.
• Surfaces: shared read-only Goal page and Story view only — never Home, Capture, or anything outside the explicit share grant (Section 17 owns the model).

2.5 The Inside-Out Organizing Idea #

The product's central metaphor is inside-out: closer to the centre = more permanent, further out = more immediate. Purpose and Vision sit at the centre, then values, then topics by life area, then goals by horizon, then this week's actions at the rim. Zooming out answers "why am I doing this?"; zooming in answers "what do I do Tuesday?" Every object's lineage — task back to the life goal it serves — is drawn and walkable in both directions (3.6 gives the exact chain and orphan rules). This is why the product is explicitly not a Kanban board (CONTEXT.md, "User interface"): a board implies a pipeline a card graduates through and flattens time into columns; inside-out makes distance from the present moment the organizing axis instead.

The mental model for every topic and goal is a bank account: in credit or overdrawn. The recurring question, in UI copy and AI prompts alike: "What would 'in credit' look like here, and what measures it?" A Priority Topic or Goal is never just "on track" or "behind" — it's a balance being built up or drawn down, which is what makes the traffic-light status (2.3, principle 3) intuitive: green = in credit, red = meaningfully overdrawn and needs attention before more is spent from it.

2.6 Non-Goals #

Each row is a reasonable feature elsewhere, excluded here on purpose.

2.7 Success Criteria #

Qualitative (MVP):

• A new user goes from a blank Purpose field to a linked, completed task in one sitting, without leaving the product or needing outside instructions.
• A user who's never heard of IKIGAI or the Wheel of Life can still complete a meaningful Eulogy and Goal Capture session unaided — AI assists carry the method (principle 7).
• A user who deliberately leaves Purpose blank experiences zero nagging — no badge, reminder email, or red indicator — for the life of the account (principle 6).
• Reviewing "This Week," any task's serving goal is visible in one glance without opening the task (lineage always visible, CONTEXT.md Interaction laws).

Quantitative (MVP targets, first 90 days post-launch):

2.8 How to Read This Document #

Human reader, recommended order: Section 2 (vision, principles) → Section 1 (defaults) → Section 3 (domain model/glossary, assumed vocabulary) → Sections 4–8 (stack, conventions, data, API, accounts) → Sections 9–12 (module behaviour, domain layer order) → Sections 13–15 (frontend, surfaces, localization) → Sections 16–20 (security, sharing, infra, testing, analytics) → Sections 21–22 (milestones, executor instructions) last.

Executing agent: order doesn't matter — the document is executable cold, section by section, since every cross-cutting decision is fixed once (here, in CONTEXT.md, or in its canonical section) and referenced elsewhere rather than re-decided. Two sections implying different answers is a defect, not a decision for the executor: the canonical section (CONTEXT.md's ownership table) wins, and the conflict should be flagged.

3. Domain Model, Ubiquitous Language & Bilingual Glossary #

This section is canonical for every domain term, entity, enum value, and piece of lineage logic in the product. Sections 6 (database), 7 (API), 9–11 (module behaviour), and 15 (localization) implement what this section defines; none of them may introduce a competing definition, a competing enum, or different attainment arithmetic.

3.1 The Five-Layer Domain Model #

Five layers, matching the inside-out metaphor from 2.5: lower layer number = closer to "why," higher = closer to "what do I do Tuesday."

flowchart TB
  subgraph FOUNDATION["Layer 1 — Foundation (Why)"]
    EU["Eulogy (1..n, one per speaker)"]
    PU["Purpose (1..n)"]
    VI["Vision (1..n)"]
    MI["Mission (0..1)"]
    VA["Value (3..5 core)"]
    PR["Principle (0..n)"]
  end

  subgraph ASSESSMENT["Layer 2 — Assessment (Where)"]
    LA["Life Area / Category (0..n, configurable)"]
    WS["Wheel Snapshot (0..n, dated)"]
    PT["Priority Topic (0..n)"]
  end

  subgraph GOALSL["Layer 3 — Goals (What)"]
    G["Goal"]
    MG["Main Goal"]
    LG["Life Goal (max 3 confirmed, unlimited candidate)"]
  end

  subgraph EXECUTION["Layer 4 — Execution (How)"]
    AG["Annual Goal"]
    QG["Quarterly Goal"]
    KA["Key Activity"]
    KR["Key Result"]
    TA["Task"]
  end

  subgraph RHYTHM["Layer 5 — Rhythm (Alive)"]
    CI["Check-in"]
    WR["Weekly Review"]
    QC["Quarterly Close-out"]
    AR["Annual Retreat"]
    IN["Insight"]
  end

  EU -->|mined for| VA
  EU -->|mined for| G
  PU --> VI
  VI --> MI
  VA -.->|decision filter| G
  PR -.->|decision filter| G

  LA --> PT
  WS -->|rates| LA
  WS -.->|flags keystone| PT
  PT -->|source topic for| G

  G -->|promote| MG
  MG -->|promote| LG
  LG -.->|demote, reversible| MG
  MG -.->|demote, reversible| G

  LG --> AG
  MG --> AG
  G --> AG
  AG --> QG
  QG --> KA
  QG --> KR
  KA --> TA
  KR -.->|rolls up into| QG
  TA -.->|rolls up into| KA

  CI --> TA
  CI --> IN
  WR --> G
  WR --> PT
  QC --> AG
  AR --> LG
  AR --> VA

3.2 Entity Catalogue #

Default lifecycle unless noted: created → revised (any number of times) → archived or achieved → soft-deleted (30-day Trash, Section 6) → hard-deleted. Archived = user stopped pursuing it; achieved = completed as stated — both terminal-but-visible, set only explicitly by the user or an accepted AI-drafted action, never inferred silently.

Eulogy

• Definition: a written reflection, one per speaker, answering "what would you wish this person to say about you?" Fastest route to an authentic value set.
• Cardinality: 1..n per user, one per named speaker (partner, children, parents, siblings, friends, colleagues, the world, a higher power — configurable enum-like text column with CHECK per Section 6, not fixed; user may add a speaker not listed).
• Lifecycle: created → revised any number of times → archived (rare) → soft-deleted → hard-deleted after Trash window.
• Owner: Module 1 (Section 9).

Purpose

• Definition: a statement of why the user exists, arrived at via IKIGAI, the Venn variant, or stepwise elimination (2.5: innate genius × learned skills × lived story).
• Cardinality: 1..n per user (drafts may coexist; "current" = most recent is_primary).
• Lifecycle: created → revised → archived (superseded, kept for history) → soft-deleted → hard-deleted. May stay a "created, empty draft" for years (principle 6) — not a stalled/erroneous state.
• Owner: Module 2 (Section 9).

Vision

• Definition: "year X if all went perfectly," with a why, evidence, self-assessed percent attained — a concrete future scene, not an abstraction.
• Cardinality: 1..n per user.
• Lifecycle: created → revised → archived/achieved → soft-deleted → hard-deleted.
• Owner: Module 2 (Section 9).

Mission

• Definition: what the user tells others about what they do — outward-facing, single-paragraph distillation of Purpose and Vision.
• Cardinality: 0..1 primary per user (may draft several; one marked primary, same pattern as Purpose).
• Lifecycle: created → revised → archived → soft-deleted → hard-deleted.
• Owner: Module 2 (Section 9).

Value

• Definition: a core value (3-5 required) or ethical value, each with name AND description — "a word alone is not enough," enforced as required non-empty description.
• Cardinality: 3-5 core values recommended (soft guidance, not a hard cap; UI nudges 3-5 but never blocks a 6th).
• Lifecycle: created → revised → archived → soft-deleted → hard-deleted.
• Owner: Module 3 (Section 9).

Principle

• Definition: a self-defined rule of conduct; decision support like Value ("which principle does this goal serve or contradict?").
• Cardinality: 0..n per user.
• Lifecycle: created → revised → archived → soft-deleted → hard-deleted.
• Owner: Module 3 (Section 9).

Life Area (Category)

• Definition: a user-configurable classification bucket (e.g. "Health," "Career") that Priority Topics and Goals attach to; pre-populated from the chosen preset (3.9), every label and the set itself editable non-destructively (renaming/removing never deletes attached topics/goals — 3.6 orphan rules).
• Cardinality: 0..n per user; presets ship with 11 (workbook) or 8 (wheel).
• Lifecycle: created → revised (renamed) → archived (hidden from pickers, existing refs valid) → soft-deleted → hard-deleted (only once no live-referencing Topic/Goal remains, or refs reassigned to "Uncategorized").
• Owner: Module 4 (Section 10).

Wheel Snapshot

• Definition: a single dated rating pass across every active Life Area, 1-10 each, immutable once created (a new rating creates a new snapshot, never edits a past one).
• Cardinality: 0..n per user, one keystone_category flag per snapshot (biggest domino-effect category that round).
• Lifecycle: created (immutable) → superseded by a later snapshot (never "revised") → soft-deleted → hard-deleted. No "archived" state — history is the value.
• Owner: Module 4b (Section 10).

Priority Topic

• Definition: the permanent per-life-area planning source: category, description, ideal state, why, one or more measures, status, image. The entity 2.5's "bank account" model is written about most directly.
• Cardinality: 0..n per user, typically one active topic per Life Area (soft guidance, not enforced).
• Lifecycle: created → revised → archived (no longer actively worked) → soft-deleted → hard-deleted.
• Owner: Module 4 (Section 10); numeric alternative (KPI module: group=period, set=heading, KPIs=topics in %) same owner.

Goal (unified entity; level is an attribute, not a separate table — see 3.4)

• Definition: single object type spanning capture through Main Goal through Life Goal. level (goal|main_goal|life_goal) tracks promotion state; at life_goal level also carries life_goal_state (candidate|confirmed).
• Cardinality: 0..n per user at goal; unbounded at main_goal (soft guidance ~one per horizon per category, never enforced — 3.4); hard-capped at 3 for life_goal+confirmed, unlimited for life_goal+candidate.
• Lifecycle: created (captured, typically via a W-question, level=goal) → revised → promoted/demoted any number of times (3.4) → archived or achieved (attainment reaches 100, or explicit mark) → soft-deleted → hard-deleted.
• Owner: Module 5 (capture, Section 10) and Module 6 (promotion, Section 10).

Annual Goal

• Definition: top-level execution container for a Life/Main/Goal within a calendar year, built at year-end by walking documents/backlogs. Owns Key Activities/Results indirectly, via Quarterly Goal — a real stored entity, not a view (boxed note below).
• Cardinality: 0..n per user per year; exactly one parent Goal (any level) via goal_id.
• Lifecycle: created (annual planning) → revised through the year → closed at year-end (archived/achieved, informs next year's walk) → soft-deleted → hard-deleted.
• Owner: Module 7 (Section 11).

Quarterly Goal

• Definition: one row per Annual Goal per quarter (1-4) — real parent object Key Activities/Results attach to, carrying its own status/attainment_percent (3.7 roll-up applies) so Quarterly Close-out (module 8) has something concrete to open/re-rate/close.
• Cardinality: 0..4 per Annual Goal per year, created as quarters are actually planned.
• Lifecycle: created (annual/quarterly planning) → revised through the quarter → closed at quarter-end (archived/achieved, feeds next quarter) → soft-deleted (30-day Trash, RLS enforced like every user-content table) → hard-deleted.
• Owner: Module 7 (Section 11).

Design decision — "Quarterly Goal" is a real table, not a view. CONTEXT.md's literal lineage chain (Life Goal → Main Goal → Goal → Annual Goal → Key Activity → Key Result → Task) has no Quarterly Goal node, which would suggest a computed view. Superseded: Section 6 implements quarterly_goals as a real table — its own row per Annual Goal per quarter, RLS, standard 30-day Trash lifecycle — and key_activities/key_results carry quarterly_goal_id uuid NOT NULL REFERENCES quarterly_goals(id) rather than a bare quarter integer. The real chain is Annual Goal → Quarterly Goal → Key Activity/Key Result, one hop longer than CONTEXT.md's wording but a faithful implementation of its "assigned to a quarter" behaviour (3.1's diagram matches). Why: a real table gives Quarterly Close-out something concrete to open/re-rate/close (principle 4), gives Quarterly Goal its own status/attainment_percent under 3.7 — a view can't carry RLS-protected state or its own Trash lifecycle. Glossary (3.8) names this table's row "Quarterly Goal (Quartalsziel)."

Key Activity

• Definition: a concrete activity in service of a Quarterly Goal (3.2), parent of the Tasks that implement it. The "how," not the measurable outcome (that's Key Result's job).
• Cardinality: 0..n per Quarterly Goal.
• Lifecycle: created → revised → completed/archived → soft-deleted → hard-deleted.
• Owner: Module 7 (Section 11).

Key Result

• Definition: a measurable outcome for a Quarterly Goal (3.2): kr_kind improve|maintain (3.7), baseline/target/current value, and (for maintain) a tolerance. Never a checklist item — the Tasks that move its current value are created during Check-ins (module 8), not at annual-planning time.
• Cardinality: 0..n per Quarterly Goal (may exist without a sibling Key Activity, and vice versa).
• Lifecycle: created → revised (current value updated over time) → closed (achieved/missed, both terminal-but-visible) → soft-deleted → hard-deleted.
• Owner: Module 7 (Section 11).

Task

• Definition: atomic unit of execution: due date, optional "this week" flag, completion boolean rolling up into parent attainment (3.7), full visible ancestry (3.6). Comes from Check-ins (module 8), not annual planning — Key Results get planned, Tasks get done, week to week.
• Cardinality: 0..n, optionally parented by a Key Activity, a Goal directly, or nothing (orphan, 3.6). Never parented by another Task — no subtask nesting (principle 1).
• Lifecycle: created → revised → completed (or abandoned, both terminal) → soft-deleted → hard-deleted.
• Owner: Module 9 (Section 11).

Micro-goal

• Definition: a lightweight commitment from the Wheel of Life's keystone area: a 7-day action plus a 90-day metric. Intentionally NOT a Goal record — 7 days doesn't fit the horizon_years granularity (0/1/3/5/10, 3.3), so forcing it in would misrepresent the timeframe or require special-casing. Its own minimal entity: wheel_snapshot_id origin, life_area_id, description, target_date (created+7d default), 90-day metric description+target value, status. MAY be promoted into a full Goal (one-way: creates a new Goal, marks Micro-goal converted, link preserved) — a converted Micro-goal is never deleted, only marked converted, so Wheel history stays complete.
• Cardinality: 0..n per user, typically one active per Wheel Snapshot's keystone area.
• Lifecycle: created → (7 days pass, self-assessed done/not-done) → closed (done/not-done/converted) → soft-deleted → hard-deleted.
• Owner: Module 4b (Section 10).

Check-in

• Definition: a conversational, one-item-at-a-time touchpoint on a single goal: status, define/close tasks, capture insights. Distinct from Weekly Review (walks everything) — scoped to one goal, on demand or from a Weekly Review's item walk.
• Cardinality: 0..n per Goal (any level) or per Annual Goal.
• Lifecycle: created → completed (point-in-time event, not revised after) → soft-deleted (audit trail) → hard-deleted.
• Owner: Module 8 (Section 11).

Weekly Review

• Definition: a scheduled walk of every active Priority Topic and non-archived goal, re-rating each — the mechanism behind principle 4. (Life Areas carry no re-ratable fields; Priority Topics are what's actually walked.)
• Cardinality: 0..n per user (weekly typical, not enforced).
• Lifecycle: created (started) → completed → soft-deleted → hard-deleted. A started- but-abandoned review stays in_progress indefinitely — no forced completion (principle 6 extended to rhythms).
• Owner: Module 8 (Section 11).

Quarterly Close-out

• Definition: end-of-quarter walk closing that quarter's Quarterly Goal — its Key Activities and Key Results (3.2 boxed note) — and seeding the next quarter's.
• Cardinality: 0..n per user, nominally one per quarter.
• Lifecycle: created → completed → soft-deleted → hard-deleted.
• Owner: Module 8 (Section 11).

Annual Retreat

• Definition: a 1-2 day solo session: revisit Foundation-layer content, run the year-end Annual Goal walk, and (3.5) optionally revisit candidate life goals.
• Cardinality: 0..n per user, nominally one per year.
• Lifecycle: created → completed → soft-deleted → hard-deleted.
• Owner: Module 8 (Section 11).

Insight

• Definition: an observation logged during any Check-in, Weekly Review, Quarterly Close-out, or Annual Retreat — free text plus a link back to the object it concerns.
• Cardinality: 0..n per user.
• Lifecycle: created → (rarely revised) → archived → soft-deleted → hard-deleted.
• Owner: Module 8 (Section 11).

3.3 The Shared Goal Attribute Set #

Defined once, exhaustively. Every Goal (any level) and every Annual Goal carries this set; Key Activity/Key Result carry status and attainment_percent/value fields but not priority, horizon, or W-tag (goal-level-only, inherited by reference down the lineage, not duplicated onto children).

Decision — horizon's three-value edge case. CONTEXT.md describes horizon_years as "0,1,3,5,10, or >10," but ">10" can't be stored as a discrete integer. Decision: horizon_years accepts only {0,1,3,5,10} when horizon_kind = relative_years. Anything vaguer than 10 years ("someday," no specific date) uses horizon_kind = lifetime instead. Anything with a specific target year beyond 10 years out (e.g. "by 2045") uses horizon_kind = absolute_year with horizon_year = 2045. This removes the ambiguity while preserving every case the original phrasing covered.

3.4 Promotion & Demotion Rules #

Goal → Main Goal. Any Goal at level goal may be promoted to main_goal by explicit user gesture (never automatic, never AI-initiated without acceptance, per principle 7). No hard cap on Main Goals. Non-blocking UI warning if a second Main Goal is promoted within the same horizon_kind/horizon_years/horizon_year+category combination ("you already have a Main Goal here — promote anyway?") — CONTEXT.md's module 6 description ("promote the most important goal per horizon") is a recommendation the tool nudges toward, not an enforced constraint (principle 8).

Main Goal → Life Goal. A Main Goal may be promoted to life_goal (life_goal_state = candidate by default). Promoting directly from goal to life_goal is allowed in one step (state machine on level, not strictly sequential). A life_goal may only move to life_goal_state = confirmed while fewer than 3 other Goals hold level = life_goal, life_goal_state = confirmed; a 4th confirmation attempt returns error LIFE_GOAL_LIMIT_REACHED (Section 7's error envelope) until the user demotes or archives an existing confirmed Life Goal. Candidate Life Goals are never capped (3.5).

Demotion. Every promotion is reversible: life_goal → main_goal, main_goal → goal, life_goal(confirmed) → life_goal(candidate) — one user gesture each, any time, no cooldown. Demoting never deletes or re-parents any child Annual Goal, Key Activity, Key Result, or Task — goal_id references stay untouched; only level (and life_goal_state) changes.

History. Every level or life_goal_state transition — old value, new value, timestamp, human-gesture flag (promotions are always human-initiated even when AI-suggested; AI can suggest but never write the promotion, principle 7) — is written to an append-only history log keyed to the Goal (Section 6 owns storage). Retained through soft-delete, purged only at the Goal's hard-delete.

3.5 The "Candidate" Life-Goal State #

A life_goal may sit in life_goal_state = candidate indefinitely — years, if needed — with zero nagging, implementing principle 6 at the Life-Goal layer. The product never surfaces a "confirm this candidate" reminder, badge, or notification outside one narrow, opt-out-able touchpoint: the Annual Retreat (module 8) may include an optional prompt, "Revisit your candidate life goals?", shown once per year at most, with a per-item "stop asking about this one" toggle honored permanently once set. This is the only place candidate Life Goals are ever proactively surfaced — nowhere else (Home, This Week, Weekly Review, notifications) references an unconfirmed candidate's status.

3.6 Lineage & Orphans #

The full lineage chain, walkable in both directions, per CONTEXT.md's exact spec:

Life Goal → Main Goal → Goal → Annual Goal → Key Activity → Key Result → Task

This is the maximal chain; per 3.2's Annual Goal definition, an Annual Goal's goal_id may point directly at a Goal, Main Goal, or Life Goal — the chain "telescopes" depending on promotion depth at creation time. A Task's ancestry display (Section 14, Goal page and This Week) always walks whatever chain actually exists, showing every populated link.

Orphans are explicitly allowed, at two points in the chain, by design:

1. A task with no goal. Task.goal_id (and key_activity_id) are nullable — forcing every task through a goal link would violate CONTEXT.md's "never block on a required field" law and contradict principle 1's framing of tasks as lightweight output. Display: orphan tasks appear in "This Week" tagged Unlinked (subtle label, not a warning color) with a one-tap "attach to a goal" affordance; roll up into a virtual "Unassigned" bucket on the Action Board (V2) rather than being hidden. Surfaced later: Weekly Review (module 8) includes unlinked tasks older than 14 days in its "what needs attention" pass, with a gentle "still don't know what this serves?" prompt — never before day 14, so same-day quick capture is never nagged.

2. A goal with no topic/category. Goal.category is nullable — module 5's Goal Capture flow lands new goals category-less ("horizon, category, priority and why assigned afterwards," CONTEXT.md). Display: uncategorized goals appear in an "Uncategorized" bucket on Map (radial) and Horizon views — fully functional, can carry Annual Goals/Tasks like any other goal. Surfaced later: the same Weekly Review pass flags any goal still uncategorized after 14 days; the AI review-prep assist (Section 12, "what haven't you touched in nine weeks") includes uncategorized goals regardless of the 9-week staleness window, since these are worth surfacing sooner.

The 14-day threshold is one constant (ORPHAN_SURFACE_AFTER_DAYS = 14), defined once in packages/shared/constants, referenced by both rules so they never drift apart.

3.7 Attainment Roll-Up Arithmetic #

The exact, unambiguous arithmetic Sections 6 and 11 implement. No section may define different math.

Inputs at each node. A node with attainment (a Goal at any level, an Annual Goal, or a Quarterly Goal — 3.2) derives attainment_percent from whichever children it has:

• Direct Tasks (via goal_id): each contributes 100 if completed, else 0, weighted by task.weight (numeric, default 1, editable per task; weights are relative, need not sum to a total).

• Direct Key Results (via the owning Quarterly Goal, 3.2): each carries kr_kind text CHECK (kr_kind IN ('improve','maintain')), fixed at creation:

  • improve — moving from baseline toward target. Contributes clamp(0, 100, round((current_value - baseline_value) / (target_value - baseline_value) * 100)). A CHECK forbids target_value = baseline_value on improve (undefined at that point — belongs to maintain).
  • maintain — "hold steady" (e.g. "maintain weight at 70kg"), where target_value legitimately equals baseline_value. Contributes 100 while current_value is within tolerance (numeric, same unit, required/non-null on maintain, N/A to improve) of target_value, else 0. No partial credit.

  Each Key Result is weighted by key_result.weight_percent (integer; no separate weight column). All Key Results under one Quarterly Goal must have weight_percent summing to exactly 100 — validated on save. Default weighting on creation: equal split among siblings. Auto-rebalance on add/remove uses largest-remainder distribution: divide 100 by sibling count, floor as base share, distribute remaining points one at a time to largest fractional remainders, tie-break by lowest id (UUIDv7, creation order). Runs automatically only while no sibling's weight_percent has been hand-edited; once one has, add/remove doesn't silently touch the others — UI (Section 14) prompts explicit rebalance instead.

• Child Goals (Main Goal's child Goals, Life Goal's child Main Goals): each contributes its own attainment_percent, weighted equally by default (100 / count_of_children), overridable per child via rollup_weight on the parent-child relationship, same summing rule as Key Results.

Combining multiple kinds of children at one node. Each kind of child (e.g. direct Tasks vs. child Goals) counts as one weighted bucket in the overall average — bucket-level inputs combine with equal weight by default (50/50 for two, evenly split for three), overridable the same way. This two-level structure (item weight within a bucket, bucket weight across kinds) keeps arithmetic consistent at any tree depth.

Rounding. Every node's computed value rounds to the nearest integer, half-up (stated once here, governs every node — no other section may apply a different rule; Section 19.2 must match), before being used as an input one level up. Never deferred to the top of the tree — bounds compounding error to one rounding step per level (Section 6's triggers must implement it this way; rounding only at the end would give non-reproducible results depending on tree shape).

Computation trigger. 3.7 fixes the arithmetic only. Section 6 implements the trigger mechanism with database triggers (not an app-layer recompute, not a generated column), firing on every write that changes an input — task completion toggle, Key Result value update, weight change, soft-deletion (OR UPDATE OF deleted_at) — walking upward until reaching root or a manually-overridden node. Section 6's rollup triggers touch only a separate rollup_version counter, never the user-facing version column used by If-Match optimistic concurrency (Section 7) — a background recompute must never cause a spurious 412 VERSION_CONFLICT for a user who made no edit.

Manual override. A user may set attainment_percent directly on any node, setting attainment_source = 'manual' (default 'auto'). A manual node:

• Is not recalculated automatically when children change — stored value is authoritative until the user acts again.
• Still propagates upward exactly as an automatically computed value would — the frozen attainment_percent is simply the parent-level rollup's input for this node.
• When a manual node's children change in a way that would alter the automatic computation, the UI (Section 14) shows a non-blocking banner: "Children have changed since you set this manually — recalculate?" Clicking sets attainment_source back to 'auto' and recomputes immediately; dismissing leaves the manual value untouched indefinitely. The system never silently overwrites a manual value.

3.8 Bilingual Glossary #

These translations are FROZEN. Section 15 applies them verbatim across every UI string, seed content item, and AI system prompt; no other section may introduce an alternate translation for any term below.

3.9 Category Presets — Workbook-11 and Wheel-8 #

Two presets ship (Section 1.1 row 7 fixes workbook-11 as default). Both are non-destructive starting points — a user may rename, remove, add to, or fully replace either preset's categories at any time (CONTEXT.md: "Categories: configurable; workbook (11) and Wheel (8) as presets; non-destructive").

Workbook-11 (default preset, used for Priority Topics from first onboarding):

Wheel-8 (classic Wheel of Life set, used by module 4b and offered as an alternate preset at first Priority Topics setup):

Switching a user's active preset (e.g. turning on module 4b for a Workbook-11 user) does not delete or merge existing Life Areas; it offers Wheel-8 as additional categories alongside what the user already has. The Wheel of Life rating screen operates over whichever categories are flagged wheel_tracked = true — by default the Wheel-8 set when freshly enabled, but user-editable immediately afterward.

4. Technology Stack & System Architecture #

4.1 Purpose and scope #

CANONICAL for the technology stack and system topology. Every package, version and service named here is fixed; no other section may substitute a library. Defines runtime topology, request lifecycle, background-job architecture, caching model, offline architecture, and the env-variable catalogue. Sections 6, 7, 16, 18, 22 build on this and must not redeclare stack choices.

4.2 Dependency table #

Versions are exact minimums; pin in package.json using the caret range shown (^) unless marked "exact" (use =). All packages are npm registry unless noted.

4.2.1 Root / tooling #

4.2.2 apps/web #

4.2.3 apps/api #

4.2.4 apps/worker #

Shares drizzle-orm, bullmq, ioredis, pino, @opentelemetry/sdk-node, sharp, @aws-sdk/client-s3 with apps/api (pnpm-hoisted). Additional:

4.2.5 packages/shared, packages/db, packages/i18n, packages/ui #

4.3 Monorepo layout #

Three levels deep for every package in CONTEXT.md. One line of purpose per directory.

lifeplanner/
├── apps/
│   ├── web/ # React 19 SPA — user-facing product
│   │   ├── src/
│   │   │   ├── routes/     # TanStack Router file-based routes (one file = one URL)
│   │   │   ├── features/   # capture/, horizon/, map/, goals/, checkin/, tasks/, ai/
│   │   │   ├── components/ # App-level composed components (not design-system primitives)
│   │   │   └── lib/        # Query client, offline queue, i18n init, API client instance
│   │   ├── public/ # Static assets, PWA manifest, icons
│   │   └── tests/  # Component + a11y tests (Vitest + Testing Library)
│   ├── api/ # Fastify HTTP API — synchronous request handling
│   │   ├── src/
│   │   │   ├── routes/   # One file per resource, mirrors /api/v1/<resource>
│   │   │   ├── plugins/  # auth, RLS-context, rate-limit, error-handler
│   │   │   ├── services/ # Business logic called by routes, framework-agnostic
│   │   │   └── lib/      # DB client, queue producers, encryption helpers
│   │   └── tests/ # Integration tests against a real Postgres test container
│   ├── worker/ # BullMQ worker processes — asynchronous execution
│   │   ├── src/
│   │   │   ├── queues/ # Queue + worker definitions, one file per queue (4.7)
│   │   │   ├── jobs/   # Job handlers, one file per job type
│   │   │   ├── ai/     # anthropic.ts, openai.ts, gemini.ts, mistral.ts, openai-compatible.ts
│   │   │   └── lib/    # Idempotency store, DLQ helpers
│   │   └── tests/ # Job handler unit tests with a fake queue
│   └── kms/ # Internal KMS, own trust boundary (4.4); ONLY process loading KEK_MASTER_KEY (16)
│       ├── src/
│       │   ├── rpc/ # wrap-dek / unwrap-dek / rotate-master-key handlers (4.4.1)
│       │   └── lib/ # In-memory master-key cache — never persisted, never logged
│       └── tests/ # RPC contract, key-rotation, no-egress tests
├── packages/
│   ├── shared/ # Zod schemas + inferred TS types + domain constants — the contract
│   │   ├── src/
│   │   │   ├── schemas/    # One file per domain object (goal.ts, topic.ts, checkin.ts, ...)
│   │   │   ├── constants/  # W-tags, priority/status enums, horizon kinds (mirrors Section 3)
│   │   │   └── api-client/ # Typed fetch wrapper for apps/web, generated route types
│   │   └── tests/ # Schema round-trip tests
│   ├── db/ # Drizzle schema + migrations — the persistence contract
│   │   ├── src/
│   │   │   ├── schema/     # One file per table group, mirrors Section 6 domain layers
│   │   │   ├── migrations/ # Timestamp-prefixed SQL migrations, each with a rollback note
│   │   │   └── seed/       # Deterministic dev/test seed data (workbook category presets)
│   │   └── tests/ # RLS policy tests (cross-user isolation)
│   ├── i18n/ # Message catalogs + bilingual glossary — the language contract
│   │   ├── src/
│   │   │   ├── locales/  # de-DE/, en-US/ — one ICU JSON catalog per namespace
│   │   │   └── glossary/ # Frozen DE/EN domain-term pairs (3), generated types
│   │   └── tests/ # Catalog-key parity tests (every DE key has an EN key and vice versa)
│   └── ui/ # Design-system components — the visual contract
│       ├── src/
│       │   ├── primitives/ # Radix wrappers: Dialog, Tabs, Slider, Popover, Toast
│       │   ├── components/ # Composed: Button, GoalCard, ProgressRing, WTagBadge, RadialNode
│       │   └── tokens/     # Tailwind theme extension: color, spacing, motion durations (13)
│       └── tests/ # Visual + a11y snapshot tests
├── infra/              # Docker Compose files, Traefik config, deploy scripts (18)
├── .github/workflows/  # CI/CD pipelines (18)
└── turbo.json, pnpm-workspace.yaml, tsconfig.base.json, eslint.config.js, .env.example

4.4 Runtime topology #

User's browser (PWA), apps/web static bundle
  │ HTTPS (TLS 1.3)                             [Boundary A: public internet -> edge]
  ▼
Traefik v3 (Hetzner nbg1) — TLS termination (Let's Encrypt), routes by Host header
  │ HTTP (internal Docker network)              [Boundary B: edge -> app]
  ▼
Fastify API (apps/api) — auth plugin -> RLS-context plugin -> route
  │                                              [Boundary C: app -> data]
  ├─▶ PostgreSQL 17 (RLS forced)
  ├─▶ Valkey 8 (queues, cache, rate-limit) ──▶ BRPOP/BullMQ protocol ──▶ Worker (apps/worker, BullMQ consumers)
  ├─▶ Hetzner Object Storage (S3-compatible; images, exports, backups)
  └─▶ KMS (apps/kms), RPC only                  [Boundary G: app/worker -> kms]

Worker (apps/worker) additionally calls:
  ├─▶ KMS (apps/kms), same RPC contract as api  [Boundary G]
  ├─▶ AI provider (user's key; Anthropic/OpenAI/Gemini/Mistral/OAI-compatible) [Boundary E: worker -> user's own AI provider]
  ├─▶ Brevo (SMTP, EU, DPA)                     [no numbered boundary — EU DPA-covered]
  └─▶ Stripe (Billing, Tax)                     [Boundary F: worker -> Stripe]

KMS (apps/kms): own container + own Docker network namespace; no route from Traefik, no
public exposure; the ONLY process that ever loads KEK_MASTER_KEY; RPC contract in 4.4.1.

Every boundary-crossing arrow carries TLS (boundary G is instead confined to a network namespace no other container can reach — 4.4.1). User content crosses network edges at exactly three points: A (browser to Traefik — HTTPS covers transport), E (worker to the user's own AI provider, only consented fields, decrypted in-worker-memory just before the call, never written to disk unencrypted), and C (API/worker to Postgres, content already application-encrypted for CONTEXT.md's sensitive classes before the row is written). D carries only images/export archives, never raw structured text. F carries billing metadata only. G carries only wrapped/unwrapped DEKs — never user content, never the Master KEK, which never leaves the kms container.

4.4.1 KMS service and RPC contract #

apps/kms is deliberately the smallest service in the system: a Fastify process with no database connection, no object-storage credentials, no outbound network access except its own healthcheck — its only job is holding KEK_MASTER_KEY in memory and performing key-wrap/key-unwrap on request. This is the trust boundary 16 mandates: api and worker never load, hold, or log the Master KEK; they call kms over the internal Docker network at KMS_RPC_URL and receive back only a wrapped/unwrapped per-user DEK, never the KEK. Canonical KMS interface contract; Sections 16 and 21 reference it, don't restate it.

RPC contract (JSON over internal HTTPS, Fastify — no lp_session cookie or CSRF token; trust is established by mTLS between the caller and kms over the private network, since kms has no route from Traefik and is unreachable except from api/worker):

kms fails to boot if KEK_MASTER_KEY is absent/malformed — no degraded mode. api and worker fail their own boot check (18) if KMS_RPC_URL is unreachable.

4.5 Request lifecycle — representative write #

Example: PATCH /api/v1/goals/:id (user edits a goal's title and why-text).

1. Traefik terminates TLS, forwards to Fastify over the internal Docker network, preserving X-Forwarded-For for rate-limit keying.
2. Rate-limit plugin (@fastify/rate-limit, Valkey-backed) checks the endpoint-class bucket for this session/IP; on excess returns 429 with RateLimit-* headers before any further work.
3. Auth plugin reads the lp_session cookie, hashes the token (SHA-256), looks it up in the sessions table (fast, indexed, non-RLS). Missing/expired session -> 401 UNAUTHENTICATED. Valid session resolves a userId.
4. CSRF plugin verifies X-CSRF-Token matches the double-submit cookie for this unsafe method. Mismatch -> 403 CSRF_TOKEN_INVALID.
5. RLS-context plugin opens a Postgres transaction and runs SELECT set_config('app.current_user_id', $1, true) (transaction-local, Section 6) with the resolved userId. Every subsequent query is now RLS-scoped — a mismatched-user_id row is invisible to the planner regardless of the route's own WHERE clause.
6. Route handler (apps/api/src/routes/goals.ts) parses the body against updateGoalSchema via fastify-type-provider-zod. Schema failure -> 422 with the canonical error envelope (7.4), details per-field — handler body never runs.
7. Optimistic concurrency check: handler compares If-Match header to the row's current version. Mismatch -> 412 VERSION_CONFLICT before any write (canonical per 7.4).
8. Service layer (apps/api/src/services/goals.ts) applies domain rules (e.g. a life_goal cannot silently lose its life_goal_state), calls encryptSensitiveFields(goal, ['title', 'why']) — envelope-encrypts with the user's DEK (16) — and recomputes the why blind-index tokens so search stays functional.
9. Drizzle update runs UPDATE goals SET ... version = version + 1, updated_at = now() WHERE id = $1 RETURNING * inside the same RLS-scoped transaction.
10. Audit write: same transaction, INSERT INTO audit_log records actor_user_id, action = 'goal.updated', entity_id, changed_fields (names only, never values — 5.5 redaction rule), request_id. Audit rows are append-only, excluded from RLS user_id scoping (admin-queried by actor_user_id only, metadata-only, zero-staff-access promise).
11. Commit. Transaction commits; set_config(..., true) scoping is discarded with it (transaction-local).
12. Response envelope: { "data": { ...decrypted-for-response goal }, "meta": {} }, meta omitted when empty (7). Sensitive fields decrypted once for this response only, using the same in-request DEK — never cached decrypted.
13. Structured log line (pino, JSON) at info with requestId, route, userId (hashed, 5.5), statusCode, durationMs — never title or why.
14. OpenTelemetry span for the request closes and exports to Tempo; audit write and encryption each get child spans for latency visibility.

If step 6, 7, 8 or 9 throws, the error hierarchy (5.4) maps it to an HTTP code and the transaction rolls back atomically — no partial write, no orphaned audit row.

4.6 Cross-cutting non-choices #

4.7 Background job architecture #

All jobs run on BullMQ over Valkey 8. One queue per row below; names are the literal strings used in code (new Queue('reminder-dispatch', ...)).

Global BullMQ settings: removeOnComplete: { age: 86400, count: 1000 }, removeOnFail: false (failed jobs stay until moved to their DLQ by a dedicated onFailed handler — nothing lost silently). Every payload is Zod-validated (from packages/shared) before enqueue and again on dequeue (defends against a schema change between enqueue and a delayed job's run).

4.8 Caching #

No page-level HTTP caching beyond static assets is used for API responses — every authenticated response is user-specific/RLS-scoped, so caching at any shared layer (CDN, Traefik) risks cross-user leakage. Static apps/web build assets get long-lived immutable Cache-Control (hashed filenames) directly from Traefik.

4.9 Offline / PWA architecture #

Installable: vite-plugin-pwa generates a manifest (name, icons, display: standalone, theme colors from Section 13 tokens) and a Workbox service worker.

What is cached (read path):

• App shell (JS/CSS bundles, fonts, icons): precached on install, StaleWhileRevalidate.
• Last-synced snapshot of: Home surface data (vision image, purpose text, values, one next step), the active Check-in's questions, and This Week's list. Cached in IndexedDB via a TanStack Query persister (@tanstack/query-persist-client-core) keyed by userId. Section 13.11's mobile full-parity surfaces are Home, Capture, Check-in; this read cache substitutes This Week for Capture deliberately — Capture is write-first with no pre-existing data to read (its offline story is the mutation queue below); This Week is read-heavy and more likely opened than written to offline, so its snapshot is higher-value.
• Everything else (Map, Horizon, Story, full goal history) is online-only; offline shows a calm "you're offline — reconnect to see this" state, never a blank page (never-an-empty-page law).

Offline cache encryption (canonical — applies to both the read cache and the mutation queue below): the Home snapshot carries purpose text (Section 16's most-sensitive class) and the mutation queue carries raw Capture/Check-in payloads before server-side encryption — unencrypted IndexedDB storage would be readable to anything with local device access, bypassing Section 16 entirely. Both are encrypted client-side before every write:

• On login, alongside the httpOnly lp_session cookie (7), the API sets a second cookie lp_offline_key_material — Secure, SameSite=Lax, not httpOnly, same TTL as SESSION_TTL_DAYS, 32 bytes of random key material authenticating nothing alone (compromise grants only read access to a device's own already-downloaded offline cache).
• On app start, apps/web derives a non-extractable AES-256-GCM CryptoKey via crypto.subtle.importKey('raw', material, 'HKDF', false, ['deriveKey']) -> deriveKey(...), held only as an in-memory handle for the tab's life. Deterministic derivation re-derives the same key every reload, including offline, so cached ciphertext stays decryptable without a server round-trip.
• Every record written to the TanStack Query persister or Background Sync queue is crypto.subtle.encrypt-ed (AES-GCM, random 12-byte IV per write, prepended to ciphertext) before reaching IndexedDB; reads reverse the step. Decryption failure (stale key) is a cache miss, never a crash — falls back to the never-an-empty-page state.
• Purge: both stores deleted in full (indexedDB.deleteDatabase) on logout and on session-cookie expiry (detected on the next 401 UNAUTHENTICATED, Section 7.4). lp_offline_key_material clears in the same step, so an incomplete purge still leaves ciphertext unrecoverable without a fresh login.

Which mutations queue offline: only Capture (new W-tagged backlog items) and Check-in (status updates, insight notes, task completion toggles) are queueable. Queued via a Workbox Background Sync queue, encrypted per the rule above, each entry carrying: a client-generated UUIDv7 id (final id before the server ever sees it — critical for offline-created goals/tasks), the mutation payload, and If-Match: null (no prior version). All other mutations (editing a goal's why-text, deleting a topic, reordering the Horizon) are disabled offline — controls show a disabled state with a tooltip rather than queueing an edit to a record of unknown current state.

Conflict resolution rule: when a queued mutation reaches the server and the target changed server-side meanwhile:

• Offline-created entities (client-generated id, no prior version): always accepted as a new insert — no server-side row to conflict with by definition.
• Offline-updated entities (Check-in status/insight only, per the queueable scope above): the API compares the mutation's captured pre-offline version (snapshotted at queue time) against the current row version. Unchanged -> apply normally. Changed -> field-level last-write-wins merge: only fields the offline mutation touched (e.g. status, insightNote) are overwritten; fields the other write changed and offline left untouched are preserved. Merged version increments once; client cache reconciles from the server response, not its own optimistic guess.
• User sees a one-line dismissible notice ("some check-in updates were merged while you were offline") after any sync flush involving a conflict-merge — "lineage always visible" applied to sync history.

4.10 Environment configuration — canonical variable catalogue #

This table is CANONICAL. Sections 18 and 22 reference it and must not introduce undocumented vars.

kms deliberately has no row for DATABASE_URL, VALKEY_URL, any S3_*, STRIPE_*, BREVO_*, AI_*, RATE_LIMIT_*, SESSION_*, CSRF_SECRET, or BLIND_INDEX_PEPPER — no database, queue, object storage, or path to any external service, which is what makes its trust boundary (4.4, 4.4.1) meaningful rather than nominal.

AI_ALLOW_INSECURE_BASE_URL (default false) is a local-development-only escape hatch: true disables the private-IP/localhost-range block in Section 16.6's SSRF guard so a developer can point an OpenAI-compatible adapter at a localhost/Docker-internal Ollama or vLLM instance. Production boot check (apps/api/apps/worker, Section 18) refuses to start if NODE_ENV=production and AI_ALLOW_INSECURE_BASE_URL=true are both set — loud boot-time failure, not a silently weakened SSRF guard. Section 19.7's SSRF test exercises both states.

Secrets are never committed; .env.example lists every name above with an empty/placeholder value and a comment. Production secrets are injected via the deploy script from a secrets store on the Hetzner host (18), never baked into images. kms's secret (KEK_MASTER_KEY) is provisioned into the kms container only — never written into api/worker's environment, so a compromised api/worker container has no path to it.

4.11 Build and dev workflow #

All commands run from the repo root via Turborepo, fanning out to the relevant workspaces.

CI (Section 18 owns the workflow file), in order: pnpm install --frozen-lockfile -> lint -> typecheck -> test -> build -> test:e2e, failing fast on the first red step.

5. Engineering Conventions & Standards #

5.1 Naming conventions #

5.2 TypeScript rules #

tsconfig.base.json strict flags enabled (every package extends this, no per-package loosening):

{
  "compilerOptions": {
    "strict": true,
    "noUncheckedIndexedAccess": true,
    "exactOptionalPropertyTypes": true,
    "noImplicitOverride": true,
    "noFallthroughCasesInSwitch": true,
    "noPropertyAccessFromIndexSignature": true,
    "verbatimModuleSyntax": true,
    "isolatedModules": true
  }
}

No-any policy: any is banned by @typescript-eslint/no-explicit-any at error severity, with exactly two allowed escapes, both requiring an inline justification comment:

1. Third-party type gaps — an untyped/badly-typed dependency forcing a boundary cast uses unknown plus a Zod .parse() to re-establish a real type, never a bare any cast. If even that's impossible, // eslint-disable-next-line @typescript-eslint/no-explicit-any -- <library> ships no types for <X>, tracked in <issue> is the only accepted form, wrapping the smallest possible expression.
2. Generic test doubles — a mock/stub factory in tests/ whose signature must accept arbitrary shapes may use any only inside tests/**/*.ts (a relaxed ESLint override scoped by glob), never in src/.

Domain types from Zod, single source of truth: every domain object's TypeScript type is z.infer<typeof xSchema> — never a hand-written interface duplicating a schema. This guarantees the compile-time type and the runtime validator can never drift.

Branded ID types prevent passing a TopicId where a GoalId is expected, even though both are strings at runtime:

// packages/shared/src/schemas/branded.ts
import { z } from 'zod';

const brand = <B extends string>(b: B) =>
  z.string().uuid().brand<B>(b);

export const goalIdSchema = brand('GoalId');
export type GoalId = z.infer<typeof goalIdSchema>;

export const userIdSchema = brand('UserId');
export type UserId = z.infer<typeof userIdSchema>;

// usage: a function typed (goalId: GoalId) rejects a raw string or a TopicId at compile time;
// construct one only via goalIdSchema.parse(value) at a validated boundary (API input, DB row mapping).

5.3 The shared validation contract #

One Zod schema per domain object lives in packages/shared/src/schemas/, imported unchanged by apps/api (route validation), apps/worker (job payload validation), and apps/web (react-hook-form resolvers). No package re-declares a parallel shape.

Full worked example — the Goal entity (fields per the shared goal attributes in CONTEXT.md and the domain vocabulary in Section 3):

// packages/shared/src/schemas/goal.ts
import { z } from 'zod';
import { goalIdSchema, userIdSchema } from './branded';

export const wTagSchema = z.enum(['BE', 'DO', 'SEE', 'OWN', 'NO_MORE']);
export const prioritySchema = z.enum(['A', 'B', 'C']);
export const statusSchema = z.enum(['green', 'yellow', 'orange', 'red']);
export const goalLevelSchema = z.enum(['goal', 'main_goal', 'life_goal']);
export const lifeGoalStateSchema = z.enum(['candidate', 'confirmed']);
export const horizonKindSchema = z.enum(['relative_years', 'absolute_year', 'lifetime']);
export const contentLangSchema = z.enum(['de', 'en']);
// Origin enum (ai | human | ai_edited), CONTEXT.md vocabulary. NOT a per-field *Origin
// column below: Section 6's ai_suggestions is the ONLY origin ledger, keyed to the field it
// drafted. Exported for the ai_suggestions schema (Section 6/12), not used by goalSchema.
export const originSchema = z.enum(['ai', 'human', 'ai_edited']);
export const attainmentSourceSchema = z.enum(['manual', 'rollup']);

export const goalSchema = z.object({
  id: goalIdSchema,
  userId: userIdSchema,
  categoryId: z.string().uuid().nullable(),
  level: goalLevelSchema,
  lifeGoalState: lifeGoalStateSchema.nullable(),
  // Plaintext at API surface; encrypted at rest as `title_enc` (6); envelope
  // encrypt/decrypt (16, 4.5 step 8/12) is transparent — API never returns/accepts ciphertext.
  title: z.string().trim().min(1).max(200),
  // Required, may be empty string, never null (3.3) — progressive depth, field always exists.
  why: z.string().trim().max(4000),
  // Mirrors why's required-but-may-be-empty rule (principle #2: Why + vivid description +
  // image share primacy); same progressive-depth law applies.
  vividDescription: z.string().trim().max(4000),
  imageId: z.string().uuid().nullable(),
  wTag: wTagSchema,
  horizonKind: horizonKindSchema,
  horizonYears: z.union([z.literal(0), z.literal(1), z.literal(3), z.literal(5), z.literal(10)]).nullable(),
  horizonYear: z.number().int().min(1900).max(9999).nullable(),
  priority: prioritySchema,
  status: statusSchema,
  attainmentPercent: z.number().int().min(0).max(100),
  // Manual entry (traffic light/%, principle #3) or Section 6 rollup trigger (arithmetic in
  // 3.7). Also present on annual_goals, quarterly_goals, key_results (6).
  attainmentSource: attainmentSourceSchema,
  // Manual ordering (This Week reordering, Action Board column order) — Alt+Arrow and
  // dnd-kit both write this field.
  sortOrder: z.number().int(),
  contentLang: contentLangSchema,
  version: z.number().int().min(1),
  createdAt: z.string().datetime(),
  updatedAt: z.string().datetime(),
  deletedAt: z.string().datetime().nullable(),
}).refine(
  (g) => g.level === 'life_goal' || g.lifeGoalState === null,
  { message: 'lifeGoalState may only be set when level is life_goal', path: ['lifeGoalState'] },
).refine(
  (g) => g.horizonKind !== 'relative_years' || g.horizonYears !== null,
  { message: 'horizonYears is required when horizonKind is relative_years', path: ['horizonYears'] },
).refine(
  (g) => g.horizonKind !== 'absolute_year' || g.horizonYear !== null,
  { message: 'horizonYear is required when horizonKind is absolute_year', path: ['horizonYear'] },
);

export type Goal = z.infer<typeof goalSchema>;

// Creation input: server-assigned fields omitted, client supplies the rest
export const createGoalSchema = goalSchema.omit({
  id: true, userId: true, version: true, createdAt: true, updatedAt: true, deletedAt: true,
});
export type CreateGoalInput = z.infer<typeof createGoalSchema>;

// Update input: everything optional except nothing is required, id comes from the URL param
export const updateGoalSchema = createGoalSchema.partial();
export type UpdateGoalInput = z.infer<typeof updateGoalSchema>;

Consumption in the three layers:

• API (apps/api/src/routes/goals.ts): fastify-type-provider-zod binds updateGoalSchema as the route's body schema directly — no separate JSON Schema file.
• Worker (apps/worker/src/jobs/ai-job/reformulate-goal.job.ts): validates the job payload with goalSchema.pick({ title: true, why: true, contentLang: true }) before calling the AI adapter.
• Frontend (apps/web/src/features/goals/GoalForm.tsx): useForm({ resolver: zodResolver(updateGoalSchema) }) — the exact same schema drives client-side validation messages (localized via i18next, Section 15) with zero duplication.

5.4 Error handling #

Error class hierarchy (packages/shared/src/errors.ts, thrown in apps/api and apps/worker, never in apps/web):

export abstract class DomainError extends Error {
  abstract readonly code: string;       // SCREAMING_SNAKE_CASE, matches Section 7.4 envelope
  abstract readonly httpStatus: number;
  details?: { path: string; code: string; message: string }[];
}

export class ValidationError extends DomainError {
  readonly code = 'VALIDATION_FAILED';
  readonly httpStatus = 400;
}
export class NotFoundError extends DomainError {
  readonly code: string;                // e.g. 'GOAL_NOT_FOUND' (per-entity) or 'RESOURCE_NOT_FOUND' (generic, Section 7.9)
  readonly httpStatus = 404;
  constructor(entity: string, id: string, code?: string) {
    super(`${entity} ${id} not found`);
    this.code = code ?? `${entity.toUpperCase()}_NOT_FOUND`;
  }
  /** Generic form for any throw site with no entity-specific code yet — makes Section 7.9's
   *  RESOURCE_NOT_FOUND actually reachable. */
  static generic(id: string): NotFoundError {
    return new NotFoundError('resource', id, 'RESOURCE_NOT_FOUND');
  }
}
export class ConflictError extends DomainError {
  readonly code = 'VERSION_CONFLICT';
  readonly httpStatus = 412;
}
export class UnauthenticatedError extends DomainError {
  readonly code = 'UNAUTHENTICATED';
  readonly httpStatus = 401;
}
export class ForbiddenError extends DomainError {
  readonly code = 'FORBIDDEN';
  readonly httpStatus = 403;
}
export class RateLimitedError extends DomainError {
  readonly code = 'RATE_LIMITED';
  readonly httpStatus = 429;
}
export class ExternalProviderError extends DomainError {
  readonly code = 'AI_PROVIDER_ERROR';
  readonly httpStatus = 502;
}

Mapping rule: every route/job handler runs inside a try/catch owned by a Fastify setErrorHandler (API) / a BullMQ onFailed-aware wrapper (worker). A caught DomainError maps directly via its code/httpStatus/details into the canonical error envelope (7.4). Any non-DomainError (genuine bug — TypeError, driver error) is caught, logged at error with the full stack server-side, and converted to a generic { code: 'INTERNAL_ERROR', message: 'Something went wrong', httpStatus: 500 } — no raw exception message, stack trace, or driver error ever reaches the client. Enforced by a lint rule banning res.send(err)/returning a caught error object directly, plus an integration test asserting a deliberately-thrown non-domain error returns exactly the generic envelope.

5.5 Logging #

Structured JSON via pino, one log line per event, never multi-line/pretty-printed in production (pretty-printing is local-dev-only).

Exact field set on every log line:

Levels: trace — verbose step-by-step (disabled in production); debug — repro-useful decision points (disabled in production, per-request via a staging debug header); info — one line per completed request/job, production default; warn — handled DomainError with httpStatus >= 400 < 500; error — non-domain exception or httpStatus >= 500; fatal — process about to exit (uncaught top-level exception, used once in bootstrap).

Redaction rule (canonical, testable): a named deny-list configured as pino's redact option, applied to every logger instance app-wide — no logger is ever constructed without it:

// packages/shared/src/logging/redact-paths.ts
export const REDACT_PATHS = [
  // all user content fields, by field name, wherever they appear in a log object
  '*.title', '*.why', '*.eulogyText', '*.purposeText', '*.visionText', '*.missionText',
  '*.valueDescription', '*.principleText', '*.topicDescription', '*.idealState',
  '*.insightNote', '*.checkinNotes', '*.taskDescription', '*.aiPromptText', '*.aiResponseText',
  // AI provider keys, session tokens
  '*.apiKey', '*.providerKey', '*.sessionToken', '*.cookie', 'req.headers.cookie',
  'req.headers["x-csrf-token"]', 'req.headers.authorization',
  // email addresses beyond a salted hash — the raw address itself
  '*.email', '*.emailAddress',
];

Every logger (apps/api, apps/worker) is instantiated once via a shared factory applying REDACT_PATHS, so no call site can construct an unredacted logger. Tested: a CI test (packages/shared/tests/logging/redact-paths.test.ts) logs an object containing every REDACT_PATHS field with a sentinel and asserts it appears nowhere in the emitted JSON (only "[Redacted]" at that key). A second test asserts every Zod schema field flagged as sensitive (16) has a matching REDACT_PATHS entry — part of pnpm test, failing CI if a new sensitive field lacks a redaction path.

5.6 Git conventions #

• Branch naming: <type>/<short-kebab-description>, e.g. feat/wheel-radar-snapshot, fix/goal-version-conflict-race, chore/bump-drizzle. <type> matches the Conventional Commit type set below.
• Conventional Commits, enforced by a commit-msg hook (@commitlint/config-conventional via husky): <type>(<scope>): <description>, types limited to feat, fix, refactor, test, docs, chore, perf, build, ci. <scope> = package/module touched, e.g. feat(web/capture): add timer to capture screen.
• PR template (.github/PULL_REQUEST_TEMPLATE.md) requires four sections: What & why (one paragraph, links the Section 21 milestone item), How to verify (exact commands/steps), Screenshots/recording (required for any apps/web Section 14 surface change), Checklist (mirrors the Definition of Done, 5.7).
• Review rules: at least one approval before merge; author cannot approve own PR; CI (lint, typecheck, test, build) must be green (branch protection); squash-merge only, so main history is one commit per PR matching the PR title (a valid Conventional Commit subject).

5.7 Definition of Done #

A feature is Done only when every box below is literally true — an executing agent ticks this checklist before opening a PR for review:

• Shared Zod schema added/updated in packages/shared for every new/changed field; Goal-style derived types used, no hand-written parallel interface.
• API route validates request/response against the shared schema; canonical error envelope used for every error path (7.4); no raw exception reaches the client (5.4).
• DB migration written via drizzle-kit generate, includes a rollback note, RLS policy present if the table is user-content (6).
• Every sensitive content field is covered by the envelope-encryption list (16) and by REDACT_PATHS (5.5) if newly introduced.
• Frontend form (if any) uses react-hook-form + the same Zod schema via zodResolver — no parallel client-side validation logic.
• Both de-DE and en-US catalog entries added for every new UI string (15); no hard-coded user-facing string in component code.
• Keyboard navigable end to end; focus order verified; passes the axe-core check in the Playwright a11y suite (19); respects prefers-reduced-motion if animated (5.8).
• Empty, loading, and error states all designed and implemented — no surface ships with only the happy path (never-an-empty-page law).
• Unit tests for new service/job logic; integration test for the new/changed API route; component test for new interactive UI; colocated per naming convention (5.1).
• Structured log lines added at the appropriate level (5.5) for any new mutation or job.
• Performance budget check: bundle size and API p95 impact assessed against 5.9; no regression without an explicit, documented justification in the PR description.
• pnpm lint && pnpm typecheck && pnpm test pass locally; CI is green.
• PR "How to verify" section lets a reviewer reproduce the feature without asking the author a clarifying question.

5.8 Accessibility conventions (all code) #

Section 13 owns design-system-specific a11y (component ARIA patterns, focus rings, token contrast). This subsection is the baseline every line of code must honour:

• Focus management: every route transition moves focus to the new view's primary heading (tabIndex={-1} + .focus() on mount); every modal/dialog traps focus (Radix Dialog primitive, never hand-rolled) and returns focus to the trigger on close; focus never silently lost to <body>.
• Semantic landmarks: every screen has exactly one <main>, nav lives in <nav aria-label="...">, one <header> and, where relevant, one <aside> — verified by axe-core rule landmark-unique in the Playwright a11y suite.
• prefers-reduced-motion: every Motion transition reads a shared useReducedMotion() hook and swaps to instant/opacity-only when true — enforced by code review, checked by the Definition of Done (5.7).
• Colour contrast minimums: WCAG 2.2 AA floor (4.5:1 normal text, 3:1 large text/UI boundaries); packages/ui tokens (13) pre-validated in both themes; a Playwright a11y test asserts no color-contrast axe violation on every surface's default state.
• Keyboard-only rule: every interactive element operable via Tab/Shift+Tab, Enter/Space, and Arrow keys where implied (radio groups, Wheel slider, drag reordering — dnd-kit's keyboard sensor mandatory wherever its pointer sensor is used). No feature ships with a mouse/touch-drag-only interaction path — enforced by the Playwright a11y suite running each surface's primary flow keyboard-only as a first-class test.

5.9 Performance budgets #

5.10 Copywriting conventions for UI strings #

• Tone: calm, not corporate. No marketing superlatives ("amazing", "powerful"), no urgency language ("don't miss out", "act now") — the product's own principle (#6, slow answers allowed) forbids nagging copy by construction.

• Sentence case for all UI strings — headings, buttons, labels: "Add a key result", never "Add A Key Result" or "ADD A KEY RESULT".

• No exclamation marks, anywhere, including success toasts ("Saved." not "Saved!").

• No gamified language: no "streak", "level up", "badge earned", "you're on fire" — this mirrors the explicit NO badges/streaks/gamification interaction law in CONTEXT.md and extends it to copy.

• Second person, addressing the user directly: "Your purpose can stay blank" not "The user's purpose can stay blank" or the passive "Purpose may be left blank".

• German formal/informal address — DECISION: formal "Sie", not informal "Du". Rationale: eulogy/purpose/end-of-life content for an adult, single-user, private, serious register; German professional planning tools at this register default to "Sie" — "Du" reads as presumptuous for unsolicited eulogy/purpose prompts. FINAL; binds every German string in packages/i18n (15) and every German example anywhere in this spec.

  Enforcement is a BUILD-FAILING GATE, not advisory. Lint rule (packages/i18n/tests/validate-de-sie-form.test.ts) runs as part of pnpm test (CI pipeline 4.11, branch protection 5.6). Scans every de-DE catalog string, fails on any match of the informal-register regex:

  Copy

  /\b(du|dich|dir|dein|deine|deinem|deinen|deiner|deines)\b/giu

  Word-boundary (\b, avoids false positives on individuell/Kundin), case-insensitive (sentence-initial "Du" caught), Unicode-aware (umlaut word boundaries). A match blocks the PR like a failing typecheck.

  Sections 4–5 are pure engineering content with no UI copy, so nothing here needed conversion. Every German example elsewhere in this document (coach questions 12.5.1, reminder emails 11.2, eulogy prompts 9.2, AI failure copy 12.2.6, and all de-DE strings in Sections 7–14) has been converted to formal "Sie" against this same regex.

6. Data Model & Database Schema #

6.1 Conventions Applied in This Schema #

Canonical for every table/column/index/constraint/RLS policy; other sections reference this one by table name.

• PostgreSQL 17, snake_case, plural table names.
• id uuid PRIMARY KEY, app-supplied UUIDv7 (uuidv7 pkg, packages/shared) — no DB DEFAULT gen_random_uuid(). Column tables show id default —.
• Every user-owned table (incl. child/detail via parent join) carries user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE — uniform RLS/indexing, no join-time policy eval.
• SC: created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now() (trigger-maintained), version integer NOT NULL DEFAULT 1 (trigger-incremented; If-Match concurrency, Section 7). All tables carry SC.
• SC+T: SC + deleted_at timestamptz NULL, only on independently trashable/restorable content (§6.10). Child/detail rows w/o independent existence (readings, ratings, measures, links, checkin items, exercise steps, coach responses) skip deleted_at; hard-cascade, trashed/restored with parent.
• Enum-like values: text+CHECK, never ENUM — additive changes never rewrite a table.
• Encrypted columns: bytea, _enc suffix (e.g. why_enc), AES-256-GCM via user's DEK, KEK held outside DB (Section 16). Always NULL-able (Principle 6: honest-blank). Exception: title_enc (goals,tasks,priority_topics, §6.6) NOT NULL — title is mandatory structural content.
• DEK never password-derived. users.dek_wrapped wrapped by KMS KEK (Section 16), not password_hash — reset = zero data loss. Trade-off: forgoes zero-knowledge; KMS can unwrap DEK on any authenticated request (Section 16).
• Blind-index columns: text[] (GIN), _bi suffix, HMAC-SHA256 of normalized words, API-only computation (key never DB-resident) — DB compromise can't forge tokens (Section 16). Mechanism: §6.12.
• RLS ENABLE+FORCE-d on every user-content table. Roles: app_user (API, RLS-subject), app_service (BYPASSRLS; Trash-purge/migrations/metadata only). API sets app.current_user_id via SET LOCAL per transaction.
• Standard RLS policy, verbatim on every table owned by user_id:
  Copy

  ALTER TABLE <table> ENABLE ROW LEVEL SECURITY;
  ALTER TABLE <table> FORCE ROW LEVEL SECURITY;
  CREATE POLICY <table>_isolation ON <table>
    USING (user_id = current_setting('app.current_user_id')::uuid)
    WITH CHECK (user_id = current_setting('app.current_user_id')::uuid);

  share_grants (not user_id-owned) states its own policy inline.
• Standard "touch" trigger, every table, maintains updated_at/version:
  Copy

  CREATE FUNCTION set_updated_at() RETURNS trigger AS $$
  BEGIN
    NEW.updated_at := now();
    NEW.version := OLD.version + 1;
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  
  CREATE TRIGGER trg_<table>_touch BEFORE UPDATE ON <table>
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

  Defined once in migration 0001 (§6.7); each table adds only its own trigger line.
• "Exactly one of N" polymorphic-parent constraints use num_nonnulls(), not an untyped (type, id) pair — keeps every parent link a real, checkable FK.
• Money is never stored here (Stripe is ledger of record; billing_events.payload retains the raw event for reconciliation).

6.2 Entity-Relationship Diagram (Core Cluster) #

erDiagram
  users ||--o{ eulogy_speakers : has
  eulogy_speakers ||--o{ eulogies : "written for"
  eulogies ||--o{ values : "source of"
  eulogies ||--o{ goals : "source of"
  eulogies ||--o{ priority_topics : "source of"
  users ||--o{ purposes : has
  purposes ||--o{ purpose_exercises : has
  users ||--o{ visions : has
  visions ||--o{ vision_evidence : has
  users ||--o{ missions : has
  users ||--o{ values : has
  users ||--o{ principles : has
  users ||--o{ user_categories : has
  user_categories ||--o{ life_areas : activates
  life_areas ||--o{ priority_topics : classifies
  priority_topics ||--o{ topic_measures : has
  life_areas ||--o{ wheel_ratings : rated_in
  users ||--o{ wheel_snapshots : has
  wheel_snapshots ||--o{ wheel_ratings : contains
  users ||--o{ goals : has
  user_categories ||--o{ goals : tags
  goals ||--o{ goal_links : has
  priority_topics ||--o{ goal_links : "linked by"
  values ||--o{ goal_links : "linked by"
  goals ||--o{ goal_links : "parent of"
  goals ||--o{ goal_history : logs
  goals ||--o{ goal_status_changes : logs
  goals ||--o{ annual_goals : "planned as"
  annual_goals ||--o{ quarterly_goals : contains
  quarterly_goals ||--o{ key_activities : contains
  quarterly_goals ||--o{ key_results : contains
  key_results ||--o{ key_result_readings : has
  goals ||--o{ tasks : "may own"
  key_activities ||--o{ tasks : "may own"
  key_results ||--o{ tasks : "may own"
  users ||--o{ habits : has
  habits ||--o{ habit_entries : has
  users ||--o{ reviews : has
  reviews ||--o{ checkins : contains
  checkins ||--o{ checkin_items : contains
  goals ||--o{ checkin_items : "touched in"
  priority_topics ||--o{ checkin_items : "touched in"
  life_areas ||--o{ checkin_items : "touched in"
  users ||--o{ insights : has
  users ||--o{ reminders : has
  users ||--o{ images : owns
  goals ||--o{ coach_sessions : clarifies
  coach_sessions ||--o{ coach_responses : contains
  coach_questions ||--o{ coach_responses : answers
  users ||--o{ ai_credentials : has
  users ||--o{ ai_suggestions : receives
  ai_credentials ||--o{ ai_usage_log : incurs

6.3 The One-Goal-Table Decision #

Decision: exactly one table, goals, for every goal — plain goal, Main Goal, Life Goal are the same row shape, distinguished only by goals.level. Rationale: goals are promoted, not re-typed (module 6); separate tables would force migrating FKs (goal_links,tasks,annual_goals,checkin_items,goal_history, AI suggestions) each promotion/demotion — churn "demote C-goals cheaply" forbids.

W-questions (BE/DO/SEE/OWN/NO_MORE) are a capture tag, not a container: goals.w_tag is nullable text+CHECK, like priority/status — no be_goals tables, no FK "into a W-bucket". Written once at capture (module 5), pure metadata thereafter. "Not a Kanban board" is structurally enforced: only tasks.status maps to board columns (Action Board, Section 11), a task property, never goal.

life_goal_state (candidate|confirmed) exists only when level='life_goal' — CHECK ties the columns (§6.6.5) — "candidate" only applies once a goal is one of the 1-3 Life Goals.

6.4 Enum-Value Catalogue #

Domain enums fixed by shared context (Section 3 owns glossary); restated verbatim since they appear in CHECK constraints. Technical/system enums are schema-only.

6.5 Standard Patterns Reference #

Defined once here; table sections reference by name.

-- Migration 0001 — extensions, roles (see §6.7 for full list; set_updated_at() defined above in §6.1)
CREATE EXTENSION IF NOT EXISTS citext;
CREATE ROLE app_user LOGIN;
CREATE ROLE app_service LOGIN BYPASSRLS;

Each table below: full CREATE TABLE DDL (RLS, touch trigger), column table, indexes w/ query served, encryption note. Grouped by five-layer domain model plus system tables. The standard columns every table carries — id, user_id, created_at, updated_at, version (and rollup_version where noted) — are defined once in 6.1 and appear in each CREATE TABLE block; per-table column tables list only the table-specific columns. RLS marked "standard user-owned pattern" is the template in 6.3, keyed on user_id; tables deviating from it state their policy in full.

6.6 Tables #

6.6.1 Identity & Account #

users #

CREATE TABLE users (
  -- standard cols (6.1): id, created_at, updated_at, version
  email citext NOT NULL,
  email_verified_at timestamptz NULL,
  password_hash text NOT NULL,
  display_name text NULL,
  locale text NOT NULL DEFAULT 'de-DE' CHECK (locale IN ('de-DE','en-US')),
  status text NOT NULL DEFAULT 'pending_verification' CHECK (status IN
    ('pending_verification','active','suspended','pending_deletion','deleted')),
  ai_enabled boolean NOT NULL DEFAULT false,
  dek_wrapped bytea NOT NULL,
  blind_index_salt bytea NOT NULL,
  CONSTRAINT users_email_unique UNIQUE (email)
);
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE users FORCE ROW LEVEL SECURITY;
CREATE POLICY users_isolation ON users
  USING (id = current_setting('app.current_user_id')::uuid)
  WITH CHECK (id = current_setting('app.current_user_id')::uuid);
CREATE TRIGGER trg_users_touch BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Indexes: users_email_unique — login + signup collision check. RLS: self-row only. No deleted_at: modelled by status (pending_deletion grace window → deleted), not soft-delete — cascade root, so a second flag would compete. deleted ≠ physically removed: on deletion_requests.scheduled_for, a nightly job crypto-shreds (destroys dek_wrapped → all encrypted rows unreadable), scrubs email/display_name/password_hash, sets status = 'deleted' — never a hard DELETE (would cascade, wiping billing_events, which must survive statutory bookkeeping retention). Auth paths reject deleted like a nonexistent user. Encryption: none — dek_wrapped/blind_index_salt are key material, not DEK-encrypted content (§6.1).

user_settings #

CREATE TABLE user_settings (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  timezone text NOT NULL DEFAULT 'Europe/Berlin',
  week_starts_on smallint NOT NULL DEFAULT 1 CHECK (week_starts_on BETWEEN 0 AND 6),
  wheel_enabled boolean NOT NULL DEFAULT false,
  kpi_module_enabled boolean NOT NULL DEFAULT false,
  habits_enabled boolean NOT NULL DEFAULT false,
  print_locale text NOT NULL DEFAULT 'de-DE' CHECK (print_locale IN ('de-DE','en-US')),
  CONSTRAINT user_settings_user_unique UNIQUE (user_id)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: user_settings_user_unique — serves the 1:1 settings lookup on every request. RLS: standard. No deleted_at: configuration state, not content. Encryption: none.

sessions #

CREATE TABLE sessions (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  token_hash bytea NOT NULL,
  user_agent text NULL,
  ip_address inet NULL,
  last_seen_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL,
  revoked_at timestamptz NULL,
  CONSTRAINT sessions_token_hash_unique UNIQUE (token_hash)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: sessions_token_hash_unique — auth-middleware lookup; idx_sessions_user_active ON sessions(user_id) WHERE revoked_at IS NULL — active-sessions list. RLS: standard. No deleted_at: CANONICAL rule — hard-deleted (nightly job) once expires_at < now() or revoked_at < now() - interval '1 day'. Encryption: none — only the hash is stored.

totp_credentials #

CREATE TABLE totp_credentials (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  secret_enc bytea NOT NULL,
  confirmed_at timestamptz NULL,
  CONSTRAINT totp_credentials_user_unique UNIQUE (user_id)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: totp_credentials_user_unique. RLS: standard. No deleted_at: hard-deleted on 2FA removal. Encryption: secret_enc, envelope-encrypted with the user DEK like sensitive content — not a content-class field, but compromise defeats 2FA.

recovery_codes #

CREATE TABLE recovery_codes (
  -- standard cols (6.1): id, user_id, created_at, updated_at
  code_hash bytea NOT NULL,
  used_at timestamptz NULL,
  version integer NOT NULL DEFAULT 1
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_recovery_codes_user_unused ON recovery_codes(user_id) WHERE used_at IS NULL — remaining-codes count + redemption lookup. RLS: standard. No deleted_at: hard-deleted when TOTP disabled or codes regenerated. Encryption: none — hashed like passwords; no legitimate reason to recover plaintext.

email_verifications #

CREATE TABLE email_verifications (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  token_hash bytea NOT NULL,
  expires_at timestamptz NOT NULL,
  verified_at timestamptz NULL,
  CONSTRAINT email_verifications_token_hash_unique UNIQUE (token_hash)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: email_verifications_token_hash_unique — serves the verification-link handler. RLS: standard. No deleted_at: hard-deleted by a nightly job once expires_at has passed. Encryption: none.

password_resets #

CREATE TABLE password_resets (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  token_hash bytea NOT NULL,
  expires_at timestamptz NOT NULL,
  used_at timestamptz NULL,
  CONSTRAINT password_resets_token_hash_unique UNIQUE (token_hash)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: password_resets_token_hash_unique. RLS: standard. No deleted_at: hard-deleted by a nightly job once expired or used. Encryption: none.

email_change_tokens #

Not in Draft 1; added because Section 8's email-change flow needs to hold a proposed email pending confirmation, which email_verifications (keyed to the current email) cannot.

CREATE TABLE email_change_tokens (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  new_email citext NOT NULL,
  token_hash bytea NOT NULL,
  expires_at timestamptz NOT NULL,
  confirmed_at timestamptz NULL,
  CONSTRAINT email_change_tokens_token_hash_unique UNIQUE (token_hash)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: email_change_tokens_token_hash_unique — confirmation-link handler. RLS: standard. No deleted_at: hard-deleted once expired/confirmed, matching email_verifications/password_resets. Encryption: none — same sensitivity class as users.email, also plaintext, required for login/lookup.

onboarding_state #

Not in Draft 1; added so Module 0's guided, skippable, workbook-order walkthrough can persist step progress across sessions.

CREATE TABLE onboarding_state (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  current_step text NOT NULL DEFAULT 'welcome',
  step_status jsonb NOT NULL DEFAULT '{}',
  completed_at timestamptz NULL,
  CONSTRAINT onboarding_state_user_unique UNIQUE (user_id)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: onboarding_state_user_unique — 1:1 lookup, gates the onboarding shell. RLS: standard. No deleted_at: progress state, not content, matching user_settings. Encryption: none — fixed product vocabulary, not user content.

subscriptions #

CREATE TABLE subscriptions (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  stripe_customer_id text NOT NULL,
  stripe_subscription_id text NULL,
  plan text NULL CHECK (plan IN ('monthly','annual')),
  status text NOT NULL CHECK (status IN ('trialing','active','past_due','canceled','incomplete')),
  trial_ends_at timestamptz NULL,
  current_period_end timestamptz NULL,
  cancel_at_period_end boolean NOT NULL DEFAULT false,
  CONSTRAINT subscriptions_user_unique UNIQUE (user_id),
  CONSTRAINT subscriptions_stripe_customer_unique UNIQUE (stripe_customer_id),
  CONSTRAINT subscriptions_stripe_subscription_unique UNIQUE (stripe_subscription_id)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: the three unique constraints above serve billing-portal + webhook lookups. RLS: standard. No deleted_at: lifecycle fully modelled by status; cancelled subscriptions remain historical, never trashed. Encryption: none — Stripe Billing owns PAN/PCI scope.

billing_events #

CREATE TABLE billing_events (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  subscription_id uuid NULL REFERENCES subscriptions(id) ON DELETE SET NULL,
  stripe_event_id text NOT NULL,
  event_type text NOT NULL,
  payload jsonb NOT NULL,
  CONSTRAINT billing_events_stripe_event_unique UNIQUE (stripe_event_id)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: billing_events_stripe_event_unique — idempotent webhook processing; idx_billing_events_user_created ON billing_events(user_id, created_at DESC) — billing-history screen. RLS: standard. No deleted_at: immutable financial log, retained per statutory bookkeeping retention (Section 16). Encryption: none (no PAN/PCI data reaches this payload).

6.6.2 Categorization #

user_categories #

CREATE TABLE user_categories (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  category_type text NOT NULL CHECK (category_type IN ('goal','topic_workbook','topic_wheel')),
  preset_key text NULL,
  label_de text NOT NULL,
  label_en text NOT NULL,
  sort_order integer NOT NULL DEFAULT 0,
  is_active boolean NOT NULL DEFAULT true,
  deleted_at timestamptz NULL
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_user_categories_user_type ON user_categories(user_id, category_type) WHERE deleted_at IS NULL — category pickers; idx_user_categories_user_preset ON user_categories(user_id, preset_key) WHERE preset_key IS NOT NULL — signup seed-idempotency. RLS: standard. Trash: SC+T — "non-destructive": seeded workbook-11/wheel-8 rows are never hard-deleted by product action; only deactivate (is_active = false) or, if unused, soft-delete. Encryption: none — short, low-sensitivity, must render sortable.

life_areas #

CREATE TABLE life_areas (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  user_category_id uuid NOT NULL REFERENCES user_categories(id) ON DELETE CASCADE,
  sort_order integer NOT NULL DEFAULT 0,
  is_active boolean NOT NULL DEFAULT true,
  deleted_at timestamptz NULL,
  CONSTRAINT life_areas_user_category_unique UNIQUE (user_category_id)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Design note: thin activation/ordering layer over user_categories, not a redefinition — user_category holds the durable label; life_area is its Assessment-surface instance per user (order, active-flag only), letting a user deactivate an Assessment area without touching the category other modules (e.g. goal tagging) still use — no duplicate label_de/label_en.

Indexes: life_areas_user_category_unique — one activation row per category; idx_life_areas_user_active ON life_areas(user_id, sort_order) WHERE deleted_at IS NULL AND is_active = true — Wheel radar + Priority Topics list. RLS: standard. Trash: SC+T. Encryption: none.

6.6.3 Foundation "Why" #

eulogy_speakers #

CREATE TABLE eulogy_speakers (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  speaker_key text NULL,
  label_de text NOT NULL,
  label_en text NOT NULL,
  sort_order integer NOT NULL DEFAULT 0,
  is_active boolean NOT NULL DEFAULT true,
  deleted_at timestamptz NULL
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_eulogy_speakers_user_active ON eulogy_speakers(user_id, sort_order) WHERE deleted_at IS NULL — module 1 speaker list. RLS: standard. Trash: SC+T. Encryption: none (role labels only; no eulogy content here).

eulogies #

CREATE TABLE eulogies (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  speaker_id uuid NOT NULL REFERENCES eulogy_speakers(id) ON DELETE CASCADE,
  content_enc bytea NULL,
  status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','complete')),
  written_on date NOT NULL DEFAULT current_date,
  content_lang text NOT NULL DEFAULT 'de' CHECK (content_lang IN ('de','en')),
  image_id uuid NULL,
  deleted_at timestamptz NULL
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_eulogies_user_speaker ON eulogies(user_id, speaker_id, written_on DESC) WHERE deleted_at IS NULL — per-speaker history. RLS: standard. Trash: SC+T. Encryption: content_enc (class eulogy). No blind index, matching purposes below: a prior content_bi was dropped — low row count (≤8/speaker set, §6.9) makes decrypt-filter cheap, and indexing the product's most sensitive field isn't worth the leakage cost (§6.12); fixes a prior inconsistency where only eulogies was blind-indexed.

purposes #

CREATE TABLE purposes (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  method text NOT NULL CHECK (method IN ('ikigai','venn','stepwise','freeform')),
  statement_enc bytea NULL,
  status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','active','archived')),
  content_lang text NOT NULL DEFAULT 'de' CHECK (content_lang IN ('de','en')),
  image_id uuid NULL,
  deleted_at timestamptz NULL
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_purposes_user_status ON purposes(user_id, status) WHERE deleted_at IS NULL — Home purpose display + list. RLS: standard. Trash: SC+T. Encryption: statement_enc (class purpose). No blind index: count/user is small (1-5 lifetime, §6.9), decrypt-then-filter cheap, overhead unjustified on this most-sensitive field.

purpose_exercises #

CREATE TABLE purpose_exercises (
  -- standard cols (6.1): id, user_id, created_at, updated_at
  purpose_id uuid NOT NULL REFERENCES purposes(id) ON DELETE CASCADE,
  exercise_type text NOT NULL CHECK (exercise_type IN ('ikigai','venn','stepwise')),
  step_key text NOT NULL,
  content_enc bytea NULL,
  sort_order integer NOT NULL DEFAULT 0,
  version integer NOT NULL DEFAULT 1
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_purpose_exercises_purpose_step ON purpose_exercises(purpose_id, step_key, sort_order) — renders one step in order. RLS: standard. Trash: none — child of purposes, cascades hard, implicitly trashed/restored with parent (§6.1, §6.10). Encryption: content_enc (class purpose).

visions #

CREATE TABLE visions (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  target_year integer NULL,
  statement_enc bytea NULL,
  why_enc bytea NULL,
  attainment_percent integer NOT NULL DEFAULT 0 CHECK (attainment_percent BETWEEN 0 AND 100),
  status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','active','archived')),
  content_lang text NOT NULL DEFAULT 'de' CHECK (content_lang IN ('de','en')),
  image_id uuid NULL,
  deleted_at timestamptz NULL
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_visions_user_status ON visions(user_id, status) WHERE deleted_at IS NULL — Home vision image/statement display. RLS: standard. Trash: SC+T. Encryption: statement_enc, why_enc (class vision). No blind index — low row count (§6.9).

vision_evidence #

CREATE TABLE vision_evidence (
  -- standard cols (6.1): id, user_id, created_at, updated_at
  vision_id uuid NOT NULL REFERENCES visions(id) ON DELETE CASCADE,
  description_enc bytea NULL,
  image_id uuid NULL,
  occurred_on date NULL,
  version integer NOT NULL DEFAULT 1
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_vision_evidence_vision ON vision_evidence(vision_id, occurred_on DESC) — vision detail timeline. RLS: standard. Trash: none — child of visions, cascades hard, implicit with parent. Encryption: description_enc (class vision).

missions #

CREATE TABLE missions (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  statement_enc bytea NULL,
  status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','active','archived')),
  content_lang text NOT NULL DEFAULT 'de' CHECK (content_lang IN ('de','en')),
  deleted_at timestamptz NULL
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_missions_user_status ON missions(user_id, status) WHERE deleted_at IS NULL. RLS: standard. Trash: SC+T. Encryption: statement_enc (class mission).

values #

CREATE TABLE "values" (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  name text NOT NULL,
  description_enc bytea NULL,
  value_kind text NOT NULL DEFAULT 'core' CHECK (value_kind IN ('core','ethical')),
  source_eulogy_id uuid NULL REFERENCES eulogies(id) ON DELETE SET NULL,
  sort_order integer NOT NULL DEFAULT 0,
  deleted_at timestamptz NULL
);
ALTER TABLE "values" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "values" FORCE ROW LEVEL SECURITY;
CREATE POLICY values_isolation ON "values"
  USING (user_id = current_setting('app.current_user_id')::uuid)
  WITH CHECK (user_id = current_setting('app.current_user_id')::uuid);
CREATE TRIGGER trg_values_touch BEFORE UPDATE ON "values"
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Naming decision: VALUES is a reserved SQL keyword; rather than rename the table (brief and glossary both say "Values"), the schema keeps the name, always double-quoted — "values" — in DDL. Drizzle Kit auto-quotes reserved identifiers, so it's a runtime non-issue; noted so nobody hand-writes raw SQL against it unquoted.

Indexes: idx_values_user_kind ON "values"(user_id, value_kind, sort_order) WHERE deleted_at IS NULL — values band + goal-vs-value picker; idx_values_source_eulogy ON "values"(source_eulogy_id) WHERE source_eulogy_id IS NOT NULL — lineage walks from a eulogy (Section 9). RLS: standard. Trash: SC+T. Encryption: description_enc (class value); name plaintext by decision — less exposure than the description, renders instantly in the values band without a decrypt round-trip.

Completeness, kept out of the schema: description_enc was originally NOT NULL, contradicting the always-nullable rule for encrypted columns (§6.1) and blocking name-only saves. "Complete" (name and description present) is computed application-side from description_enc IS NOT NULL, not persisted as a DB flag — no status/is_complete column, matching the identical fix in principles and topic_measures (§6.6.4).

principles #

CREATE TABLE principles (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  title text NOT NULL,
  description_enc bytea NULL,
  sort_order integer NOT NULL DEFAULT 0,
  deleted_at timestamptz NULL
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_principles_user_sort ON principles(user_id, sort_order) WHERE deleted_at IS NULL. RLS: standard. Trash: SC+T. Encryption: description_enc (class principle); title plaintext, same reason as values.name. Completeness (title + description present) computed application-side, not persisted — see values note above.

6.6.4 Assessment "Where" #

priority_topics #

CREATE TABLE priority_topics (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  life_area_id uuid NOT NULL REFERENCES life_areas(id) ON DELETE RESTRICT,
  title_enc bytea NOT NULL,
  title_bi text[] NULL,
  description_enc bytea NULL,
  description_bi text[] NULL,
  ideal_state_enc bytea NULL,
  why_enc bytea NULL,
  source_eulogy_id uuid NULL REFERENCES eulogies(id) ON DELETE SET NULL,
  status text NOT NULL DEFAULT 'yellow' CHECK (status IN ('green','yellow','orange','red')),
  content_lang text NOT NULL DEFAULT 'de' CHECK (content_lang IN ('de','en')),
  image_id uuid NULL,
  sort_order integer NOT NULL DEFAULT 0,
  deleted_at timestamptz NULL
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_priority_topics_user_area(user_id, life_area_id, sort_order) WHERE deleted_at IS NULL; idx_priority_topics_description_bi GIN(description_bi) (§6.9); idx_priority_topics_title_bi GIN(title_bi) (§6.12); idx_priority_topics_source_eulogy(source_eulogy_id) WHERE NOT NULL. RLS/Trash: standard/SC+T. Encryption: title_enc/description_enc/ideal_state_enc/why_enc (class topic); BI title_bi/description_bi.

topic_measures #

CREATE TABLE topic_measures (
  -- standard cols (6.1): id, user_id, created_at, updated_at
  topic_id uuid NOT NULL REFERENCES priority_topics(id) ON DELETE CASCADE,
  description_enc bytea NULL,
  measure_type text NOT NULL CHECK (measure_type IN ('numeric','percent','boolean','text')),
  target_value numeric NULL,
  current_value numeric NULL,
  unit text NULL,
  sort_order integer NOT NULL DEFAULT 0,
  version integer NOT NULL DEFAULT 1
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_topic_measures_topic(topic_id, sort_order). RLS/Trash: standard/none. Encryption: description_enc (class topic). Completeness computed app-side (§6.6.3).

wheel_snapshots #

CREATE TABLE wheel_snapshots (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  snapshot_date date NOT NULL DEFAULT current_date,
  note_enc bytea NULL,
  deleted_at timestamptz NULL
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_wheel_snapshots_user_date(user_id, snapshot_date DESC) WHERE deleted_at IS NULL. RLS/Trash: standard/SC+T. Encryption: note_enc (class topic).

wheel_ratings #

CREATE TABLE wheel_ratings (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  snapshot_id uuid NOT NULL REFERENCES wheel_snapshots(id) ON DELETE CASCADE,
  life_area_id uuid NOT NULL REFERENCES life_areas(id) ON DELETE RESTRICT,
  rating smallint NOT NULL CHECK (rating BETWEEN 1 AND 10),
  is_keystone boolean NOT NULL DEFAULT false,
  micro_goal_enc bytea NULL,
  metric_90_day_enc bytea NULL,
  CONSTRAINT wheel_ratings_snapshot_area_unique UNIQUE (snapshot_id, life_area_id)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: wheel_ratings_snapshot_area_unique (radar chart); idx_wheel_ratings_area_trend(user_id, life_area_id, created_at) (§12). RLS/Trash: standard/none. Encryption: micro_goal_enc/metric_90_day_enc (class topic).

kpi_groups #

CREATE TABLE kpi_groups (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  period_label text NOT NULL,
  period_start date NOT NULL,
  period_end date NOT NULL,
  sort_order integer NOT NULL DEFAULT 0,
  deleted_at timestamptz NULL,
  CONSTRAINT kpi_groups_period_check CHECK (period_end > period_start)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_kpi_groups_user_period(user_id, period_start DESC) WHERE deleted_at IS NULL. RLS/Trash: standard/SC+T. Encryption: none (KPI is the numeric alternative to narrative topics).

kpi_sets #

CREATE TABLE kpi_sets (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  kpi_group_id uuid NOT NULL REFERENCES kpi_groups(id) ON DELETE CASCADE,
  title text NOT NULL,
  sort_order integer NOT NULL DEFAULT 0,
  deleted_at timestamptz NULL
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_kpi_sets_group(kpi_group_id, sort_order) WHERE deleted_at IS NULL. RLS/Trash: standard/SC+T. Encryption: none.

kpis #

CREATE TABLE kpis (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  kpi_set_id uuid NOT NULL REFERENCES kpi_sets(id) ON DELETE CASCADE,
  life_area_id uuid NULL REFERENCES life_areas(id) ON DELETE SET NULL,
  title text NOT NULL,
  target_percent smallint NOT NULL CHECK (target_percent BETWEEN 0 AND 100),
  current_percent smallint NOT NULL DEFAULT 0 CHECK (current_percent BETWEEN 0 AND 100),
  status text NOT NULL DEFAULT 'yellow' CHECK (status IN ('green','yellow','orange','red')),
  sort_order integer NOT NULL DEFAULT 0,
  deleted_at timestamptz NULL
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_kpis_set(kpi_set_id, sort_order) WHERE deleted_at IS NULL. RLS/Trash: standard/SC+T. Encryption: none.

kpi_readings #

CREATE TABLE kpi_readings (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  kpi_id uuid NOT NULL REFERENCES kpis(id) ON DELETE CASCADE,
  reading_date date NOT NULL,
  value_percent smallint NOT NULL CHECK (value_percent BETWEEN 0 AND 100),
  note text NULL,
  CONSTRAINT kpi_readings_kpi_date_unique UNIQUE (kpi_id, reading_date)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: kpi_readings_kpi_date_unique (one-reading-per-day + trend chart). RLS/Trash: standard/none. Encryption: none (note is operational, not a sensitive class).

6.6.5 Goals "What" #

goals #

The canonical goal table (§6.3) — every goal, Main Goal, Life Goal is a row here.

CREATE TABLE goals (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  level text NOT NULL DEFAULT 'goal' CHECK (level IN ('goal','main_goal','life_goal')),
  life_goal_state text NULL CHECK (life_goal_state IN ('candidate','confirmed')),
  w_tag text NOT NULL CHECK (w_tag IN ('BE','DO','SEE','OWN','NO_MORE')),
  category_id uuid NULL REFERENCES user_categories(id) ON DELETE SET NULL,
  horizon_kind text NOT NULL CHECK (horizon_kind IN ('relative_years','absolute_year','lifetime')),
  horizon_years integer NULL,
  horizon_year integer NULL,
  priority text NOT NULL DEFAULT 'B' CHECK (priority IN ('A','B','C')),
  status text NOT NULL DEFAULT 'yellow' CHECK (status IN ('green','yellow','orange','red')),
  attainment_percent integer NOT NULL DEFAULT 0 CHECK (attainment_percent BETWEEN 0 AND 100),
  attainment_source text NOT NULL DEFAULT 'manual' CHECK (attainment_source IN ('manual','rollup')),
  rollup_version integer NOT NULL DEFAULT 1,
  title_enc bytea NOT NULL,
  title_bi text[] NULL,
  source_eulogy_id uuid NULL REFERENCES eulogies(id) ON DELETE SET NULL,
  why_enc bytea NULL,
  why_bi text[] NULL,
  vivid_description_enc bytea NULL,
  content_lang text NOT NULL DEFAULT 'de' CHECK (content_lang IN ('de','en')),
  image_id uuid NULL,
  sort_order integer NOT NULL DEFAULT 0,
  deleted_at timestamptz NULL,
  CONSTRAINT goals_life_goal_state_check CHECK (
    (level = 'life_goal' AND life_goal_state IS NOT NULL) OR
    (level <> 'life_goal' AND life_goal_state IS NULL)
  ),
  CONSTRAINT goals_horizon_check CHECK (
    (horizon_kind = 'relative_years' AND horizon_years IS NOT NULL AND horizon_year IS NULL
      AND horizon_years IN (0,1,3,5,10)) OR
    (horizon_kind = 'absolute_year' AND horizon_year IS NOT NULL AND horizon_years IS NULL) OR
    (horizon_kind = 'lifetime' AND horizon_years IS NULL AND horizon_year IS NULL)
  )
);
-- RLS: standard user-owned pattern (6.3); touch trigger set_updated_at_rollup_aware()

DECISION — rollup-aware touch, not standard. goals is one of four tables (with annual_goals, quarterly_goals, key_results) system-recomputed by §6.11; set_updated_at_rollup_aware() bumps rollup_version, not version, on rollup writes — recompute never causes a spurious 412 on If-Match.

Indexes (all WHERE deleted_at IS NULL unless noted): idx_goals_user_level(user_id, level); idx_goals_user_horizon(user_id, horizon_kind, horizon_years); idx_goals_user_status(user_id, status); idx_goals_user_wtag(user_id, w_tag); idx_goals_user_category(user_id, category_id); idx_goals_why_bi/idx_goals_title_bi GIN (§6.12); idx_goals_source_eulogy(source_eulogy_id) WHERE NOT NULL (§9).

RLS: standard. Trash: SC+T; see §6.10 for child (annual_goals, tasks, goal_links) behavior.

Encryption: title_enc/why_enc/vivid_description_enc (class goal). BI title_bi/why_bi cover searched fields; vivid_description_enc via decrypt-then-filter, always fetched together (§6.12).

goal_links #

CREATE TABLE goal_links (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  goal_id uuid NOT NULL REFERENCES goals(id) ON DELETE CASCADE,
  link_type text NOT NULL CHECK (link_type IN ('supports_topic','serves_value','parent_of')),
  topic_id uuid NULL REFERENCES priority_topics(id) ON DELETE CASCADE,
  value_id uuid NULL REFERENCES "values"(id) ON DELETE CASCADE,
  parent_goal_id uuid NULL REFERENCES goals(id) ON DELETE CASCADE,
  CONSTRAINT goal_links_exactly_one CHECK (num_nonnulls(topic_id, value_id, parent_goal_id) = 1),
  CONSTRAINT goal_links_type_match CHECK (
    (link_type = 'supports_topic' AND topic_id IS NOT NULL) OR
    (link_type = 'serves_value' AND value_id IS NOT NULL) OR
    (link_type = 'parent_of' AND parent_goal_id IS NOT NULL)
  ),
  CONSTRAINT goal_links_no_self_parent CHECK (goal_id <> parent_goal_id),
  CONSTRAINT goal_links_unique UNIQUE (goal_id, link_type, topic_id, value_id, parent_goal_id)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_goal_links_goal(goal_id); idx_goal_links_parent(parent_goal_id) WHERE NOT NULL; idx_goal_links_topic(topic_id) WHERE NOT NULL; idx_goal_links_value(value_id) WHERE NOT NULL (§12). RLS: standard. Trash: none, owned by goal_id; cascades if linked row hard-deleted. Encryption: none, only FKs and a type tag.

goal_history #

CREATE TABLE goal_history (
  -- standard cols (6.1): id, user_id
  goal_id uuid NOT NULL REFERENCES goals(id) ON DELETE CASCADE,
  changed_by uuid NULL REFERENCES users(id) ON DELETE SET NULL,
  change_type text NOT NULL CHECK (change_type IN ('status_change','attainment_change','promotion','demotion','level_change')),
  field_name text NOT NULL,
  old_value text NULL,
  new_value text NULL,
  note text NULL,
  changed_at timestamptz NOT NULL DEFAULT now()
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_goal_history_goal_changed(goal_id, changed_at DESC). RLS: standard. No deleted_at/updated_at/version/touch trigger: append-only (CANONICAL: logs hard-deleted only, never soft — only via cascade at the parent's 30-day Trash purge). Encryption: none — only plaintext columns (status,attainment_percent,level,priority); an encrypted-field change never diffs to cleartext.

DECISION — rollup changes deliberately NOT logged here. goal_history backs the history panel: user-driven events only (status/promotion/demotion/level changes, manual attainment edits while attainment_source='manual'). §6.11's triggers recompute attainment_percent/rollup_version on every reading write without inserting here — every tick would flood the panel for a value the progress ring already shows live. goal_status_changes gives a coach grantee a scoped, narrative-free view; goal_history stays owner-only since note carries free-text never meant for a grantee.

goal_status_changes #

DECISION. Not in Draft 1. Added: §17's coach share role needs a history source structurally incapable of the free-text note a check-in attaches to goal_history — a coach sees that status changed and when, never the owner's private annotation why.

CREATE TABLE goal_status_changes (
  -- standard cols (6.1): id, user_id
  goal_id uuid NOT NULL REFERENCES goals(id) ON DELETE CASCADE,
  change_type text NOT NULL CHECK (change_type IN ('status_change','attainment_change','promotion','demotion','level_change')),
  field_name text NOT NULL,
  old_value text NULL,
  new_value text NULL,
  changed_at timestamptz NOT NULL DEFAULT now()
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Relationship to goal_history: every goal_history insert also inserts the identical tuple here, same event, two trust levels. goal_history adds changed_by/note, owner-only; this table carries neither and is what a coach-role share_grants row (§6.6.12) may expose. Intentional duplication, same transaction.

Indexes: idx_goal_status_changes_goal(goal_id, changed_at DESC). RLS: owner-only, coach access via API grant-check (§17), as share_grants (§6.6.12). No deleted_at/updated_at/version/touch trigger: lifecycle as goal_history. Encryption: none.

6.6.6 Execution "How" #

annual_goals #

CREATE TABLE annual_goals (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  goal_id uuid NOT NULL REFERENCES goals(id) ON DELETE CASCADE,
  year integer NOT NULL,
  why_this_year_enc bytea NULL,
  status text NOT NULL DEFAULT 'yellow' CHECK (status IN ('green','yellow','orange','red')),
  attainment_percent integer NOT NULL DEFAULT 0 CHECK (attainment_percent BETWEEN 0 AND 100),
  attainment_source text NOT NULL DEFAULT 'manual' CHECK (attainment_source IN ('manual','rollup')),
  rollup_version integer NOT NULL DEFAULT 1,
  deleted_at timestamptz NULL,
  CONSTRAINT annual_goals_goal_year_unique UNIQUE (goal_id, year)
);
-- RLS: standard user-owned pattern (6.3); touch trigger set_updated_at_rollup_aware()

Indexes: annual_goals_goal_year_unique (uniqueness + year lookup); idx_annual_goals_user_year ON annual_goals(user_id, year) WHERE deleted_at IS NULL (annual planning screen). RLS: standard. Trash: SC+T. Encryption: why_this_year_enc (class goal). trg_annual_goals_touch uses the rollup-aware touch function — see goals (§6.6.5) and §6.11.

quarterly_goals #

CREATE TABLE quarterly_goals (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  annual_goal_id uuid NOT NULL REFERENCES annual_goals(id) ON DELETE CASCADE,
  quarter smallint NOT NULL CHECK (quarter BETWEEN 1 AND 4),
  status text NOT NULL DEFAULT 'yellow' CHECK (status IN ('green','yellow','orange','red')),
  attainment_percent integer NOT NULL DEFAULT 0 CHECK (attainment_percent BETWEEN 0 AND 100),
  attainment_source text NOT NULL DEFAULT 'manual' CHECK (attainment_source IN ('manual','rollup')),
  rollup_version integer NOT NULL DEFAULT 1,
  deleted_at timestamptz NULL,
  CONSTRAINT quarterly_goals_annual_quarter_unique UNIQUE (annual_goal_id, quarter)
);
-- RLS: standard user-owned pattern (6.3); touch trigger set_updated_at_rollup_aware()

Indexes: quarterly_goals_annual_quarter_unique; idx_quarterly_goals_user_quarter ON quarterly_goals(user_id, annual_goal_id, quarter) WHERE deleted_at IS NULL. RLS: standard. Trash: SC+T. Encryption: none — quarterly goals carry no narrative field of their own (the "why" lives on the parent goal/annual_goal). trg_quarterly_goals_touch uses the rollup-aware touch function — see goals (§6.6.5) and §6.11.

key_activities #

CREATE TABLE key_activities (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  quarterly_goal_id uuid NOT NULL REFERENCES quarterly_goals(id) ON DELETE CASCADE,
  title text NOT NULL,
  description text NULL,
  status text NOT NULL DEFAULT 'planned' CHECK (status IN ('planned','in_progress','done')),
  due_date date NULL,
  sort_order integer NOT NULL DEFAULT 0,
  deleted_at timestamptz NULL
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_key_activities_quarter ON key_activities(quarterly_goal_id, sort_order) WHERE deleted_at IS NULL. RLS: standard. Trash: SC+T. Encryption: none (see description rationale above).

key_results #

CREATE TABLE key_results (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  quarterly_goal_id uuid NOT NULL REFERENCES quarterly_goals(id) ON DELETE CASCADE,
  title text NOT NULL,
  kr_kind text NOT NULL DEFAULT 'improve' CHECK (kr_kind IN ('improve','maintain')),
  target_value numeric NOT NULL,
  baseline_value numeric NOT NULL DEFAULT 0,
  current_value numeric NOT NULL DEFAULT 0,
  tolerance numeric NULL,
  unit text NULL,
  weight_percent integer NOT NULL DEFAULT 100 CHECK (weight_percent > 0),
  attainment_percent integer NOT NULL DEFAULT 0 CHECK (attainment_percent BETWEEN 0 AND 100),
  attainment_source text NOT NULL DEFAULT 'manual' CHECK (attainment_source IN ('manual','rollup')),
  rollup_version integer NOT NULL DEFAULT 1,
  status text NOT NULL DEFAULT 'yellow' CHECK (status IN ('green','yellow','orange','red')),
  sort_order integer NOT NULL DEFAULT 0,
  deleted_at timestamptz NULL,
  CONSTRAINT key_results_maintain_tolerance CHECK (
    (kr_kind = 'maintain' AND tolerance IS NOT NULL) OR
    (kr_kind = 'improve' AND tolerance IS NULL)
  ),
  CONSTRAINT key_results_improve_not_flat CHECK (
    kr_kind = 'maintain' OR target_value <> baseline_value
  )
);
-- RLS: standard user-owned pattern (6.3); touch trigger set_updated_at_rollup_aware()

Indexes: idx_key_results_quarter ON key_results(quarterly_goal_id, sort_order) WHERE deleted_at IS NULL. RLS: standard. Trash: SC+T. Encryption: none — not narrative content (the "why" lives on goals). trg_key_results_touch uses the rollup-aware touch function — see goals (§6.6.5) and §6.11.

key_result_readings #

CREATE TABLE key_result_readings (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  key_result_id uuid NOT NULL REFERENCES key_results(id) ON DELETE CASCADE,
  reading_date date NOT NULL,
  value numeric NOT NULL,
  note text NULL,
  CONSTRAINT key_result_readings_kr_date_unique UNIQUE (key_result_id, reading_date)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: key_result_readings_kr_date_unique (one-per-day rule + trend chart). RLS: standard. Trash: none — child of key_results, cascades hard with parent. Encryption: none.

tasks #

CREATE TABLE tasks (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  goal_id uuid NULL REFERENCES goals(id) ON DELETE CASCADE,
  key_activity_id uuid NULL REFERENCES key_activities(id) ON DELETE CASCADE,
  key_result_id uuid NULL REFERENCES key_results(id) ON DELETE CASCADE,
  origin_checkin_item_id uuid NULL REFERENCES checkin_items(id) ON DELETE SET NULL,
  title_enc bytea NOT NULL,
  title_bi text[] NULL,
  notes_enc bytea NULL,
  due_date date NULL,
  this_week boolean NOT NULL DEFAULT false,
  status text NOT NULL DEFAULT 'open' CHECK (status IN ('open','doing','done')),
  completed_at timestamptz NULL,
  sort_order integer NOT NULL DEFAULT 0,
  deleted_at timestamptz NULL,
  CONSTRAINT tasks_exactly_one_parent CHECK (num_nonnulls(goal_id, key_activity_id, key_result_id) = 1)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_tasks_user_this_week ON tasks(user_id, due_date) WHERE deleted_at IS NULL AND this_week = true; idx_tasks_user_status ON tasks(user_id, status) WHERE deleted_at IS NULL; idx_tasks_goal ON tasks(goal_id) WHERE goal_id IS NOT NULL AND deleted_at IS NULL, idx_tasks_key_activity ON tasks(key_activity_id) WHERE key_activity_id IS NOT NULL AND deleted_at IS NULL, idx_tasks_key_result ON tasks(key_result_id) WHERE key_result_id IS NOT NULL AND deleted_at IS NULL (ancestry per attachment point); idx_tasks_title_bi GIN ON tasks USING gin(title_bi).

RLS: standard. Trash: SC+T. Encryption: title_enc, notes_enc (class task); no plaintext title. Decrypted titles render per row on "This week"/Action Board, cheap because both are bounded, partial-index-backed working sets (this_week = true or one status at a time; §6.9 caps active tasks at a small fraction of the 3,000-8,000 five-year total) — not the unbounded-list case a blind index/trigram scan exists to protect elsewhere.

Migration-order note: tasks.origin_checkin_item_id references checkin_items (§6.6.7), created in an earlier migration than tasks (§6.7) — reviews/checkins/checkin_items migrate in 0016, tasks in 0018, so the forward reference always resolves.

6.6.7 Rhythm "Alive" #

reviews #

CREATE TABLE reviews (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  review_type text NOT NULL CHECK (review_type IN ('weekly','quarterly','annual')),
  period_start date NOT NULL,
  period_end date NOT NULL,
  status text NOT NULL DEFAULT 'in_progress' CHECK (status IN ('in_progress','completed')),
  summary_enc bytea NULL,
  started_at timestamptz NOT NULL DEFAULT now(),
  completed_at timestamptz NULL,
  deleted_at timestamptz NULL,
  CONSTRAINT reviews_period_check CHECK (period_end >= period_start)
);
-- RLS + touch trigger: standard user-owned pattern (6.3)

Indexes: idx_reviews_user_type_period ON reviews(user_id, review_type, period_start DESC) WHERE deleted_at IS NULL (last N weekly reviews). RLS: standard. Trash: SC+T. Encryption: summary_enc — classified under checkin (reviews are the containing session; no separate review sensitive class exists in the CANONICAL list, and a summary is materially the same reflective content as a check-in note).

checkins #

CREATE TABLE checkins (
  -- standard cols (6.1): id, user_id, created_at, updated_at, version
  review_id uuid NULL REFERENCES reviews(id) ON DELETE SET NULL,
  checkin_type text NOT NULL CHECK (checkin_type IN ('weekly','goal','topic','wheel')),
  status text NOT NULL DEFAULT 'in_progress' CHECK (status IN ('in_progress','completed')),
  item_count integer NOT NULL DEFAULT 0,
  started_at timestamptz NOT NULL DEFAULT now(),
  completed_at timestamptz NULL,
  deleted_at timestamptz NULL
);
-- RLS + touch trigger: standard user-owned pattern (6.3)