DoGaMa-serv/docs/domain/data-model.md

Data model

SQLite is authoritative for product state. Runtime Docker state is reconciled into, but never replaces, the registry.

Core entities

Entity	Purpose	Important fields
users	Local identities	id, username, password_hash, global_role, disabled_at, created_at
sessions	Revocable browser sessions	id_hash, user_id, expires_at, last_seen_at
instance_memberships	Per-instance baseline role	instance_id, user_id, role (user, manager)
permission_overrides	Explicit allow/deny beyond baseline	instance_id, user_id, permission, effect
templates	Catalog identity and origin	id, origin, trust_status, active_version
template_versions	Immutable validated snapshots	template_id, version, schema_version, canonical_yaml, digest
modules	Module identity and trust	id, source, trust_status, active_version
module_versions	Immutable installed artifacts	module_id, version, manifest, wasm_digest, path, api_range
instances	Desired and observed instance state	id, slug, display_name, template snapshot, revision, lifecycle_state, public_host
instance_settings	Typed non-secret template values	instance_id, field_id, value_json
instance_secrets	Encrypted secret values	instance_id, field_id, key_version, nonce, ciphertext
instance_ports	Published and private bindings	instance_id, port_id, host_ip, host_port, container_port, protocol
instance_mounts	Approved persistent mount mapping	instance_id, mount_id, host_path, container_path, category
instance_resources	Docker limits	instance_id, cpu_limit, memory_limit_mb, reservation_mb, pids_limit
instance_module_bindings	Pinned integration version	instance_id, module_id, module_version, config_revision, status
configuration_revisions	Small rollback history	instance_id, revision, redacted_snapshot, reason, created_by
backup_policies	Schedule and retention	instance_id, enabled, cron, timezone, retention_count, safety flags
backups	Managed archive metadata	id, instance_id, origin, status, path, size, sha256, game/template versions
imports	Temporary validation workflow	id, instance_id nullable, stage_path, detected_type, status, expires_at
operations	Durable long-running workflows	id, instance_id, type, phase, status, idempotency_key, error_code
scheduled_jobs	Next execution state	id, type, owner_id, schedule, timezone, next_run_at, enabled
installation_requests	User catalog requests	id, requested_by, template_id, message, status, reviewed_by
notification_channels	Global delivery configuration	id, type, enabled, encrypted_config, event_filter
notification_deliveries	Bounded retry queue	id, channel_id, event_type, payload_redacted, attempt, next_attempt_at
audit_events	Compact significant actions	id, occurred_at, actor_id, instance_id, action, outcome, summary_json
system_settings	Admin-configured global values	key, value_json, revision

Invariants

• IDs are opaque and stable; slugs are unique but mutable only through a controlled rename.
• Released template and module versions are immutable. Editing creates a new version or an independent local copy.
• An instance pins a template snapshot and module version; catalog changes do not mutate it silently.
• There is at most one active mutating operation per instance.
• A port tuple (host_ip scope, host_port, protocol) cannot be assigned twice by DoGaMa.
• Mount host paths are canonical absolute paths below configured roots.
• Secret fields never coexist in plaintext settings.
• Backup metadata becomes available only after archive finalization and checksum persistence.
• Imports expire and their staging directories are cleaned unless attached as a managed backup.
• Audit summary_json is allow-listed by event type and contains no secret values or full uploaded content.

Backup origins

manual, scheduled, pre_update, pre_restore, idle_shutdown, imported and system are stable origin identifiers. V1 retention applies only to automatic backups eligible under the instance policy. Manual, imported and explicit safety backups require deliberate deletion or a separately documented policy.

Operation state

Operations use queued, running, succeeded, failed, cancelled or intervention_required. A phase-specific checkpoint records enough information to decide safely after restart whether to resume, compensate or stop.

Data retention

• Audit: administrator-configurable, default 30 days, optional maximum entry count.
• Configuration history: default 10 revisions per instance, with secrets omitted.
• Notification delivery attempts: short operational retention after terminal state.
• Temporary imports: default 24 hours after last activity.
• Technical logs: standard output/error, outside SQLite and controlled by container log rotation.