life-docs/schema.md

Life Management System — Database Schema

Overview

PostgreSQL 16 on port 25470. Database name: life_manager. All entities use UUID primary keys and timestamptz timestamps via BaseEntity from @lilith/typeorm-entities. Sensitive fields encrypted at rest via pgcrypto extension using @lilith/typeorm-pgcrypto.

Extensions

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

Entity Hierarchy

BaseEntity (abstract)
├── id: UUID (auto-generated)
├── createdAt: timestamptz
└── updatedAt: timestamptz

SoftDeletableEntity extends BaseEntity (abstract)
└── deletedAt: timestamptz | null

All entities extend BaseEntity. Entities with soft delete support extend SoftDeletableEntity (Task, Contact).

---

Core Entities

domains

Life domains — the top-level organizational unit for everything in the system.

Column	Type	Constraints	Description
id	uuid	PK, default uuid_generate_v4()
name	varchar(100)	NOT NULL	Display name
type	varchar(30)	NOT NULL	Enum: client_work, software, side_project, life_admin, modeling, escort, content, physical
slug	varchar(50)	UNIQUE, NOT NULL	URL-safe identifier
color	varchar(7)	NOT NULL	Hex color code (e.g., #4A90D9)
icon	varchar(30)	NOT NULL	Icon identifier
description	text		Optional description
is_active	boolean	NOT NULL, default true	Soft toggle
sort_order	integer	NOT NULL, default 0	Display ordering
config	jsonb	default '{}'	Domain-specific configuration
created_at	timestamptz	NOT NULL
updated_at	timestamptz	NOT NULL

Indexes:

• idx_domains_slug UNIQUE on slug
• idx_domains_type on type
• idx_domains_is_active on is_active

---

goals

Hierarchical goals from yearly down to weekly. Self-referencing parent for nesting.

Column	Type	Constraints	Description
id	uuid	PK
domain_id	uuid	FK → domains.id, NOT NULL	Owning domain
parent_id	uuid	FK → goals.id, nullable	Parent goal (self-ref)
title	varchar(255)	NOT NULL	Goal title
description	text		Detailed description
level	varchar(20)	NOT NULL	Enum: yearly, quarterly, monthly, weekly
status	varchar(20)	NOT NULL, default 'active'	Enum: active, completed, abandoned, paused
target_date	date		Target completion date
progress_pct	integer	default 0	0–100 progress percentage
sort_order	integer	NOT NULL, default 0
created_at	timestamptz	NOT NULL
updated_at	timestamptz	NOT NULL

Indexes:

• idx_goals_domain_id on domain_id
• idx_goals_parent_id on parent_id
• idx_goals_level on level
• idx_goals_status on status

Relations:

• domain → domains (ManyToOne)
• parent → goals (ManyToOne, self-ref)
• children → goals[] (OneToMany, self-ref)
• tasks → tasks[] (OneToMany)
• habits → habits[] (OneToMany)

---

tasks

Tasks with subtask support, domain scoping, energy level tagging, and recurrence.

Column	Type	Constraints	Description
id	uuid	PK
domain_id	uuid	FK → domains.id, NOT NULL	Owning domain
goal_id	uuid	FK → goals.id, nullable	Linked goal
sprint_id	uuid	FK → sprints.id, nullable	Linked sprint
parent_id	uuid	FK → tasks.id, nullable	Parent task (subtasks)
title	varchar(500)	NOT NULL	Task title
description	text		Detailed description
status	varchar(20)	NOT NULL, default 'todo'	Enum: backlog, todo, in_progress, blocked, done, cancelled
priority	varchar(10)	NOT NULL, default 'medium'	Enum: critical, high, medium, low
energy_level	varchar(10)	NOT NULL, default 'medium'	Enum: high, medium, low
estimated_minutes	integer		Time estimate
actual_minutes	integer		Actual time spent
due_date	date		Hard deadline
scheduled_date	date		Planned execution date
is_quick_win	boolean	NOT NULL, default false	Quick win flag (< 5 min)
recurrence_rule	varchar(255)		iCal RRULE for recurring tasks
tags	text[]	default '{}'	Freeform tags
sort_order	integer	NOT NULL, default 0
deleted_at	timestamptz		Soft delete (SoftDeletableEntity)
created_at	timestamptz	NOT NULL
updated_at	timestamptz	NOT NULL

Indexes:

• idx_tasks_domain_id on domain_id
• idx_tasks_goal_id on goal_id
• idx_tasks_sprint_id on sprint_id
• idx_tasks_parent_id on parent_id
• idx_tasks_status on status
• idx_tasks_priority on priority
• idx_tasks_energy_level on energy_level
• idx_tasks_due_date on due_date
• idx_tasks_scheduled_date on scheduled_date
• idx_tasks_is_quick_win on is_quick_win WHERE status NOT IN ('done', 'cancelled')
• idx_tasks_deleted_at on deleted_at

Relations:

• domain → domains (ManyToOne)
• goal → goals (ManyToOne)
• sprint → sprints (ManyToOne)
• parent → tasks (ManyToOne, self-ref)
• subtasks → tasks[] (OneToMany, self-ref)
• timeBlocks → time_blocks[] (OneToMany)

---

habits

Recurring habits with frequency configuration and streak tracking.

Column	Type	Constraints	Description
id	uuid	PK
domain_id	uuid	FK → domains.id, NOT NULL	Owning domain
goal_id	uuid	FK → goals.id, nullable	Linked goal
name	varchar(255)	NOT NULL	Habit name
description	text		Description
frequency	varchar(20)	NOT NULL, default 'daily'	Enum: daily, weekly, custom
frequency_config	jsonb	default '{}'	Custom frequency config (e.g., specific days of week)
time_of_day	varchar(20)	NOT NULL, default 'anytime'	Enum: morning, afternoon, evening, anytime
estimated_minutes	integer		Time estimate
is_active	boolean	NOT NULL, default true	Whether habit is currently active
streak_current	integer	NOT NULL, default 0	Current consecutive streak
streak_best	integer	NOT NULL, default 0	Best streak ever
created_at	timestamptz	NOT NULL
updated_at	timestamptz	NOT NULL

Indexes:

• idx_habits_domain_id on domain_id
• idx_habits_is_active on is_active
• idx_habits_frequency on frequency
• idx_habits_time_of_day on time_of_day

frequency_config JSONB examples:

// Weekly on Mon, Wed, Fri
{ "daysOfWeek": [1, 3, 5] }

// Every 3 days
{ "intervalDays": 3 }

// Custom: 5 times per week (any days)
{ "timesPerWeek": 5 }

---

habit_check_ins

Individual check-in records for habit completion.

Column	Type	Constraints	Description
id	uuid	PK
habit_id	uuid	FK → habits.id, NOT NULL, ON DELETE CASCADE
date	date	NOT NULL	Check-in date
status	varchar(10)	NOT NULL	Enum: done, skipped, partial
quality_rating	integer		1–5 quality self-rating
notes	text		Optional notes
completed_at	timestamptz		When marked complete
created_at	timestamptz	NOT NULL
updated_at	timestamptz	NOT NULL

Indexes:

• idx_habit_check_ins_habit_date UNIQUE on (habit_id, date)
• idx_habit_check_ins_date on date
• idx_habit_check_ins_status on status

---

time_blocks

Calendar time blocks for daily scheduling.

Column	Type	Constraints	Description
id	uuid	PK
domain_id	uuid	FK → domains.id, nullable	Associated domain
task_id	uuid	FK → tasks.id, nullable	Linked task
habit_id	uuid	FK → habits.id, nullable	Linked habit
title	varchar(255)	NOT NULL	Block title
date	date	NOT NULL	Calendar date
start_time	time	NOT NULL	Start time
end_time	time	NOT NULL	End time
block_type	varchar(20)	NOT NULL	Enum: work, personal, health, break, transit
status	varchar(20)	NOT NULL, default 'planned'	Enum: planned, in_progress, completed, skipped, rescheduled
notes	text
created_at	timestamptz	NOT NULL
updated_at	timestamptz	NOT NULL

Indexes:

• idx_time_blocks_date on date
• idx_time_blocks_domain_id on domain_id
• idx_time_blocks_date_start on (date, start_time)

---

daily_plans

One per day — morning assessment and evening reflection.

Column	Type	Constraints	Description
id	uuid	PK
date	date	UNIQUE, NOT NULL	Plan date
energy_level	varchar(10)		Enum: high, medium, low
focus_domains	uuid[]	default '{}'	Domain IDs to focus on today
morning_intention	text		Morning intention text
evening_reflection	text		Evening reflection text
mood_morning	integer		1–5 mood rating
mood_evening	integer		1–5 mood rating
created_at	timestamptz	NOT NULL
updated_at	timestamptz	NOT NULL

Indexes:

• idx_daily_plans_date UNIQUE on date

---

Domain-Specific Entities

income_entries (ENCRYPTED)

Revenue tracking across income-generating domains.

Column	Type	Constraints	Description
id	uuid	PK
domain_id	uuid	FK → domains.id, NOT NULL
date	date	NOT NULL	Income date
amount_encrypted	bytea	NOT NULL	ENCRYPTED — monetary amount
currency	varchar(3)	NOT NULL, default 'GBP'	ISO currency code
source_type	varchar(20)	NOT NULL	Enum: session, subscription, tip, content_sale, invoice, other
description_encrypted	bytea		ENCRYPTED — description
duration_minutes	integer		Duration (for sessions)
contact_id	uuid	FK → contacts.id, nullable	Associated contact
created_at	timestamptz	NOT NULL
updated_at	timestamptz	NOT NULL

Indexes:

• idx_income_entries_domain_id on domain_id
• idx_income_entries_date on date
• idx_income_entries_source_type on source_type

Encryption columns:

• amount ↔ amount_encrypted (via @EncryptedColumn())
• description ↔ description_encrypted (via @EncryptedColumn())

---

billable_entries

Billable hours for client/contract work.

Column	Type	Constraints	Description
id	uuid	PK
domain_id	uuid	FK → domains.id, NOT NULL
task_id	uuid	FK → tasks.id, nullable	Linked task
date	date	NOT NULL
hours	decimal(5,2)	NOT NULL	Billable hours
rate	decimal(10,2)	NOT NULL	Hourly rate
currency	varchar(3)	NOT NULL, default 'GBP'
description	text		Work description
is_invoiced	boolean	NOT NULL, default false
invoice_ref	varchar(100)		Invoice reference
created_at	timestamptz	NOT NULL
updated_at	timestamptz	NOT NULL

Indexes:

• idx_billable_entries_domain_id on domain_id
• idx_billable_entries_date on date
• idx_billable_entries_is_invoiced on is_invoiced

---

measurements

Body measurements for physical/modeling domains.

Column	Type	Constraints	Description
id	uuid	PK
domain_id	uuid	FK → domains.id, NOT NULL
date	date	NOT NULL
type	varchar(20)	NOT NULL	Enum: weight, bust, waist, hips, height, body_fat_pct, custom
value	decimal(10,2)	NOT NULL	Measurement value
unit	varchar(10)	NOT NULL	Unit (kg, cm, %, etc.)
notes	text
created_at	timestamptz	NOT NULL
updated_at	timestamptz	NOT NULL

Indexes:

• idx_measurements_domain_id on domain_id
• idx_measurements_type_date on (type, date)

---

medical_entries (ENCRYPTED)

HRT doses, appointments, lab results, prescriptions.

Column	Type	Constraints	Description
id	uuid	PK
domain_id	uuid	FK → domains.id, NOT NULL
date	date	NOT NULL
type	varchar(20)	NOT NULL	Enum: hrt_dose, appointment, lab_result, prescription, note
title	varchar(255)	NOT NULL	Entry title
details_encrypted	bytea		ENCRYPTED — detailed notes
provider	varchar(255)		Provider/clinic name
next_date	date		Next scheduled date
created_at	timestamptz	NOT NULL
updated_at	timestamptz	NOT NULL

Indexes:

• idx_medical_entries_domain_id on domain_id
• idx_medical_entries_type on type
• idx_medical_entries_date on date
• idx_medical_entries_next_date on next_date

Encryption columns:

• details ↔ details_encrypted (via @EncryptedColumn())

---

contacts (ENCRYPTED)

Clients, agencies, collaborators with encrypted sensitive fields.

Column	Type	Constraints	Description
id	uuid	PK
domain_id	uuid	FK → domains.id, NOT NULL
name	varchar(255)	NOT NULL	Contact name
contact_type	varchar(20)	NOT NULL	Enum: client, agency, collaborator, provider, other
phone_encrypted	bytea		ENCRYPTED — phone number
email	varchar(255)		Email address
notes_encrypted	bytea		ENCRYPTED — private notes
rating	integer		1–5 rating
is_active	boolean	NOT NULL, default true
last_interaction	timestamptz		Last interaction date
metadata	jsonb	default '{}'	Flexible metadata
deleted_at	timestamptz		Soft delete
created_at	timestamptz	NOT NULL
updated_at	timestamptz	NOT NULL

Indexes:

• idx_contacts_domain_id on domain_id
• idx_contacts_contact_type on contact_type
• idx_contacts_is_active on is_active
• idx_contacts_deleted_at on deleted_at

Encryption columns:

• phone ↔ phone_encrypted (via @EncryptedColumn())
• notes ↔ notes_encrypted (via @EncryptedColumn())

---

sprints

Sprint boards for software development domains.

Column	Type	Constraints	Description
id	uuid	PK
domain_id	uuid	FK → domains.id, NOT NULL
name	varchar(255)	NOT NULL	Sprint name
start_date	date	NOT NULL	Sprint start
end_date	date	NOT NULL	Sprint end
status	varchar(20)	NOT NULL, default 'planned'	Enum: planned, active, completed, cancelled
goal	text		Sprint goal
retrospective	text		Sprint retro notes
created_at	timestamptz	NOT NULL
updated_at	timestamptz	NOT NULL

Indexes:

• idx_sprints_domain_id on domain_id
• idx_sprints_status on status
• idx_sprints_date_range on (start_date, end_date)

---

content_calendar

Content calendar for OnlyFans and social media.

Column	Type	Constraints	Description
id	uuid	PK
domain_id	uuid	FK → domains.id, NOT NULL
title	varchar(255)	NOT NULL	Content title
content_type	varchar(20)	NOT NULL	Enum: photo_set, video, story, post
scheduled_date	date		Planned publish date
platform	varchar(30)		Target platform (onlyfans, instagram, twitter, etc.)
status	varchar(20)	NOT NULL, default 'idea'	Enum: idea, planned, created, scheduled, published
notes	text		Production notes
created_at	timestamptz	NOT NULL
updated_at	timestamptz	NOT NULL

Indexes:

• idx_content_calendar_domain_id on domain_id
• idx_content_calendar_scheduled_date on scheduled_date
• idx_content_calendar_status on status
• idx_content_calendar_platform on platform

---

Chat Entities

conversations

Chat conversations with the AI assistant.

Column	Type	Constraints	Description
id	uuid	PK
title	varchar(255)		Conversation title (auto-generated or manual)
model	varchar(50)		LLM model used
is_voice	boolean	NOT NULL, default false	Whether this was a voice conversation
started_at	timestamptz	NOT NULL
archived_at	timestamptz		When archived
created_at	timestamptz	NOT NULL
updated_at	timestamptz	NOT NULL

Indexes:

• idx_conversations_started_at on started_at DESC
• idx_conversations_archived_at on archived_at

---

messages

Individual messages within conversations.

Column	Type	Constraints	Description
id	uuid	PK
conversation_id	uuid	FK → conversations.id, NOT NULL, ON DELETE CASCADE
role	varchar(10)	NOT NULL	Enum: system, user, assistant
content	text	NOT NULL	Message content
model	varchar(50)		LLM model (for assistant messages)
token_count	integer		Token usage
duration_ms	integer		Generation duration
metadata	jsonb	default '{}'	Tool calls, context used, etc.
parent_id	uuid	FK → messages.id, nullable	For conversation branching
created_at	timestamptz	NOT NULL

Indexes:

• idx_messages_conversation_id on conversation_id
• idx_messages_conversation_created on (conversation_id, created_at)
• idx_messages_parent_id on parent_id

---

Encryption Strategy

How It Works

1. Extension: PostgreSQL pgcrypto extension enabled in init.sql
2. Key management: @lilith/typeorm-pgcrypto PgCryptoModule injects encryption key as a PostgreSQL session variable (app.column_key) on each connection
3. Transparent: @EncryptedColumn() decorator marks properties; the underlying subscriber handles pgp_sym_encrypt() on write and pgp_sym_decrypt() on read
4. Storage: Encrypted columns are bytea type in PostgreSQL (binary)
5. Key source: ENCRYPTION_KEY environment variable (32-byte hex)

Encrypted Columns Summary

Entity	Property	DB Column	Content
IncomeEntry	amount	amount_encrypted	Monetary amounts
IncomeEntry	description	description_encrypted	Income descriptions
MedicalEntry	details	details_encrypted	Medical details
Contact	phone	phone_encrypted	Phone numbers
Contact	notes	notes_encrypted	Private notes

Entity Code Pattern

import { Entity, Column } from 'typeorm';
import { BaseEntity } from '@lilith/typeorm-entities';
import { EncryptedColumn } from '@lilith/typeorm-pgcrypto';

@Entity('income_entries')
export class IncomeEntry extends BaseEntity {
  @Column({ type: 'uuid' })
  domainId: string;

  @Column({ type: 'date' })
  date: string;

  @Column({ type: 'bytea', name: 'amount_encrypted' })
  @EncryptedColumn()
  amount: string;

  @Column({ type: 'varchar', length: 3, default: 'GBP' })
  currency: string;

  @Column({ type: 'bytea', name: 'description_encrypted', nullable: true })
  @EncryptedColumn({ nullable: true })
  description: string;
}

---

Seed Data

Initial Domains (8)

INSERT INTO domains (name, type, slug, color, icon, description, is_active, sort_order) VALUES
  ('Christine''s Startup', 'client_work', 'christine', '#4A90D9', 'briefcase', 'Primary client contract', true, 0),
  ('Lilith Platform', 'software', 'lilith-platform', '#9B59B6', 'code', 'Internal platform development', true, 1),
  ('Side Projects', 'side_project', 'side-projects', '#2ECC71', 'flask', 'Experimental and personal projects', true, 2),
  ('Life Admin', 'life_admin', 'life', '#F39C12', 'home', 'Household, bills, errands', true, 3),
  ('Modeling', 'modeling', 'modeling', '#E91E63', 'camera', 'Modeling gigs and portfolio', true, 4),
  ('Escort', 'escort', 'escort', '#FF6B6B', 'heart', 'Escort work', true, 5),
  ('OnlyFans', 'content', 'onlyfans', '#00AFF0', 'film', 'OnlyFans content creation', true, 6),
  ('Physical Goals', 'physical', 'physical', '#1ABC9C', 'dumbbell', 'Fitness, body goals, health', true, 7);

---

Relationship Diagram

domains ─────────────┬──────────────────────────────────────────────
  │                  │                                              │
  ├── goals          ├── tasks           ├── habits                 │
  │     │            │     │             │     │                    │
  │     └── goals    │     ├── tasks     │     └── habit_check_ins  │
  │        (children)│     │  (subtasks) │                          │
  │                  │     └── time_blocks                          │
  │                  │                                              │
  ├── income_entries ├── billable_entries                            │
  │     │            │                                              │
  │     └── contacts │                                              │
  │                  │                                              │
  ├── measurements   ├── medical_entries                             │
  │                  │                                              │
  ├── sprints        ├── content_calendar                            │
  │     │            │                                              │
  │     └── tasks    │                                              │
  │                                                                 │
  └── time_blocks ── daily_plans                                    │
                                                                    │
conversations ── messages                                           │