tane/docs/design/data-model.md

Tane — Data model (v0 draft)

Technical spec feeding the Flutter/Drift implementation. Written in English (project convention). Implements the concepts discussed in data-notes.md and PLAN §3 / §5-bis.

Status: draft for discussion. Nothing here is frozen; it is the starting point for the first Drift schema (schemaVersion = 1).

Core/domain split. This model is presented flat for readability. Which of these tables live in the generic engine (commons_core) vs. the seeds domain (app_seeds) is decided in core-domain-boundary.md. In short: Variety, Lot, Movement, Species and the seed-specific fields are domain; Offer, Party, SeedBank→Group, trust, identity and the signed-return promise are core. Two entities below are the seeds view of a generalized core concept — see the notes on Movement (§2.4) and Plantare (§2.7).

0. Design constraints (why the model looks like this)

• Local-first & offline. The whole model lives in local SQLite. No server is required. Online only enriches.
• CRDT-ready from day one. Every mutable row carries the metadata needed to merge across devices/peers later, even before sync is built. See §4.
• Progressive disclosure. Only Variety.label is mandatory. Everything else is optional. The schema must never force a field the UI hides.
• Three levels: identity / batch / event. Variety (what it is) → Lot (a batch you hold) → Movement (append-only log). This makes year, in/out, dates and germination fall into place.
• Append-only ledger. Movement rows are immutable events. This is both the bank history (mockup HISTORY tab) and the provenance/Plantare chain (§5-bis).

1. Conventions for every table

All identifiers, column names, comments and commit messages are in English. Shared columns on every mutable entity:

Column	Type	Purpose
id	TEXT (UUIDv7)	Client-generated primary key. Never auto-increment (would collide across peers). UUIDv7 sorts by time.
created_at	INTEGER (ms)	Creation timestamp.
updated_at	TEXT (HLC)	Hybrid Logical Clock stamp of last change — drives last-writer-wins merge.
last_author	TEXT	Public key / device id of who last wrote (for CRDT + trust).
is_deleted	BOOLEAN	Soft delete only. Never DELETE FROM; tombstones must survive to merge correctly.
schema_row_version	INTEGER	Per-record format version for distributed migration (§5).

Movement is the exception: it is append-only and immutable, so it needs no updated_at/is_deleted (a correction is a new compensating event).

2. Entities

2.1 Variety — the identity (accession)

What you saved; one row per distinct thing in your inventory.

Column	Type	Notes
id	UUID
label	TEXT (required)	Your own name: "el tomate de la abuela". The only mandatory field.
species_id	UUID → Species	Nullable. Links to the bundled catalog; unlocks scientific name + Wikidata.
cultivar_name	TEXT	Nullable. 'Marmande', 'Cherokee Purple'. Free text — landraces aren't in any authority.
category	TEXT/enum	Nullable. Botanical family or user group, for the inventory sections.
notes	TEXT (markdown)	Nullable. Free personal notes.
(+ common columns)

Related (separate tables so concurrent edits merge as sets):

• VarietyVernacularName(id, variety_id, name, language, region) — the multiple common names.
• Attachment(id, parent_type, parent_id, uri, kind) — photos/docs (kind: photo, doc). Polymorphic parent.
• ExternalLink(id, parent_type, parent_id, url, title) — any pasted URL (Wikipedia, forum…).

2.2 Species — bundled name catalog (mostly read-only)

The lightweight offline catalog (Wikidata CC0 + GBIF CC-BY). Users may add local entries.

