SQLite and future storage changes

SQLite’s limits became noticeable while building with PocketBase, but replacing the database was not the first step. Screens that know collection names and raw response shapes would require broad changes alongside any storage migration. We first needed to reduce that dependency to leave room for PostgreSQL or another platform later.

For example, editing a game list involves permission checks, the current state and change history as well as saving values. Calling collections individually from a screen complicates recovery when only some writes succeed. Read rules for public documents and private drafts can also spread across screens.

flowchart TD
    accTitle: The boundary between web requests and storage
    accDescr: Browsers reach DataKit through Next while server rendering calls it directly; backend custom APIs own storage operations.
    B["Browser"] --> N["Next Route Handler"]
    N --> D["DataKit service"]
    S["Server rendering"] --> D
    D --> A["Backend custom API"]
    A --> P["PocketBase storage"]

API v2 therefore accepts a user operation as one request. The interface supplies intent; the server handles authorization, save order and failure behavior. This makes an operation’s rules easier to inspect while reducing knowledge of the storage structure.

Web requests and server APIs

PocketBase still supplies authentication and storage. Documented custom APIs expose the supported arcade operations, including request values, product responses and the meaning of authorization errors and conflicts. Keeping that interface stable can limit screen changes when internal collections evolve.

sequenceDiagram
    accTitle: Conflict checks and atomic game saves
    accDescr: Commit the next state pointer and history together only when the base matches; otherwise return a conflict.
    participant W as Web editor
    participant D as DataKit
    participant A as Backend API
    participant P as Storage
    W->>D: Base state and changes
    D->>A: Game mutation request
    A->>P: Check current state
    alt Base state matches
        A->>P: Commit next state pointer and history
        A-->>W: New state ID
    else Another save completed first
        A-->>W: 409 conflict
    end

Browser interactions pass through a Next.js Route Handler to a DataKit service; server rendering calls the same service directly. DataKit handles authenticated server transport and response interpretation. Both paths use the same product types instead of forwarding raw storage responses to the browser.

Authorization and transactional saves

A game edit includes the arcade ID, the state from which editing began and additions, modifications and removals. The server checks editing permission and current state. If someone else saved first, it returns a conflict while the interface keeps the draft available for comparison with the latest version.

flowchart TD
    accTitle: Code ownership in Backend Core and Full
    accDescr: The Full deployment application includes Core and adds existing-data migrations and operational configuration.
    C["Core module"] -->|"includes"| F["Full deployment application"]
    C --> A["API authorization and transactions"]
    C --> S["Fresh-environment base schema"]
    M["Existing-data migration"] --> F
    R["Operational configuration and job registration"] --> F

A valid request saves the new game state, current reference, history and any required reward in one transaction. A transaction makes the group commit or roll back together. External service calls run outside it, avoiding a database lock held while waiting for another service.

Core, Full and the current implementation

Core owns reusable schemas and API behavior. Full adds operational migration of existing data, configuration and job registration. Creating a fresh environment and transforming existing history and references require different verification.

The current custom APIs still run on PocketBase and SQLite. Migration to PostgreSQL or another platform remains future work. The change gives screens the result of a complete user operation and lets the server validate authorization and persistence together. New features need to use the same APIs to retain those benefits.

Related articles and sources: Arcade change history · Musecat engineering notes