#Data model

MariaDB, InnoDB, utf8mb4. Migrations live in src/migrations.j and are applied by sqlmigrate, which records them in schema_migrations.

#Tables

#The joins that carry the model

users has no household. A person is global and unique by username; household_members carries the per-family role. That is what lets a child belong to two families and a person hold different roles in each.

expense_shares names two people. beneficiary_user_id (nullable) is the child the portion is for; bearer_user_id is who carries it. Combined with expenses.payer_user_id, those three columns produce every settlement case - see Domain model.

notification_settings hangs off the pair, the address off the person. users.email is the mailbox - one person has one, whichever families they are in - while what they want to hear about is a row per (household_id, user_id, kind). Somebody may want everything about the family they run and nothing about the one they are merely a parent in. A missing row means the class default, not "off", so a class added to core/notify.j later starts at its default for everyone rather than arriving switched off for exactly the people who once saved their settings.

settlement_items is what a transfer paid. A transfer that covers five expenses has five items. The part of a transfer with no items is an advance, and that subtraction is the whole of the wallet.

auto_cents is the part of an item nobody booked. A float settles a new expense the moment it is accepted; settlements.applyAvailableCredit records what it allocated in auto_cents as well as in amount_cents. Everything that asks "has money moved for this expense" compares the two: expenses.isLocked is settled > auto, and hasSettlement looks for amount_cents > auto_cents. So an entry paid out of an advance stays correctable - the allocation is given back by settlements.releaseAutomatic and re-applied afterwards - while an entry somebody actually paid for is as frozen as it ever was. Without the split, fixing a typo in a 12,00 expense meant reversing the 890,00 transfer the float came from.

settlements.purpose_category_id dedicates money to one kind of cost. A payment with a purpose clears only open shares of expenses in that category, and its remainder waits as a credit only that category may draw on (ledger.creditCovers). Dedicated credits are also offered to the allocator before general ones: if the general float paid a rent share, the rent money would be left with nothing it is allowed to buy. The match is on the expense's current category, so re-categorising an expense moves it into or out of reach and the callers re-run the allocation.

audit_log is per family and off by default. households.audit_enabled decides whether audit.record writes anything at all, so a family that has not asked for a trail carries no rows. Both user columns are nullable and their foreign keys are ON DELETE SET NULL: a deleted account must not take the record of what it did with it. A NULL actor_user_id therefore means no account stands behind this change - one since removed, or something done with familienkonto at the shell, where the summary ends in (Kommandozeile). acting_for_user_id is set when somebody was acting as another person, which is the one case where "who did this" has two answers. summary is German prose written at the time of the change rather than rebuilt from ids on display - rebuilding it later would describe the books as they are now, which is precisely the question the log exists to answer.

#Columns worth knowing about

Exchange rates are integers in millionths (1.085432 → 1085432), so a conversion is exact and reproducible.

#Strict SQL mode

Every connection runs under:

STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION

This is applied through the DSN, not with a SET SESSION after connecting. Go's database/sql keeps a connection pool and hands out whichever connection is free, so a statement run on one says nothing about the others. A DSN parameter is applied by the driver to every connection it opens.

db.withStrictMode appends it to whatever DSN you configure, leaving an explicit sql_mode= alone. db.isStrict lets a caller verify rather than assume; familienkonto doctor reports it.

Without it, an over-long merchant name would be silently truncated and a malformed amount would become zero. For accounting data that is not a preference.

Do not put parseTime=true in the DSN. It makes the driver return DATE columns as RFC 3339 timestamps. Dates are handled as plain strings throughout; db.dateOf normalises defensively.

#Transactions

db.begin / commit / rollback / executeIn / insertIn. Used where a write is only meaningful as a unit - a settlement and the items recording what it paid must both exist or neither, or a balance is quietly wrong with nothing to detect it. db.rollback is safe as an errdefer.

#Migrations

familienkonto migrate status
familienkonto migrate up
familienkonto migrate down --steps 1

Versions are zero-padded and applied in lexical order, each in its own transaction. migrate up is idempotent.

A new household is given the standard categories by users.createHousehold itself - a family without categories cannot record a single expense, so it is not left for someone to notice later.

That was not always true, and migration 003 is the consequence. 002 seeded a household called Familie purely so the default categories had an owner. Once createHousehold began seeding its own, the seeded household became a family that belonged to nobody and held nothing - visible as an empty row in every list of families, including the administration page. 003 removes it.

Every statement in 003 is guarded on the household still having the name 002 gave it and having nothing at all hanging off it: no members, expenses, incomes, settlements or imports. An installation where somebody has adopted id 1 as a real family keeps it, and the migration still counts as applied - it is a tidy-up, not a demand. src/migrations_test.j covers both outcomes.