The database

Everything NoCloudMe 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.

This page is generated

The tables below are read from the live database schema every time this manual is built — so it can't drift from reality. Only the per-table purpose is hand-written.

How to read it

Domain prefix. Every table starts with its domain: app_, fin_, people_, home_, photo_, health_, tax_, chat_, inv_. 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	Purpose
APP (product: users, sections, languages)
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.
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 emoji/description shown on its tile.
app_timezones	DIMENSION of selectable timezones (IANA names). What turns a stored UTC timestamp into the hour you actually read.
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.
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.
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 Pi's ajustes.json; this table is the normalized projection of it.
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.
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_movimientos), which stays untouched — see docs/FIN_DOUBLE_ENTRY_PLAN.md.
fin_cartera_fiscal	Tax lots (basis of the capital-gains FIFO) with costs: comision/gasto_divisa/otros_gastos.
fin_categories	DIMENSION of income/expense categories (PK=code; ISO 20022 purpose_code + own subcode + merchant).
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.
fin_cotizaciones	Latest quote per ticker/broker. precio=fundamental (market); calc_valor_eur=derived (shares×price×fx).
fin_cotizaciones_hist	Close-price history downloaded from Yahoo (external source, not computed).
fin_crypto_trades	Crypto trades. Generic exchange (Kraken today; extensible to other exchanges).
fin_cuenta_alias	Crosswalk: statement text → fin_cuentas (per source).
fin_cuentas	DIMENSION of own accounts (bank
fin_docs	Ingested financial documents (dedupe by sha).
fin_facturas	Utility invoices (provider/period/amount/kwh).
fin_manual_entries	One-off income/expense facts with no fin_movimientos 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.
fin_movimientos	UNIFIED bank cash book (every connected account/source merged; a source's internal envelope, if any, goes in origen) — 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.
fin_ops_broker	Broker operations (BUY/SELL of securities), canonical (import_ops_broker.py).
fin_posiciones	Current portfolio positions (from connected broker/bank captures).
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_cotizaciones_hist (Yahoo's daily closes). Was calc_pos_hist: calc_ marks derived FIELDS, not a domain, and this is finance.
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.
fin_rules	Concept→category classification rules.
fin_saldos	Balance snapshots per account (available/booked) — from bank captures.
fin_splits	Stock splits (ratio per date) to adjust historical share counts.
fin_suecia_acciones	Sweden years: stock capital gains per year (K4). calc_resultado_sek=sale−cost.
fin_suecia_ingresos	Sweden years: ANNUAL tax summary from that jurisdiction's own tax authority. sek fundamental; calc_eur derived (×fx).
fin_suecia_saldos	Sweden years: balance snapshots (SEB/Avanza) at Dec-31.
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.
fin_valor_alias	Crosswalk: statement text → fin_valores (per source).
fin_valores	DIMENSION of securities (name/ticker/isin/currency/market).
TAXES
tax_declarations	Spain IRPF returns (Modelo 100) reconciled against form 0424. (Skatteverket lives in fin_suecia_*.)
HEALTH
health_conditions	Diagnosed conditions, with date, current status and who diagnosed them.
health_documents	Index of health documents (reports, tests) — the path on disk, never the content.
health_measurements	Time series of every measurement (weight, blood pressure, glucose…): metric says which, value/value2 hold it (two for pairs like systolic/diastolic).
health_prescriptions	Prescribed medication: dose, schedule and whether it is still active.
health_profile	Key/value with the basics of the health profile (blood type, allergies…). One row per fact, so adding one needs no migration.
health_screen_time	Screen time per app and day, from Apple Screen Time. Seconds, per device.
health_visits	Medical appointments: specialty, doctor, place and reason.
PEOPLE & GENEALOGY
people_email_stats	RAW pool: person↔addresses with mail counts (to curate → people_person_emails).
people_entities	RAW pool: entities (people/companies) extracted from mail.
people_faces	RAW pool: detected-face index (idx ↔ Immich person id).
people_immich_counts	RAW pool: Immich person id with photo count.
people_name_contexts	RAW pool: name contexts.
people_person_emails	1:N dimension — a person's email addresses.
people_person_faces	1:N dimension — Immich faces (person_id) linked to a person.
people_person_phones	1:N dimension — a person's phone numbers.
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).
people_work_contexts	RAW pool: work contexts.
HOME (house + network)
home_devices	Network/home-automation devices (MAC, IP, kind, room).
home_plan_arcs	Arcs of a circle on the plan — a door's swing, mostly.
home_plan_areas	Polygons filled on the plan (rooms and zones drawn as an area, not as walls).
home_plan_categories	DIMENSION of what a plan line can be (wall, door, window…), grouped by grp. Drives its colour and meaning.
home_plan_circles	Circles on the plan (columns, round elements).
home_plan_hinges	Hinge points: where an arc pivots, i.e. which side a door opens from.
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.
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.
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.
home_plan_points	Points/icons positioned on the plan (px).
home_property	The home (global plan/surface metadata).
home_room_sides	Which lines make up each room's outline, in order. calc_rms is derived (fit against the orientation grid).
home_rooms	Rooms with plan polygon. plan_area_m2=FUNDAMENTAL (brochure); calc_area_m2/calc_area_delta_pct=derived.
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.
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.
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.
photo_dedup	Duplicate detection by sha256: which copy is kept and which zone each one lives in.
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.
photo_fb_album_names	Facebook album id → its name. Kept apart because the export splits them.
photo_fb_albums	Facebook albums as exported: folder, id and representative photo.
photo_ocr	Text read from images (OCR). What lets a screenshot be searchable by its content.
photo_takeout_json	RAW layer of the Google Takeout sidecars (photoTakenTime, coordinates). Google's own claim, imported as-is and never edited.
photo_timeline	Each photo's assigned period/category, with confidence. The curated result of the dating.
photo_triage	Photos assigned to a review album — the working queue of what still needs a human eye.
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.
chat_rooms	The CONVERSATIONS (Matrix room model): one row per chat, individual or group. persona_id links a room to a person in people_persons — the only place that link is stored.
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.
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.
DECLOUD PROGRESS
decloud_areas	The decloudification areas (email, photos, calendar…), each with its weight and status. What the global percentage is computed from.
decloud_snapshots	Historical snapshots of that percentage, to see the trend over time.
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.
META
_dominios	Legend: domain prefix → description and source DB.