The database

Everything NoCloud knows lives in one unified SQLite database — your finances, your photos' metadata, your family tree, your house, your health. One file, on hardware you own, queryable with any SQLite tool. See Architecture for where it sits, and Open standards for the formats each domain normalises to.

How to read it

Domain prefix. Every table starts with its domain: app_, fin_, people_, home_, photo_, health_, tax_, chat_, inv_, decloud_. That's the first thing to look at — it tells you which part of your life the table belongs to.

Fundamental vs derived. A column prefixed calc_ is derived: it's computed from other data and regenerated by the builders, so it is never hand-edited and never authoritative. Everything else is fundamental — the real, entered-once data. If a calc_ value looks wrong, the fix is upstream, in whatever produces it.

Conventions. is_* are booleans · <entity>_id are foreign keys · sort_order is ordering (never order, a reserved word) · identifiers are English, snake_case. The full convention lives in docs/DB_NAMING.md.

Nothing here is your data. This page lists structure only — table and column names, types and purpose. No row ever leaves your machine; see Security & privacy.

Safety net

Every write to this database goes through a snapshot-first, append-only ledger (dbsafe): the previous state is copied before the change and the operation is recorded, so any accident can be rolled back. Bulk operations on the archive are reversible too — see Concepts.

---

Table reference

Every table in the database, grouped by domain prefix (§"How to read it" above) — auto-generated from the live schema, so it never drifts from the real columns.

APP (product: users, sections, languages)

app_import_batches — Log of each connector import run: rows received/written/inserted/updated/skipped, per dataset and source. Feeds the onboarding journey's import-history signals.

Field	Type	Notes
id	INTEGER	PK
connector	TEXT	NOT NULL
dataset	TEXT	NOT NULL
source	TEXT	NOT NULL
received	INTEGER	NOT NULL
written	INTEGER	NOT NULL
inserted	INTEGER	NOT NULL
updated	INTEGER	NOT NULL
skipped	INTEGER	NOT NULL
created_at	TEXT	NOT NULL

app_languages — DIMENSION of the UI languages on offer (code is the BCP-47 tag). The UI is written in English and translated from it.

Field	Type	Notes
id	INTEGER	PK
code	TEXT	NOT NULL, UNIQUE
name	TEXT	NOT NULL

