VonixGuardian — Database & Migration

Candidate Line: 3.0.0-m1 Storage ArchitectureSchema Version 8

VonixGuardian uses a dialect-aware JDBC DAO supporting SQLite (embedded zero-config), MySQL / MariaDB, and PostgreSQL.

1. Supported Storage Backends

Backend	Typical Deployment	Advantages	Limitations
SQLite (Default)	Single server, small-to-medium activity (≤ 5M events/day).	Zero setup, local file (vonixguardian.db), no network latency.	Exclusive write-lock; do not host on NFS/SMB network shares.
MySQL / MariaDB	Network clusters sharing centralized audit data.	High concurrent throughput, mature tooling.	Requires separate database server and connection pool tuning.
PostgreSQL	Shared remote database for higher write volume.	MVCC and indexing suited to large vg_actions tables.	Requires a dedicated PostgreSQL server.

---

2. Schema v8 Object Model

The database contains 8 tables, 5 performance indexes, and crash-recovery audit structures:

Table	Purpose	Key Columns
vg_users	Player name & UUID interning	id, uuid (CHAR 36, nullable), name, first_seen, last_seen
vg_worlds	Dimension identifier interning	id, world_key (VARCHAR 96, e.g. minecraft:overworld)
vg_actions	The primary append-only audit log	id, ts, type, user_id, world_id, x, y, z, target, meta, amount, rolled_back, source_tag, sign_side, sign_dye_color, sign_waxed, old_block_state, new_block_state, block_entity_nbt, item_nbt, entity_nbt, pair_id, inventory_slot
vg_rollback_batches	Crash-recovery audit of rollbacks	id, ts, actor_uuid, mode, affected, completed, filter_json
vg_rollback_batch_actions	Composite link: batch <-> actions	batch_id, action_id (Composite Primary Key)
vg_schema_version	Applied migration ledger	version (currently 8), applied_at
vg_repair_required	Durable uncompensated rollback records	action_id, pair_id, batch_id, reason, ts
vg_sink_outbox	Dual-write staging buffer	id, payload, created_ts

Core Performance Indexes

• vg_actions_pos: (world_id, x, z, y, ts) — Used for spatial range queries and radius rollbacks.
• vg_actions_user_t: (user_id, ts) — Used for /vg lookup u:<player> queries.
• vg_actions_type_t: (type, ts) — Used for action filters (a:block, a:kill).
• vg_actions_ts: (ts) — Used for time scans, retention sweeps, and purges.
• vg_actions_pair: (pair_id) — Used for atomic sibling lookups during inventory reversals.

---

3. HikariCP Connection Pool Tuning

For MySQL and PostgreSQL backends, connection pooling is managed via HikariCP. Example configuration for config/vonixguardian/config.json:

{
  "database": {
    "type": "postgresql",
    "file": "vonixguardian.db",
    "jdbcUrl": "jdbc:postgresql://db.example.net:5432/vonixguardian?sslmode=prefer",
    "user": "vonix_operator",
    "password": "[REDACTED]"
  }
}

Rule of thumb: A pool size of 10 connections comfortably services 50–200 concurrent players. Increase pool size only if /vg status reports elevated connection acquisition wait times.

---

4. Live Database Migration (/vg migrate-db)

VonixGuardian 3.0.0-m1 includes a console-only copier for moving audit rows between backends. Treat it as a maintenance snapshot, not a zero-downtime live migration.

Operational Command

# Execute from server console ONLY:
/vg migrate-db postgresql CONFIRM

Safety & Concurrency Architecture

1. Maintenance Write-Block: The command engages an in-memory barrier that halts incoming action queue admissions.
2. Buffer Drain: The engine waits until:
   • In-flight ring buffer batches are committed.
   • Worker thread local batches are processed.
   • The active sink transaction reaches an idle state.
3. Snapshot Barrier: The dataset is copied table-by-table to the destination database.
4. Resumption: Queue admission unfreezes once the migration completes.

Snapshot Barrier Boundary

This mechanism operates as a best-effort snapshot barrier within the mod process, not an operating-system or database-level global freeze. Ensure server activity is low or scheduled during a maintenance window before executing large migrations.

---

5. Backup & Restore Runbooks

SQLite Backup

While the server is running, SQLite can be safely backed up using the SQLite online backup API or by copying the file when the queue is idle:

# Clean copy:
sqlite3 vonixguardian.db ".backup 'vonixguardian-backup.db'"

PostgreSQL Backup

pg_dump -h localhost -U vonix_operator -d vonixguardian -Fc -f vonixguardian_backup.dump

Database Maintenance Direct Query Example

To inspect active repair requirements directly:

SELECT action_id, pair_id, reason, datetime(ts/1000, 'unixepoch') AS recorded_time
FROM vg_repair_required
ORDER BY ts DESC
LIMIT 10;