Database Sync

The framework includes an optional database sync system that sends quest progress, boss kills, and NPC reputation to an external REST API in real time. This enables leaderboards, player profiles, and analytics dashboards outside the game.

Overview

The system consists of:

• VsQuestDbClient — HTTP client with API-key auth, timeout handling, and JSON serialization
• VsQuestSyncService — Fire-and-forget sync on quest lifecycle events
• Your own REST API — Any backend (PHP, Node, Python, etc.) with a database of your choice

All sync calls are non-blocking (Task.Run) so they never stall the game thread.

Configuration

File: alegacyvsquest/db-config.json (auto-generated on first run)

{
  "baseUrl": "http://localhost:8080",
  "apiKey": "your-secret-api-key",
  "timeoutSeconds": 30,
  "enableSync": true,
  "debounceSeconds": 30
}

When Sync Happens

API Specification

Your backend must expose the endpoints below and accept the exact JSON bodies the framework sends.

Player Data

Quest Progress

PUT /vsquest/player-quests/{player_uid}/{quest_id} body:

{
  "player_name": "PlayerName",
  "current_stage": 2,
  "completed_stages": [0, 1],
  "tracker_values": [5, 3],
  "status": "active",
  "quest_title": "Epic Journey"
}

• status can be "active" or "completed".
• completed_stages and tracker_values are integer arrays.

POST /vsquest/quest-completions body:

{
  "player_uid": "abc123",
  "player_name": "PlayerName",
  "quest_id": "yourmod:quest-name"
}

Boss Kills

PATCH /vsquest/boss-kills/{player_uid}/{boss_key} body:

{
  "player_name": "PlayerName",
  "boss_display_name": "Ossuary Warden"
}

Expected response (GET or PATCH):

{
  "player_uid": "abc123",
  "boss_key": "albase:bosshunt:ossuarywarden",
  "kill_count": 5
}

NPC Reputation

PUT /vsquest/npc-reputation/{player_uid}/{npc_id} body:

{
  "player_name": "PlayerName",
  "reputation": 250,
  "npc_display_name": "Boss Hunter"
}

Promo Redemptions

POST /vsquest/promo-redemptions body:

{
  "player_uid": "abc123",
  "player_name": "PlayerName",
  "code": "WELCOME2025",
  "rewards_json": "giveitem game:ingot-iron 5, giveitem game:gear-temporal 1"
}

Leaderboards

Statistics

Response:

{
  "total_players": 142,
  "active_quests": 89,
  "total_completions": 1523,
  "completions_7d": 67,
  "total_boss_kills": 412
}

Health Check

Authentication

All requests require the X-Api-Key header matching the apiKey from db-config.json. VsQuestDbClient sends this header automatically on every request. Reject requests with a missing or mismatched key with HTTP 401 Unauthorized.

Database Schema (MySQL Example)

Below is a complete SQL schema you can use as a starting point. The framework does not create tables automatically — you must run this against your own MySQL (or compatible) server.

CREATE DATABASE IF NOT EXISTS vsquest DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE vsquest;

CREATE TABLE players (
    player_uid VARCHAR(64) NOT NULL PRIMARY KEY,
    player_name VARCHAR(64) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_player_name (player_name)
) ENGINE=InnoDB;