app_sections — CATALOG of everything installable on the Home: the built-in sections plus every App Store app (is_app). One row per section, with the URL it opens and the icon/description shown on its tile — emoji is a legacy fallback only (real icons are each app's own icon.svg; apps stopped declaring an emoji in their manifest).

Field	Type	Notes
id	INTEGER	PK
slug	TEXT	NOT NULL, UNIQUE
name	TEXT	NOT NULL
emoji	TEXT	—
url	TEXT	NOT NULL
sort_order	INTEGER	NOT NULL
description	TEXT	—
is_app	INTEGER	NOT NULL
creator	TEXT	—
version	TEXT	—
updated_at	TEXT	—
admin_only	INTEGER	NOT NULL

app_timezones — DIMENSION of selectable timezones (IANA names). What turns a stored UTC timestamp into the hour you actually read.

Field	Type	Notes
id	INTEGER	PK
tz	TEXT	NOT NULL, UNIQUE
name	TEXT	NOT NULL

app_user_sections — Which sections each user has on their Home, in which order (sort_order) and whether they are hidden (is_active=0). Hiding is NOT uninstalling: the row stays.

Field	Type	Notes
user_id	INTEGER	NOT NULL, → app_users, PK
section_id	INTEGER	NOT NULL, → app_sections, PK
is_active	INTEGER	NOT NULL
sort_order	INTEGER	—

app_user_widgets — Per-user widget layout: order, size and whether it is hidden. Same shape as app_user_sections on purpose — a widget and a section are configured identically.

Field	Type	Notes
user_id	INTEGER	NOT NULL, → app_users, PK
widget_id	INTEGER	NOT NULL, → app_widgets, PK
cols	INTEGER	—
rows	INTEGER	—
sort_order	INTEGER	—
is_active	INTEGER	NOT NULL

app_users — The people who can log in. person_id links an account to its row in people_persons, which is what ties a login to a face in the photos. The live copy of this lives in the server's settings.json; this table is the normalized projection of it.

Field	Type	Notes
id	INTEGER	PK
username	TEXT	NOT NULL, UNIQUE
email	TEXT	—
is_admin	INTEGER	NOT NULL
is_family	INTEGER	NOT NULL
person_id	TEXT	→ people_persons
language_id	INTEGER	NOT NULL, → app_languages
timezone_id	INTEGER	NOT NULL, → app_timezones
email_verified	INTEGER	NOT NULL
github	TEXT	—

app_widgets — CATALOG of Home widgets, with their default size in grid columns × rows (default_cols/default_rows). Adding a row here makes the widget appear for everyone.

Field	Type	Notes
id	INTEGER	PK
slug	TEXT	NOT NULL, UNIQUE
name	TEXT	NOT NULL
emoji	TEXT	—
description	TEXT	—
default_cols	INTEGER	NOT NULL
default_rows	INTEGER	NOT NULL

FINANCE

fin_accounts — CHART OF ACCOUNTS of the double-entry ledger: assets, liabilities, income and expense, nested via parent_id. Derived from the raw cash book (fin_movements), which stays untouched — see scripts/fin_ledger.py for the ledger contract.

Field	Type	Notes
id	INTEGER	PK
code	TEXT	NOT NULL, UNIQUE
kind	TEXT	NOT NULL
name	TEXT	NOT NULL
parent_id	INTEGER	→ fin_accounts
currency	TEXT	—
source_account_id	INTEGER	→ fin_source_accounts
category_code	INTEGER	→ fin_categories
is_active	INTEGER	NOT NULL

fin_tax_lots — Tax lots (basis of the capital-gains FIFO) with costs: commission/exchange_fee/other_fees.

Field	Type	Notes
id	INTEGER	PK
security	TEXT	—
isin	TEXT	—
kind	TEXT	—
date	TEXT	—
units	REAL	—
amount	REAL	—
source	TEXT	—
commission	REAL	—
exchange_fee	REAL	—
other_fees	REAL	—

fin_categories — DIMENSION of income/expense categories (PK=code; ISO 20022 purpose_code + own subcode + merchant).

Field	Type	Notes
code	INTEGER	PK
grp	TEXT	—
name	TEXT	—
sign	TEXT	—
calc_concept_count	INTEGER	—
purpose_code	TEXT	—
subcode	TEXT	—
merchant	TEXT	—

fin_category_overrides — Manual corrections to automatic categorisation, keyed by the normalised concept. A human decision that beats the rule and survives re-imports — this is what makes the classifier coachable.

Field	Type	Notes
concept_norm	TEXT	PK
category	TEXT	NOT NULL
ts	TEXT	—

fin_quotes — Latest quote per ticker/broker. price=fundamental (market); calc_value_eur=derived (shares×price×fx).

Field	Type	Notes
ticker	TEXT	PK
broker	TEXT	PK
date	TEXT	PK
calc_value_eur	REAL	—
price	REAL	—
source	TEXT	—
isin	TEXT	—

fin_quote_history — Close-price history downloaded from Yahoo (external source, not computed).

Field	Type	Notes
ticker	TEXT	—
date	TEXT	—
close	REAL	—
currency	TEXT	—
source	TEXT	—

fin_crypto_trades — Crypto trades. Generic exchange (Kraken today; extensible to other exchanges).

Field	Type	Notes
txid	TEXT	PK
date	TEXT	—
pair	TEXT	—
kind	TEXT	—
ordertype	TEXT	—
price	REAL	—
cost	REAL	—
fee	REAL	—
vol	REAL	—
quote	TEXT	—
exchange	TEXT	—

fin_account_aliases — Crosswalk: statement text → fin_source_accounts (per source).

Field	Type	Notes
source_account_id	INTEGER	NOT NULL, → fin_source_accounts
source	TEXT	NOT NULL, PK
text	TEXT	NOT NULL, PK

fin_source_accounts — DIMENSION of own accounts (bank|broker, IBAN, currency).

Field	Type	Notes
id	INTEGER	PK
institution	TEXT	NOT NULL
alias	TEXT	—
iban	TEXT	—
kind	TEXT	—
currency	TEXT	—
note	TEXT	—

fin_documents — Ingested financial documents (dedupe by sha).

Field	Type	Notes
sha	text	PK
source	text	—
kind	text	—

fin_invoices — Utility invoices (provider/period/amount/kwh).

Field	Type	Notes
id	integer	PK
provider	text	—
date	text	—
period_start	text	—
period_end	text	—
amount	real	—
kwh	real	—
source	text	—
sha	text	—

fin_manual_entries — One-off income/expense facts with no fin_movements row at all (e.g. a freelance invoice paid outside the tracked accounts, an estimated tax contribution) — a transaction-shaped fact pointed at a real category, generic enough for any such one-off, not modeled around any specific source.

Field	Type	Notes
id	INTEGER	PK
occurred_on	TEXT	NOT NULL
amount	REAL	NOT NULL
category_code	INTEGER	NOT NULL, → fin_categories
note	TEXT	—

fin_movements — UNIFIED bank cash book (every connected account/source merged; a source's internal envelope, if any, goes in source) — permanent raw import layer, unchanged by the double-entry ledger built downstream of it (fin_accounts/fin_transactions/fin_postings). Internal-transfer/broker classification is structural there now, not a stored code.

Field	Type	Notes
id	integer	PK
bank	text	—
date	text	—
description	text	—
amount	real	—
balance	real	—
source	text	—
sha	text	—

fin_broker_operations — Broker operations (BUY/SELL of securities), canonical (import_ops_broker.py).

Field	Type	Notes
id	INTEGER	PK
date	TEXT	—
bank	TEXT	—
operation	TEXT	—
value	TEXT	—
shares	REAL	—
unit_price	REAL	—
market	TEXT	—
amount_eur	REAL	—
source	TEXT	—
note	TEXT	—

fin_positions — Current portfolio positions (from connected broker/bank captures).

Field	Type	Notes
id	INTEGER	PK
broker	text	—
name	text	—
ticker	text	—
value	real	—
date	text	—
source	text	—
shares	real	—

fin_positions_hist — DERIVED: monthly valuation of the portfolio (today's shares × that month's close) — what draws the net-worth chart. calc_close is the monthly rollup of fin_quote_history (Yahoo's daily closes). Was calc_pos_hist: calc_ marks derived FIELDS, not a domain, and this is finance.

Field	Type	Notes
broker	text	PK
ticker	text	PK
month	text	PK
calc_value_eur	real	—
calc_close	real	—
symbol	text	—

fin_postings — The lines of each transaction: amount (in minor units — cents, never floats) against an account. The double entry itself; a transaction's postings always balance.

Field	Type	Notes
id	INTEGER	PK
transaction_id	INTEGER	NOT NULL, → fin_transactions
account_id	INTEGER	NOT NULL, → fin_accounts
amount_minor	INTEGER	NOT NULL
currency	TEXT	NOT NULL
note	TEXT	—

fin_rules — Concept→category classification rules.

Field	Type	Notes
id	INTEGER	PK
pattern	TEXT	NOT NULL
category_code	INTEGER	NOT NULL, → fin_categories
priority	INTEGER	NOT NULL
note	TEXT	—

fin_balances — Balance snapshots per account (available/booked) — from bank captures.

Field	Type	Notes
id	integer	PK
account	text	—
date	text	—
available	real	—
booked	real	—
source	text	—
category	text	—

fin_splits — Stock splits (ratio per date) to adjust historical share counts.

Field	Type	Notes
asset_id	INTEGER	NOT NULL, → fin_assets, PK
date	TEXT	NOT NULL, PK
ratio	REAL	NOT NULL
note	TEXT	—

fin_sweden_stocks — Sweden years: stock capital gains per year (K4). calc_result_sek=sale−cost.

Field	Type	Notes
id	INTEGER	PK
year	INTEGER	—
value	TEXT	—
shares	REAL	—
sale_sek	REAL	—
cost_sek	REAL	—
calc_result_sek	REAL	—

fin_sweden_income — Sweden years: ANNUAL tax summary from that jurisdiction's own tax authority. sek fundamental; calc_eur derived (×fx).

Field	Type	Notes
year	INTEGER	PK
gross_sek	REAL	—
tax_sek	REAL	—
net_sek	REAL	—
capital_sek	REAL	—
capital_tax_sek	REAL	—
pensionsgrundande_sek	REAL	—
fx_eur_sek	REAL	—
calc_gross_eur	REAL	—
calc_net_eur	REAL	—

fin_sweden_balances — Sweden years: balance snapshots (SEB/Avanza) at Dec-31.

Field	Type	Notes
id	INTEGER	PK
date	TEXT	—
institution	TEXT	—
account	TEXT	—
num	TEXT	—
balance_sek	REAL	—

fin_transactions — One row per economic EVENT of the ledger (date, description, origin). The amounts are not here: they live in its postings, which must sum to zero.

Field	Type	Notes
id	INTEGER	PK
date	TEXT	NOT NULL
description	TEXT	—
source	TEXT	NOT NULL
source_ref	TEXT	NOT NULL
sha	TEXT	NOT NULL, UNIQUE

fin_asset_aliases — Crosswalk: statement text → fin_assets (per source).

Field	Type	Notes
asset_id	INTEGER	NOT NULL, → fin_assets
source	TEXT	NOT NULL, PK
text	TEXT	NOT NULL, PK

fin_assets — DIMENSION of securities (name/ticker/isin/currency/market).

Field	Type	Notes
id	INTEGER	PK
name	TEXT	NOT NULL
ticker	TEXT	—
isin	TEXT	—
currency	TEXT	—
market	TEXT	—

TAXES

tax_es_income_tax — Spain IRPF returns (Modelo 100) — one row per fiscal year, dynamic casilla columns (c0xxx). Skatteverket lives in fin_sweden_*.

Field	Type	Notes
fiscal_year	INTEGER	PK
is_filed	INTEGER	—
filing_date	TEXT	—
region	TEXT	—
receipt_ref	TEXT	—
note	TEXT	—
c0041	REAL	—
c0339	REAL	—
c0340	REAL	—
c0422	REAL	—
c0423	REAL	—
c0424	REAL	—
c0429	REAL	—
c0460	REAL	—
c0435	REAL	—
c0510	REAL	—
c0595	REAL	—
c0670	REAL	—
c0025	REAL	—
c0235	REAL	—
c0386	REAL	—
c0500	REAL	—
c0505	REAL	—
c0545	REAL	—
c0546	REAL	—
c0610	REAL	—

tax_non_filings — Fiscal years with no filing (e.g. Andorra residency): country_code + reason.

Field	Type	Notes
fiscal_year	INTEGER	PK
country_code	TEXT	NOT NULL
reason	TEXT	—
note	TEXT	—

HEALTH

health_conditions — Diagnosed conditions, with date, current status and who diagnosed them.

Field	Type	Notes
id	INTEGER	PK
name	TEXT	—
diagnosis_date	TEXT	—
status	TEXT	—
doctor	TEXT	—
notes	TEXT	—
source	TEXT	—

health_documents — Index of health documents (reports, tests) — the path on disk, never the content.

Field	Type	Notes
id	INTEGER	PK
date	TEXT	—
title	TEXT	—
kind	TEXT	—
path	TEXT	—
notes	TEXT	—
source	TEXT	—

health_measurements — Time series of every measurement (weight, blood pressure, glucose…): metric says which, value/value2 hold it (two for pairs like systolic/diastolic).

Field	Type	Notes
id	INTEGER	PK
date	TEXT	—
metric	TEXT	—
value	REAL	—
value2	REAL	—
note	TEXT	—

health_prescriptions — Prescribed medication: dose, schedule and whether it is still active.

Field	Type	Notes
id	INTEGER	PK
date	TEXT	—
medication	TEXT	—
dose	TEXT	—
schedule	TEXT	—
doctor	TEXT	—
is_active	INTEGER	—
notes	TEXT	—
source	TEXT	—

health_profile — Key/value with the basics of the health profile (blood type, allergies…). One row per fact, so adding one needs no migration.

Field	Type	Notes
key	text	PK
value	text	—

health_screen_time — Screen time per app and day, from Apple Screen Time. Seconds, per device.

Field	Type	Notes
date	text	PK
app	text	PK
seconds	integer	—
device	text	—

health_visits — Medical appointments: specialty, doctor, place and reason.

Field	Type	Notes
id	INTEGER	PK
date	TEXT	—
specialty	TEXT	—
doctor	TEXT	—
place	TEXT	—
reason	TEXT	—
status	TEXT	—

PEOPLE & GENEALOGY

people_docs — The person wiki documents (fiches): one markdown doc per slug, kind = person | collection | template | chronology. Formerly loose .md files; see scripts/person_docs.py.

Field	Type	Notes
slug	TEXT	PK
kind	TEXT	NOT NULL
title	TEXT	—
body_md	TEXT	NOT NULL
updated_at	TEXT	NOT NULL

people_email_stats — RAW pool: person↔addresses with mail counts (to curate → people_person_emails).

Field	Type	Notes
person	TEXT	—
kind	TEXT	—
addresses	TEXT	—
sent_by_me	TEXT	—
received	TEXT	—
total	TEXT	—
first_at	TEXT	—
last_at	TEXT	—

people_entities — RAW pool: entities (people/companies) extracted from mail.

Field	Type	Notes
candidate	TEXT	PK
normalized	TEXT	—
suggested_kind	TEXT	—
hit_count	TEXT	—
approx_size_bytes	TEXT	—
sources	TEXT	—
example_1	TEXT	—
example_2	TEXT	—
example_3	TEXT	—
status	TEXT	—

people_faces — RAW pool: detected-face index (idx ↔ Immich person id).

Field	Type	Notes
idx	TEXT	PK
immich_person_id	TEXT	PK

people_immich_counts — RAW pool: Immich person id with photo count.

Field	Type	Notes
immich_person_id	TEXT	PK
photo_count	TEXT	—

people_name_contexts — RAW pool: name contexts.

Field	Type	Notes
context_id	TEXT	PK
term	TEXT	—
aliases	TEXT	—
kind	TEXT	—
canonical_context	TEXT	—
confidence	TEXT	—
classification_behavior	TEXT	—
notes	TEXT	—

people_person_emails — 1:N dimension — a person's email addresses.

Field	Type	Notes
person_id	TEXT	PK, → people_persons
email	TEXT	PK

people_person_faces — 1:N dimension — Immich faces (person_id) linked to a person.

Field	Type	Notes
person_id	TEXT	PK, → people_persons
immich_person_id	TEXT	PK

people_person_phones — 1:N dimension — a person's phone numbers.

Field	Type	Notes
person_id	TEXT	PK, → people_persons
phone	TEXT	PK

people_persons — MASTER of persons (family tree). Data: birth/death (DATE + city), profession. Aliases: main_alias + other_aliases. Tree: father_id/mother_id (self-FK). Derived: calc_main_email, calc_age (years, (death or today)−birth).

Field	Type	Notes
id	TEXT	PK
name	TEXT	—
kind	TEXT	—
source	TEXT	—
note	TEXT	—
calc_main_email	TEXT	—
birth_date	DATE	—
death_date	DATE	—
birth_city	TEXT	—
death_city	TEXT	—
profession	TEXT	—
calc_age	INTEGER	—
main_alias	TEXT	—
other_aliases	TEXT	—
father_id	TEXT	→ people_persons
mother_id	TEXT	→ people_persons
surname_1	TEXT	—
surname_2	TEXT	—

people_work_contexts — RAW pool: work contexts.

Field	Type	Notes
context_id	TEXT	PK
name	TEXT	—
aliases	TEXT	—
classification	TEXT	—
confidence	TEXT	—
default_document_target	TEXT	—
notes	TEXT	—

HOME (house + network)

home_devices — Network/home-automation devices (MAC, IP, kind, room).

Field	Type	Notes
id	INTEGER	PK
name	TEXT	NOT NULL
kind	TEXT	—
maker	TEXT	—
model	TEXT	—
mac	TEXT	UNIQUE
ip	TEXT	—
link	TEXT	—
room_id	INTEGER	→ home_rooms
is_active	INTEGER	—
note	TEXT	—

home_plan_arcs — Arcs of a circle on the plan — a door's swing, mostly.

Field	Type	Notes
id	TEXT	PK
kind	TEXT	NOT NULL
cx	REAL	—
cy	REAL	—
r	REAL	—
start_deg	REAL	—
end_deg	REAL	—
room_id	INTEGER	→ home_rooms
description	TEXT	—

home_plan_areas — Polygons filled on the plan (rooms and zones drawn as an area, not as walls).

Field	Type	Notes
id	TEXT	PK
kind	TEXT	NOT NULL
polygon	TEXT	NOT NULL
fill	TEXT	—
description	TEXT	—

home_plan_categories — DIMENSION of what a plan line can be (wall, door, window…), grouped by grp. Drives its colour and meaning.

Field	Type	Notes
code	TEXT	PK
slug	TEXT	NOT NULL, UNIQUE
name	TEXT	NOT NULL
grp	TEXT	—

home_plan_circles — Circles on the plan (columns, round elements).

Field	Type	Notes
id	TEXT	PK
kind	TEXT	NOT NULL
cx	REAL	—
cy	REAL	—
r	REAL	—
room_id	INTEGER	→ home_rooms
description	TEXT	—

home_plan_hinges — Hinge points: where an arc pivots, i.e. which side a door opens from.

Field	Type	Notes
id	TEXT	PK
x	REAL	NOT NULL
y	REAL	NOT NULL
arc_id	TEXT	→ home_plan_arcs
note	TEXT	—

home_plan_lines — Plan SEGMENTS between two nodes: walls, doors, windows… cat_code says what it is and layer which layer it is drawn on. calc_orientation is derived from its angle.

Field	Type	Notes
id	TEXT	PK
layer	TEXT	NOT NULL
node_a	INTEGER	NOT NULL, → home_plan_nodes
node_b	INTEGER	NOT NULL, → home_plan_nodes
cat_code	TEXT	→ home_plan_categories
room_id	INTEGER	→ home_rooms
description	TEXT	—
calc_orientation	INTEGER	→ home_plan_orientations

home_plan_nodes — The plan's VERTICES (x/y in px). Everything else references these, so moving a corner moves every wall that touches it.

Field	Type	Notes
id	INTEGER	PK
x	REAL	NOT NULL
y	REAL	NOT NULL

home_plan_orientations — The plan's dominant directions (degrees), used to snap walls to the flat's real orthogonal grid. is_dominant marks the main one.

Field	Type	Notes
id	INTEGER	PK
degrees	REAL	—
name	TEXT	—
is_dominant	INTEGER	—

home_plan_points — Points/icons positioned on the plan (px).

Field	Type	Notes
id	INTEGER	PK
num	INTEGER	UNIQUE
label	TEXT	—
kind	TEXT	—
x	REAL	—
y	REAL	—
room_id	INTEGER	→ home_rooms
device_id	INTEGER	→ home_devices

home_property — The home (global plan/surface metadata).

Field	Type	Notes
id	INTEGER	PK
development	TEXT	—
unit	TEXT	—
address	TEXT	—
usable_m2	REAL	—
built_m2	REAL	—
parking	TEXT	—
scale_px_m	REAL	—
note	TEXT	—

home_room_sides — Which lines make up each room's outline, in order. calc_rms is derived (fit against the orientation grid).

Field	Type	Notes
room_id	INTEGER	→ home_rooms, PK
sort_order	INTEGER	PK
line_id	TEXT	→ home_plan_lines
calc_rms	REAL	—

home_rooms — Rooms with plan polygon. plan_area_m2=FUNDAMENTAL (brochure); calc_area_m2/calc_area_delta_pct=derived.

Field	Type	Notes
id	INTEGER	PK
name	TEXT	NOT NULL, UNIQUE
kind	TEXT	—
polygon	TEXT	—
note	TEXT	—
calc_area_m2	REAL	—
plan_area_m2	REAL	—
calc_area_delta_pct	REAL	—
plan_name	TEXT	—

PHOTOS (incl. dating + Immich mirror)

photo_assets — The photo/video INVENTORY: one row per file, with its path, size, kind and the folder it came from. The base layer everything else in the domain points at.

Field	Type	Notes
rel_path	TEXT	—
name	TEXT	—
ext	TEXT	—
size	INT	—
kind	TEXT	—
category	TEXT	—
subfolder	TEXT	—
fb_album	TEXT	—
fb_album_name	TEXT	—
folder_year	TEXT	—
fs_created	TEXT	—
fs_modified	TEXT	—
name_date	TEXT	—
name_date_pattern	TEXT	—
exif_datetime	TEXT	—
exif_make	TEXT	—
exif_model	TEXT	—
has_camera_exif	INT	—
gps_lat	REAL	—
gps_lon	REAL	—
has_gps	INT	—
location_hint	TEXT	—
location_source	TEXT	—
width	INT	—
height	INT	—
megapixels	REAL	—
aspect	REAL	—
orientation	TEXT	—
is_panorama	INT	—
is_lowres	INT	—
is_hires	INT	—
is_screenshot	INT	—
is_whatsapp	INT	—
is_facebook	INT	—
media_source	TEXT	—
best_date	TEXT	—
best_date_source	TEXT	—
date_confidence	TEXT	—
year	INT	—
burst_id	TEXT	—
saturation	REAL	—
is_bw	INT	—
fb_id_year	INT	—
time_is_midnight	INT	—
immich_local_date	TEXT	—
immich_year	INT	—
person_names	TEXT	—
immich_albums	TEXT	—
immich_favorite	INT	—
sha256	TEXT	—

photo_date_applied — The date actually APPLIED to each photo, with its source and confidence — and prev_date/prev_xmp_b64, the previous state, which is what makes it reversible.

Field	Type	Notes
asset_id	TEXT	—
rel_path	TEXT	—
prev_date	TEXT	—
prev_xmp_b64	TEXT	—
new_date	TEXT	—
source	TEXT	—
confidence	TEXT	—

photo_dating_evidence — All the date EVIDENCE gathered per photo, each source in its own column (EXIF, Takeout, email, filename, Immich, filesystem…). Nothing decided here — this is the raw material for the rule of the minimum.

Field	Type	Notes
asset_id	text	PK
rel_path	text	—
immich_date	text	—
takeout_ts	int	—
email_date	text	—
exif_dt	text	—
name_date	text	—
fb_year	int	—
fs_min	text	—
person_cap	text	—
new_date	text	—
source	text	—
confidence	text	—
flag	text	—

photo_dedup — Duplicate detection by sha256: which copy is kept and which zone each one lives in.

Field	Type	Notes
status	TEXT	—
zone	TEXT	—
kind	TEXT	—
path	TEXT	—
bytes	TEXT	—
sha256	TEXT	—

photo_email — Photos found as email attachments: sender, date and subject of the message that carried them — often the only surviving date for old ones.

Field	Type	Notes
saved_path	TEXT	—
sender	TEXT	—
date	TIMESTAMP	—
subject	TEXT	—
original_name	TEXT	—
bytes	INTEGER	—
sha12	TEXT	—
maildir	TEXT	—

photo_fb_album_names — Facebook album id → its name. Kept apart because the export splits them.

Field	Type	Notes
fbid	TEXT	—
name	TEXT	—

photo_fb_albums — Facebook albums as exported: folder, id and representative photo.

Field	Type	Notes
idx	TEXT	—
folder	TEXT	—
fbid	TEXT	—
count	TEXT	—
rep	TEXT	—
w	TEXT	—
h	TEXT	—
mp	TEXT	—
guess	TEXT	—

photo_ocr — Text read from images (OCR). What lets a screenshot be searchable by its content.

Field	Type	Notes
file	TEXT	—
text	TEXT	—

photo_takeout_json — RAW layer of the Google Takeout sidecars (photoTakenTime, coordinates). Google's own claim, imported as-is and never edited.

Field	Type	Notes
title	TEXT	—
photoTakenTime_ts	TEXT	—
lat	TEXT	—
lon	TEXT	—
json_path	TEXT	—

photo_timeline — Each photo's assigned period/category, with confidence. The curated result of the dating.

Field	Type	Notes
asset_id	TEXT	—
rel_path	TEXT	→ photo_assets
category	TEXT	—
confidence	TEXT	—

photo_triage — Photos assigned to a review album — the working queue of what still needs a human eye.

Field	Type	Notes
album	TEXT	—
asset_id	TEXT	—
rel_path	TEXT	→ photo_assets

CHAT / MESSAGING (Matrix-normalised)

chat_events — Every message as an EVENT, the Matrix way: an append-only log rather than an editable table, so a chat's history is what happened, in order.

Field	Type	Notes
event_id	TEXT	PK
room_id	TEXT	NOT NULL, → chat_rooms
sender	TEXT	NOT NULL
origin_ts	INTEGER	NOT NULL
type	TEXT	NOT NULL
msgtype	TEXT	—
content	TEXT	NOT NULL
media_sha	TEXT	—
source	TEXT	NOT NULL
sha	TEXT	NOT NULL

chat_family_prompted — (no description yet)

Field	Type	Notes
room_id	TEXT	PK
prompted_at	TEXT	—

chat_rooms — The CONVERSATIONS (Matrix room model): one row per chat, individual or group. person_id links a room to a person in people_persons — the only place that link is stored.

Field	Type	Notes
room_id	TEXT	PK
platform	TEXT	NOT NULL
kind	TEXT	NOT NULL
name	TEXT	—
person_id	INTEGER	—

chat_senders — Who sends in each room. Kept apart from people_persons because a sender is a phone number until someone decides which person it is.

Field	Type	Notes
sender_id	TEXT	PK
platform	TEXT	NOT NULL
display_name	TEXT	—
person_id	INTEGER	—

INVENTORY

inv_inventory — Filesystem INVENTORY: one row per file with its path, size, dates and probable type. The raw scan the archive's curation works from.

Field	Type	Notes
absolute_path	TEXT	PK
root_path	TEXT	—
relative_path	TEXT	—
name	TEXT	—
extension	TEXT	—
size	TEXT	—
created_at	TEXT	—
modified_at	TEXT	—
probable_type	TEXT	—
mime_type	TEXT	—
sha256	TEXT	—
hash_status	TEXT	—
width	TEXT	—
height	TEXT	—
status	TEXT	—
reason	TEXT	—

DECLOUD PROGRESS

decloud_areas — The decloudification areas (email, photos, calendar…), each with its weight and status. What the global percentage is computed from.

Field	Type	Notes
id	INTEGER	PK
area	TEXT	NOT NULL, UNIQUE
category	TEXT	NOT NULL
weight	INTEGER	NOT NULL
percent	INTEGER	NOT NULL
status	TEXT	—
sort_order	INTEGER	NOT NULL

decloud_snapshots — Historical snapshots of that percentage, to see the trend over time.

Field	Type	Notes
id	INTEGER	PK
ts	TEXT	NOT NULL
global_pct	REAL	NOT NULL
areas_json	TEXT	—

DB EXPLORER (own bookkeeping)

dbexp_tables — DB Explorer's own map: row count per table and which apps own/read/write it. Recomputed from the manifests; it holds no data of yours.

Field	Type	Notes
table_name	TEXT	PK
calc_num_rows	INTEGER	—
calc_last_refreshed	TEXT	—
calc_read_apps	TEXT	—
calc_write_apps	TEXT	—
calc_own_apps	TEXT	—
calc_is_system	INTEGER	NOT NULL

META

_dominios — Legend: domain prefix → description and source DB.

Field	Type	Notes
prefijo	TEXT	PK
dominio	TEXT	—
source	TEXT	—
n_tablas	INTEGER	—

OTHER

_migrations — Which migrations each app has already applied. Prevents re-running one and makes an install idempotent.

Field	Type	Notes
app_slug	TEXT	NOT NULL, PK
migration_id	TEXT	NOT NULL, PK
applied_at	TEXT	NOT NULL

aplexp_bookmarks — (no description yet)

Field	Type	Notes
url	TEXT	PK
title	TEXT	—
folder	TEXT	—
added_at	TEXT	—
source	TEXT	—

aplexp_calendar — (no description yet)

Field	Type	Notes
uid	TEXT	PK
kind	TEXT	—
summary	TEXT	—
starts_at	TEXT	—
ends_at	TEXT	—
all_day	INTEGER	—
completed	INTEGER	—
location	TEXT	—
source	TEXT	—

aplexp_contacts — (no description yet)

Field	Type	Notes
uid	TEXT	PK
full_name	TEXT	—
org	TEXT	—
emails	TEXT	—
phones	TEXT	—
note	TEXT	—
source	TEXT	—

mail_matches — (no description yet)

Field	Type	Notes
id	INTEGER	PK
rule_id	INTEGER	NOT NULL, → mail_rules
message_id	TEXT	NOT NULL
from_addr	TEXT	—
subject	TEXT	—
date_iso	TEXT	—
matched_at	TEXT	NOT NULL

mail_rules — (no description yet)

Field	Type	Notes
id	INTEGER	PK
from_pattern	TEXT	—
subject_pattern	TEXT	—
label	TEXT	NOT NULL
priority	INTEGER	NOT NULL
created_at	TEXT	NOT NULL
created_by	TEXT	—

sync_applied_ops — Queue requests from the home server already applied on the workstation. The server replica cannot write the authoritative database, so it queues intent for the workstation to apply — this table stops one request being applied twice.

Field	Type	Notes
op_id	TEXT	PK
kind	TEXT	—
applied_at	TEXT	—

sys_journal_ops — (no description yet)

Field	Type	Notes
id	INTEGER	PK
op_run	TEXT	NOT NULL
seq	INTEGER	NOT NULL
ts	TEXT	NOT NULL
kind	TEXT	NOT NULL
target	TEXT	—
before	TEXT	—
after	TEXT	—
because	TEXT	—
status	TEXT	NOT NULL

sys_journal_runs — (no description yet)

Field	Type	Notes
op_run	TEXT	PK
ts	TEXT	NOT NULL
tool	TEXT	—
descr	TEXT	—
status	TEXT	NOT NULL