Column	Type	Notes
id	UUID
scientific_name	TEXT	Solanum lycopersicum.
wikidata_qid	TEXT	e.g. Q23501. Anchor to derive Wikipedia/image/GBIF online.
gbif_key	INTEGER	Nullable. GBIF taxon key.
family	TEXT	Nullable.
is_bundled	BOOLEAN	True = shipped with app (don't sync); false = user-added (syncs).

Localized common names for the catalog live in SpeciesCommonName(species_id, name, language) and are bundled too.

2.3 Lot — a batch you hold

Zero or more per Variety, each with its own year and its own unit — and units may differ between lots of the same variety. A single maize variety can hold, at once: [2 cobs · 2024], [a few loose seeds · 2023], [a cup · 2022]. Even the same variety+year in two forms (2 cobs and loose kernels from 2024) is just two lots. A Lot = one homogeneous batch. This is exactly why quantity and unit live here, not on Variety.

Column	Type	Notes
id	UUID
variety_id	UUID → Variety
harvest_year	INTEGER	Nullable. This is where "year" lives (germination decays with lot age).
type	enum LotType	The form held — see §2.3.2.
quantity_label	TEXT + quantity_kind	Qualitative by default. Not only kitchen units — see §2.3.1.
quantity_precise	REAL + quantity_unit	Optional precise amount (grams / seed count).
presentation	enum Presentation	Nullable. How living material is packaged — see §2.3.2.
storage_location	TEXT	Nullable. Free text with suggestions ("fridge", "village bank").
offer_status	enum	private / shared / exchange / sell — the visibility from PLAN §3 Layer 2. Per lot.
seedbank_id	UUID → SeedBank	Nullable. If this lot belongs to a collective bank.
(+ common columns)

GerminationTest(id, lot_id, date, sample_size, germinated_count, notes) — optional; percent is derived (germinated_count / sample_size). One-to-many so history of tests is kept.

2.3.1 Quantity units — qualitative and plant-aware (design note)

Quantity is deliberately rough and human, not precise. But the informal vocabulary must go beyond kitchen measures ("cup", "pinch", "packet") — those feel too "andar por casa" and don't fit many seeds. Seeds are naturally counted in the forms the plant gives them. So quantity_kind is an extensible, i18n, grouped list, and the app suggests the relevant units from the linked Species/category (never forces them):

• Informal / generic: a few, some, plenty, a handful, a pinch, a jar, a packet/envelope ("sobre").
• Plant-form natural units: a cob (mazorca, maize), a flower head (cabezuela, sunflower), a pod (vaina, legumes), an ear/spike (espiga, cereals), a fruit's worth (tomato, pepper, squash), a bulb, a tuber, a seed head, a bunch.
• Precise (optional): grams or seed count (quantity_precise + quantity_unit).

Modelling: store quantity_kind (a stable enum key like pod, cob, head, packet, handful, grams, count) + an optional numeric quantity_precise (and a free-text quantity_label for a rough amount when no key fits). The display label is localized from the key, so "pod"→"vaina"/"beina"… Units are labels for a rough amount, generally not convertible between each other or to grams — and that's fine. Suggestion logic (e.g. Poaceae → ear/cob, Fabaceae → pod, Helianthus → flower head) lives in the catalog mapping, not hardcoded, so it stays extensible and translatable. To think through further: the full starter list per family, and whether users can add their own unit keys (probably yes, as free quantity_label text when no key fits).

2.3.2 Lot form (LotType) and packaging (Presentation) — design note

A lot isn't always dry seed. Reviewing the vocabulary of real seed shops and nurseries (viveros) — where "plantel" is only the seedbed/batch, not a unit — the form a lot is held in is a small, append-only enum LotType (stored by name, data-model §5):

• seed — dry seed. The only form germination tests apply to.
• seedling — a young plant grown from seed, to transplant (plantón / plug).
• plant — an established plant (potted or bare-root).
• tree — a woody plant: tree or shrub.
• bulb — a storage organ: bulb, corm, tuber or rhizome.
• cutting — vegetative propagation material (cutting, scion).

Form drives the offered units, and only seed gets the rich plant-form scale of §2.3.1; living forms are counted as whole individuals (seedling, plant, tree, cutting; bulb also allows grams). The packaging is deliberately a separate optional attribute Presentation (pot, tray, plug, bareRoot, rootBall) so "how many" is never conflated with "in what format" — that conflation was the original flaw (the old pot/tray "amounts", now retained only for old rows). Presentation is offered only for seedling/plant/tree. Left open: grafts/divisions/forestry seedlings (nicho) fold into cutting/plant for now, or a free note; revisit if real exchanges demand their own key.

2.4 Movement — the append-only event log

Immutable events on a Lot. Entries/exits are just types. This is the history tab and the provenance chain.

The append-only mechanic is the generic LedgerEntry of commons_core (see core-domain-boundary.md §2, §4.3); Movement is its seeds-domain shape. At v0 it lives whole in app_seeds (per that doc's §8 conservative start); it may be split later.

Column	Type	Notes
id	UUID
lot_id	UUID → Lot
type	enum	received, given, sown, harvested, germination_test, split, discarded…
occurred_on	DATE	The date (in/out/sowing…).
counterparty_id	UUID → Party	Nullable. From/to whom (for received/given).
quantity_label / quantity_precise		How much moved.
parent_movement_id	UUID → Movement	Nullable. Links a lot to the exchange it came from → provenance DAG.
plantare_id	UUID → Plantare	Nullable. A given may carry a Plantare.
notes	TEXT
created_at, last_author, schema_row_version		(no update/delete — append-only)

2.5 Party — a person or collective you exchange with

Column	Type	Notes
id	UUID
display_name	TEXT
public_key	TEXT	Nullable. The person's single identity key (Duniter/Ğ1-style; a derived subkey serves Nostr transport). Layer 3+.
kind	enum	person / collective.
note	TEXT

2.6 SeedBank — a collective bank (optional, Layer 2–3)

SeedBank(id, name, note) + SeedBankMember(seedbank_id, party_id, role). A Lot may point to a SeedBank. Kept minimal now; expands with sharing.

2.7 Plantare — the signed seed-IOU (§5-bis)

The digital version of the paper Plantare. Bilateral, signed, held by both parties.

Plantare is the seeds-domain name for the generic Pledge (return promise) of commons_core — see core-domain-boundary.md §2, §5. The generalization uses Pledge.return_kind = similar for seeds (return something alike) vs. same_item for a lent tool. The columns below map onto that core entity.

Column	Type	Notes
id	UUID
movement_id	UUID → Movement	The given event it accompanies.
debtor_key	TEXT	Who received and owes a return.
creditor_key	TEXT	Who gave.
owed_description	TEXT	"a similar amount of free seed".
due_date	DATE	Return-by date.
debtor_signature / creditor_signature	TEXT	Cryptographic signatures (both stubs of the paper).
status	enum	open / returned / forgiven. Framed as a promise, not a debt (microcopy).

Shipped vs. planned. app_seeds ships the local, one-sided version (variety link + dates + direction/counterparty free text); the key/signature/movement_id columns and the two-party handshake are deferred. Full spec of the bilateral signed record: plantare-bilateral.md.

2.8 Offer — a published, discoverable listing

The shop window: a signed statement published to the network so others can discover what you share. Distinct from Lot on purpose — publishing reveals only what you choose (privacy). Serializes to Nostr NIP-99 (kind:30402) or ActivityPub. Full rationale in sharing-model.md.

Column	Type	Notes
id	UUID
lot_id	UUID → Lot	Nullable (offer without exposing a lot; wanted needs none).
variety_summary	TEXT/JSON	Only the name + photo ref you choose to publish.
offer_type	enum	gift / exchange / sale / wanted.
price_amount	DECIMAL	Nullable (only sale). No in-app payment — price is informational; money changes hands off-platform.
price_currency	TEXT	Nullable. ISO 4217 or a local/community currency.
price_negotiable	BOOLEAN
exchange_terms	TEXT	Nullable (for exchange).
approx_geohash	TEXT	Low precision only.
radius_km	INTEGER
status	enum	active / reserved / closed.
published_at / expires_at		Seasonality / freshness.
transport_ref	TEXT	Relay event id (NIP-99) / AP object id.
author_key	TEXT	Publishing public key.
(+ common columns)

A closed deal may produce a Movement (given) and, if it is an exchange with a return promise, a Plantare. The app never processes payments, takes commissions, or holds funds (keeps Tane neutral infrastructure, not a central market operator).

3. Entity map

Species (bundled) ──< Variety ──< Lot ──< Movement (append-only) >── Party
                         │           │  │       │
              VernacularName         │  Offer   └── Plantare
              Attachment/Link   GerminationTest
                                     │
                                 SeedBank

Movement.parent_movement_id forms the cross-device provenance DAG. Offer is published (NIP-99/AP) and, when a deal closes, spawns a Movement (and optionally a Plantare).

4. CRDT strategy (merge semantics)

Chosen libraries (PLAN §3): crdt_lf / crdt_sync on top of Drift. Merge rules by data shape:

• Scalar fields (label, harvest_year, offer_status…) → LWW register keyed by HLC updated_at. Simple, good enough for personal edits.
• Collections (vernacular names, attachments, links, seedbank members) → OR-Set (observed-remove set) so concurrent adds on two phones both survive.
• Movement log → grow-only set (append-only). No conflicts possible; corrections are new events. This is why the ledger is the safest part to sync first.
• Identity: client-generated UUIDv7; deletes are tombstones (is_deleted), never physical.

Adoption order (avoid CRDT-everything at once): sync Movement first (append-only, trivial), then Lot/Variety (LWW), then the set-typed relations.

5. Migration — two axes (this is the Liquibase/Flyway question)

5.1 Local schema migration → Drift, natively (the Flyway/Liquibase equivalent)

Drift ships versioned, testable migrations — exactly the requested capability:

• schemaVersion integer on the database; bump on every schema change.
• Step-by-step migrations: dart run drift_dev schema steps drift_schemas/ lib/db/schema_versions.dart generates from1To2, from2To3… functions.
• Versioned schema exports stored in drift_schemas/ (drift_schema_v1.json, v2.json…) — the migration history, like Flyway's versioned scripts, committed to git.
• Migration tests: dart run drift_dev schema generate builds old-version DBs; validateDatabaseSchema asserts the migrated schema matches a fresh one. Run in CI.

Rule: never edit a released schema version; always add a new one with a forward migration. Keep every drift_schema_vN.json in the repo.

5.2 Distributed / wire migration → compatibility discipline (no tool can do this for you)

Because peers run different app versions, the sync payload must stay backward+forward compatible. This is the harder axis and is a convention:

• Additive, optional-only changes to synced records → both forward and backward compatible. Never repurpose a field's meaning.
• schema_row_version per record; readers ignore unknown fields and fill missing ones with defaults.
• Soft deletes / tombstones always (already in the model).
• Never reuse or renumber enum values; only append new ones, and tolerate unknown enum values on read (map to a safe default).
• A new required concept ships as a new optional table/field, promoted to "expected" only after most peers have upgraded.

Together: Drift handles the local database migration; the compatibility rules handle the distributed data migration. Both are needed for a P2P local-first app.

6. Open questions (decide before freezing schemaVersion = 1)

• quantity modelled once and reused on Lot and Movement, or duplicated? (Lean: a shared embedded type.)
• Is offer_status on Lot enough, or does a Variety-level default help? (Lean: Lot only.)
• Do we need Variety-level provenance, or is per-Lot/Movement provenance sufficient? (Lean: Movement DAG is enough.)
• category as free enum vs linked to Species.family when a species is set. (Lean: free, prefilled from family when available.)

7. Interchange export/import (Phase 1) — decided & implemented

The user-triggered interchange export (Settings → Backup). Distinct from the future encrypted full backup (.tane, backup-and-recovery.md mechanism 1): the interchange files are the only plaintext the app ever writes, and only because the user explicitly asks for them. Import reads the picked file straight into memory — no temp copies.

Two formats, one canonical:

• JSON — canonical, versioned, importable; a precursor of the future sync payload. Envelope: formatVersion (currently 1), schemaVersion (the writer's DB generation), appId: "tane", then one array per table: varieties, lots, vernacularNames, externalLinks, germinationTests, movements, parties, attachments. Every row carries its sync metadata verbatim (id, createdAt, packed-HLC updatedAt, lastAuthor, schemaRowVersion); enums serialize by name. Photos/docs are embedded as base64 (bytesBase64) — the JSON export is the complete copy of the data (~33% size overhead accepted). Tombstones are excluded: this is a user-facing export of the live inventory, not the sync wire format (real sync, Block 2, will carry tombstones on its own channel).
• CSV — export-only, for spreadsheets. One row per Lot (a variety with no lots gets one row); vernacular names and links joined into single columns; latest germination rate derived; ISO-8601 dates; never any photo bytes. Not importable — the JSON is.

Species are not exported. The bundled catalog rows get per-install ids, so species_id is not portable. Each exported variety carries a denormalized speciesScientificName; the importer re-resolves it against its own catalog by scientific name (no match → species_id stays null).

Import = merge by id (UUIDv7), never duplicating. Mutable rows: unknown id → insert preserving the original metadata (no re-stamping); known id → last-writer-wins on the packed HLC updatedAt (§4), incoming wins only if strictly newer — so re-importing the same file is a no-op, and a locally-deleted row only resurrects if the incoming version is newer than the tombstone. movements are append-only: insert-if-unknown, never updated. Everything runs in one transaction; afterwards the local HLC clock receiveEvents the newest imported stamp so the next local write out-stamps everything imported.

Reader compatibility (per §5.2): unknown JSON fields are ignored; unknown enum values map to a safe default (lot.type → seed, offer_status → private) or drop the row when none exists (an unknown movement.type); a formatVersion newer than the reader supports fails with a clear "update the app" error. formatVersion changes must be additive and documented here.

Implementation: apps/app_seeds/lib/data/export_import/ (codecs + reconciler), VarietyRepository.exportInventory()/importInventory(), lib/services/export_import_service.dart.