CREATE TABLE quest_progress (
    player_uid VARCHAR(64) NOT NULL,
    quest_id VARCHAR(128) NOT NULL,
    current_stage INT NOT NULL DEFAULT 0,
    completed_stages JSON,
    tracker_values JSON,
    status VARCHAR(32) NOT NULL DEFAULT 'active',
    quest_title VARCHAR(256),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (player_uid, quest_id),
    CONSTRAINT fk_qp_player FOREIGN KEY (player_uid) REFERENCES players(player_uid) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE quest_completions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    player_uid VARCHAR(64) NOT NULL,
    player_name VARCHAR(64) NOT NULL,
    quest_id VARCHAR(128) NOT NULL,
    completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_qc_player (player_uid),
    INDEX idx_qc_quest (quest_id),
    CONSTRAINT fk_qc_player FOREIGN KEY (player_uid) REFERENCES players(player_uid) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE boss_kills (
    player_uid VARCHAR(64) NOT NULL,
    boss_key VARCHAR(128) NOT NULL,
    kill_count INT NOT NULL DEFAULT 1,
    player_name VARCHAR(64) NOT NULL,
    boss_display_name VARCHAR(128),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (player_uid, boss_key),
    CONSTRAINT fk_bk_player FOREIGN KEY (player_uid) REFERENCES players(player_uid) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE npc_reputation (
    player_uid VARCHAR(64) NOT NULL,
    npc_id VARCHAR(128) NOT NULL,
    reputation INT NOT NULL DEFAULT 0,
    player_name VARCHAR(64) NOT NULL,
    npc_display_name VARCHAR(128),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (player_uid, npc_id),
    CONSTRAINT fk_nr_player FOREIGN KEY (player_uid) REFERENCES players(player_uid) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE promo_redemptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    player_uid VARCHAR(64) NOT NULL,
    player_name VARCHAR(64) NOT NULL,
    code VARCHAR(64) NOT NULL,
    rewards_json TEXT,
    redeemed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_pr_player (player_uid),
    INDEX idx_pr_code (code),
    CONSTRAINT fk_pr_player FOREIGN KEY (player_uid) REFERENCES players(player_uid) ON DELETE CASCADE
) ENGINE=InnoDB;

Example PHP Backend

Here is a minimal PHP implementation you can adapt. It expects Apache/Nginx + PHP + MySQL and uses the schema above.

config.php

<?php
$dbHost = 'localhost';
$dbUser = 'vsquest';
$dbPass = 'CHANGEME';
$dbName = 'vsquest';
$apiKey = 'your-secret-api-key';

function requireAuth() {
    global $apiKey;
    $headers = getallheaders();
    if (($headers['X-Api-Key'] ?? '') !== $apiKey) {
        http_response_code(401);
        echo json_encode(['error' => 'Unauthorized']);
        exit;
    }
}

function getDb() {
    global $dbHost, $dbUser, $dbPass, $dbName;
    $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $pdo;
}

header('Content-Type: application/json');
requireAuth();

index.php (router)

<?php
require 'config.php';

$method = $_SERVER['REQUEST_METHOD'];
$uri = parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH);
$input = json_decode(file_get_contents('php://input'), true) ?? [];

if ($uri === '/status' && $method === 'GET') {
    echo json_encode(['ok' => true]);
    exit;
}

// Example: PUT /vsquest/player-quests/{uid}/{quest}
if (preg_match('#^/vsquest/player-quests/([^/]+)/(.+)$#', $uri, $m) && $method === 'PUT') {
    $pdo = getDb();
    $stmt = $pdo->prepare(
        "INSERT INTO quest_progress
         (player_uid, quest_id, player_name, current_stage, completed_stages, tracker_values, status, quest_title)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE
         player_name = VALUES(player_name),
         current_stage = VALUES(current_stage),
         completed_stages = VALUES(completed_stages),
         tracker_values = VALUES(tracker_values),
         status = VALUES(status),
         quest_title = VALUES(quest_title)"
    );
    $completed = json_encode($input['completed_stages'] ?? []);
    $trackers = json_encode($input['tracker_values'] ?? []);
    $stmt->execute([$m[1], $m[2], $input['player_name'] ?? '', $input['current_stage'] ?? 0, $completed, $trackers, $input['status'] ?? 'active', $input['quest_title'] ?? null]);
    echo json_encode(['ok' => true]);
    exit;
}

// Example: POST /vsquest/quest-completions
if ($uri === '/vsquest/quest-completions' && $method === 'POST') {
    $pdo = getDb();
    $stmt = $pdo->prepare("INSERT INTO quest_completions (player_uid, player_name, quest_id) VALUES (?, ?, ?)");
    $stmt->execute([$input['player_uid'], $input['player_name'], $input['quest_id']]);
    echo json_encode(['ok' => true]);
    exit;
}

// Example: PATCH /vsquest/boss-kills/{uid}/{boss}
if (preg_match('#^/vsquest/boss-kills/([^/]+)/(.+)$#', $uri, $m) && $method === 'PATCH') {
    $pdo = getDb();
    $stmt = $pdo->prepare(
        "INSERT INTO boss_kills (player_uid, boss_key, player_name, boss_display_name, kill_count)
         VALUES (?, ?, ?, ?, 1)
         ON DUPLICATE KEY UPDATE
         player_name = VALUES(player_name),
         boss_display_name = VALUES(boss_display_name),
         kill_count = kill_count + 1"
    );
    $stmt->execute([$m[1], $m[2], $input['player_name'] ?? '', $input['boss_display_name'] ?? null]);
    $count = $pdo->query("SELECT kill_count FROM boss_kills WHERE player_uid = " . $pdo->quote($m[1]) . " AND boss_key = " . $pdo->quote($m[2]))->fetchColumn();
    echo json_encode(['player_uid' => $m[1], 'boss_key' => $m[2], 'kill_count' => (int)$count]);
    exit;
}

// Example: PUT /vsquest/npc-reputation/{uid}/{npc}
if (preg_match('#^/vsquest/npc-reputation/([^/]+)/(.+)$#', $uri, $m) && $method === 'PUT') {
    $pdo = getDb();
    $stmt = $pdo->prepare(
        "INSERT INTO npc_reputation (player_uid, npc_id, player_name, npc_display_name, reputation)
         VALUES (?, ?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE
         player_name = VALUES(player_name),
         npc_display_name = VALUES(npc_display_name),
         reputation = VALUES(reputation)"
    );
    $stmt->execute([$m[1], $m[2], $input['player_name'] ?? '', $input['npc_display_name'] ?? null, $input['reputation'] ?? 0]);
    echo json_encode(['ok' => true]);
    exit;
}

// Example: POST /vsquest/promo-redemptions
if ($uri === '/vsquest/promo-redemptions' && $method === 'POST') {
    $pdo = getDb();
    $stmt = $pdo->prepare("INSERT INTO promo_redemptions (player_uid, player_name, code, rewards_json) VALUES (?, ?, ?, ?)");
    $stmt->execute([$input['player_uid'], $input['player_name'], $input['code'], $input['rewards_json'] ?? null]);
    echo json_encode(['ok' => true]);
    exit;
}

http_response_code(404);
echo json_encode(['error' => 'Not found']);

Recommended Server Setup

• Install MySQL / MariaDB (or PostgreSQL).
• Create a database and user, then run the SQL schema above.
• Deploy a small REST API (PHP, Node, Python, Go, etc.) that implements the endpoints listed in the API Specification section.
• Set your API URL and key in alegacyvsquest/db-config.json inside your Vintage Story server config folder.
• Restart the server and watch the logs for [vsquest] Database sync enabled (event-driven).
• Test connectivity with the /vsq dbcheck console command (if available) or by checking your API logs.