Data model

Principles

PocketBase owns mutable instance/user records.
references.db owns replaceable public/reference catalogs.
Private media bytes live outside PocketBase; PocketBase stores encrypted metadata and opaque file references.
All user-owned objects include immutable owner_id assigned by the server.
Records support stable IDs, created_at, updated_at, optional deletion tombstones, sync version, and source provenance.
Sensitive payloads are encrypted before PocketBase persistence; indexed/searchable non-sensitive fields are minimized.

PocketBase logical collections

Identity and instance

users: email identity, role (administrator|user), status, locale, units, timezone, encrypted profile fields.
invitations: hashed token, email/role, expiry, inviter, consumed/revoked state.
passkeys: user, credential ID, public key, counters, transports, label, timestamps.
totp_credentials: encrypted secret metadata and activation state.
recovery_codes: hashed code and consumed timestamp.
devices: user, public signing key, name, platform/model, pairing method, status, last activity/sync.
sessions: user/device, hashed refresh-token family data, expiry, rotation/reuse state.
instance_settings: encrypted SMTP/settings, photo policy, session/audit policy, setup state.
audit_events: normalized security-only events.

Sources and synchronization

data_sources: Health Connect provider/application, BLE scale, manual, import, calculated.
sync_cursors: per user/device/source cursor and checkpoint.
sync_operations: idempotency key, mutation type, object, version, status.
sync_errors: sanitized retryable/permanent failure data.

Health and activity

body_measurement_sessions: measured UTC instant, source timezone, source, encrypted notes/context.
body_measurement_values: session, definition code, canonical value/unit, original value/unit, side, quality/origin (measured|estimated|manual|imported|calculated).
weight_records: source identifiers, measured time, canonical weight, impedance when available, provenance.
body_composition_records: linked source/weight, derived values, algorithm/version, estimated flag.
heart_rate_series: start/end, source, encrypted compressed sample block rather than one row per sample.
sleep_sessions: interval, source, summary; sleep_stage_blocks for compact stages.
daily_activity_summaries: date/timezone/source, steps, distance, calories and other totals.
workout_sessions: type, interval, duration, distance, calories, average/max heart rate, power, cadence, source identity.
workout_sample_blocks: compact timestamp-offset samples for heart rate, speed, power, cadence, distance.
source_associations: non-destructive linkage between Kinomap workouts and compatible Pixel/Fitbit heart-rate series.

media_objects: owner, purpose, encrypted storage ID/metadata, MIME, dimensions, bytes, checksum, key/schema version.
progress_photos: measurement session, media object, view type, ordering, encrypted notes.
avatars: user, full and thumbnail media references.
shares: owner, selected records/media, scope, expiry/revocation. Public-by-link sharing is not required in V1.
exports: owner, requested scope, status, expiry, encrypted artifact reference.

Future private features

training_programs, program_sessions, exercise_entries for user-specific plans and performance.
nutrition_entries, meals, user_foods for private nutrition data.

Measurement definitions

Stable codes, never translated labels, identify values: neck_circumference, shoulder_circumference, chest_circumference, waist_circumference, abdomen_circumference, hip_circumference, and sided arm/forearm/thigh/calf codes. Definitions describe canonical dimension/unit and valid sides/ranges; UI localization is separate.

Session time is stored once on body_measurement_sessions. Values do not duplicate the effective date. Updating the session date updates its chart placement without rewriting value history.

Provenance and deduplication

Source records retain:

source_type
source_application
source_record_id
source_record_version
recorded_at
source_modified_at
content_hash

Prefer uniqueness on (owner_id, source_type, source_application, source_record_id). When no stable source ID exists, derive a documented fingerprint from normalized type, time interval, values, and source. Hashes aid deduplication but do not replace authorization or collision-safe identifiers.

Imported original values and units are retained alongside one canonical conversion. A change of display unit performs no database rewrite.

Reference database

references.db has an explicit schema version and dataset version. Initial domains may include:

exercises, translations, muscles, exercise-muscle links, equipment;
program templates and source/version metadata;
foods, portions, nutrients, food-nutrient values and locales;
educational/reference content whose updates are signed and redistributable.

Rows have stable source namespace, source ID, version, deprecation state, and localized text tables. User customizations never modify this database; they reference stable IDs or copy a snapshot into PocketBase where durable semantics are required.

Deletion

Synchronization uses short-lived tombstones so deletions propagate. After the recovery window, purge records, derived associations, media, and per-user key material. Cascades are explicit and tested. Minimal audit events remain without personal health content for their configured retention.

Migrations

PocketBase migrations and Go-owned schema migrations are versioned in source, tested on realistic fixtures, and applied before accepting traffic. Reference schema compatibility is checked before atomic activation. Encryption payloads include schema/key versions so record migrations can be resumed safely.

5.9 KiB Raw Permalink Blame History