Entities and data access

Moongate stores registered entities in PostgreSQL through FreeSql. A deployment uses one Accounts database owned by login and one Realm database per game world. Standalone uses both. Transactions and world saves never span those databases.

This page is for the developer who registers an entity and reads or writes it. The other two persistence pages are Migrations: generate, review and apply, for changing the schema, and Operate PostgreSQL, for connection strings, roles, world saves and backups. For a complete first example, follow Create a persistent entity.

Two databases, four names each

The same two databases appear under different names depending on where you meet them. They are the same thing:

Database	Purpose	TOML section	Migration target	Registration helper	C# target
Accounts	Shared login and account data	[persistence.accounts]	--target auth, migrations/auth/	AddPersistenceAuth<T>()	PersistenceDatabaseTarget.Accounts
Realm	This world’s data	[persistence.realm]	--target world, migrations/world/	AddPersistenceWorld<T>()	PersistenceDatabaseTarget.Realm

The generated defaults name the databases auth and world.

Register entities

Every persisted type implements IMoongateEntity and has a stable, nonzero Serial. Select the database when registering it:

container.AddPersistenceAuth<Account>();     // Shared Accounts database.
container.AddPersistenceWorld<Character>();  // This realm's database.

Register an entity only in a process that owns its target: Auth entities in login or standalone, World entities in game or standalone. Registering an entity for an inactive target fails during startup. A login process checks only Accounts connectivity; a game checks only its Realm database.

Moongate creates internal modules automatically, grouping entities by database and PostgreSQL schema. A separate module class is not required. Declare a stable, schema-qualified table name in the entity’s attributes:

using FreeSql.DataAnnotations;
using Moongate.Core.Interfaces.Entities;
using Moongate.Core.Primitives;

[Table(Name = "inventory.items")]
public sealed class Item : IMoongateEntity
{
    [Column(Name = "id", IsPrimary = true, MapType = typeof(long))]
    public Serial Id { get; set; }

    [Column(Name = "name", StringLength = 100)]
    public string Name { get; set; } = "";
}

Mapping rules:

• Map Serial keys to PostgreSQL bigint with MapType = typeof(long). Do not mark the key IsIdentity; the sequence is attached by a migration.
• Column names default to lowercase snake_case: Username maps to username, HashPassword to hash_password, CreatedAt to created_at. Explicit [Column(Name = "...")] mappings take precedence and must also be lowercase snake_case.
• Ordinary public scalar properties are mapped by FreeSql. Every complex property needs an explicit supported mapping, a navigation mapping, or IsIgnore; an unannotated object graph is not serialized or cascaded.
• DateTime columns are timestamp without time zone. Moongate converts local values to UTC before writing and reads every value back as DateTimeKind.Utc.
• Hue properties map to an integer column automatically.
• The mapping belongs to the CLR type and must be identical in every owner. A module selects ownership and database target; it cannot remap the type, and no code may reconfigure a persistence type through another raw FreeSql instance.

Register before schema preparation:

container.RegisterMoongatePersistence(options)
         .AddPersistenceWorld<Item>();

Registration performs no database I/O. Initialization validates the entire batch, including duplicate registrations, table mappings and schema ownership. Every type has exactly one owner, and one CLR type cannot be registered in both databases in the same container.

The server publishes PersistenceReadyEvent after initialization and before startup services begin, and PersistenceStoppedEvent after the initialized owner is disposed, before container disposal. Subscribe during plugin registration with container.OnEvent<TEvent>(...); see persistence lifecycle events. The standalone persistence library does not publish these server events.

Optional explicit plugin modules

A plugin can implement IPersistenceModule and register it through AddPersistenceModule<TModule>() when it needs a stable module identifier and an explicit list of owned entity types. It declares Id, Schema, DatabaseTarget and EntityTypes. Register its entities with the matching Auth/World helper, whose target must agree with the module, or with AddPersistenceEntity<T>(), where the module supplies the target. Registration order does not matter. An explicit module owns its complete schema: include every registered entity in that schema in its declaration.

Custom values and collections as JSONB

Annotate an owned custom value or List<T> with [JsonMap, Column(DbType = "jsonb", IsNullable = true)] from FreeSql.DataAnnotations. Moongate enables FreeSql.Extensions.JsonMap for both database targets; no extra registration is required for the nested value type. The extension uses Newtonsoft.Json and, with default settings, preserves JSON member casing independently of snake_case SQL column names.

Empty collections round-trip as []; nullable properties round-trip as SQL NULL. Reads are detached and an explicit upsert replaces the whole JSON value. JSON mapping does not add cascade saves, automatic change tracking, or per-element updates. Snapshot callbacks must deep-copy nested mutable values. Use separate entities for values with their own identity and lifecycle.

Follow the JSONB tutorial for a complete collection example, migrations, and serializer limitations.

Reads, writes and transactions

Resolve IDataAccess<T> for independent operations:

var items = container.Resolve<IDataAccess<Item>>();
var item = new Item { Name = "Bandage" };
await items.UpsertAsync(item); // item.Id is assigned automatically.

var loaded = await items.GetByIdAsync(item.Id);
var page = await items.QueryAsync(value => value.Name.StartsWith("B"), 0, 50);
await items.DeleteAsync(item.Id);

GetByIdAsync, GetAllAsync and QueryAsync return detached values. Queries are translated to SQL; an unsupported expression fails instead of switching to client filtering. GetAllAsync is intentionally unbounded and meant for startup or administration. Changing a returned object does not save it.

Upserts use last-writer-wins semantics. There is no optimistic concurrency token and no automatic retry after an uncertain commit result. Group related writes on one database with ExecuteInTransactionAsync:

await persistence.ExecuteInTransactionAsync(
    PersistenceDatabaseTarget.Realm,
    async transaction =>
    {
        var items = transaction.GetDataAccess<Item>();
        await items.UpsertAsync(first);
        await items.UpsertAsync(second);
    });

The callback must complete asynchronously and must not escape its transaction facade. A transaction cannot span Accounts and Realm. If a workflow changes both, design explicit compensation or reconciliation.

Automatic Serial assignment

Leave Id at zero on a new entity and UpsertAsync assigns it on that same instance:

var account = new AccountEntity { Username = "Mario", HashPassword = passwordHash };
await accounts.UpsertAsync(account);
Console.WriteLine(account.Id); // Assigned on this same instance.

• Id == Serial.Zero reserves a unique nonzero ID and performs an insert, never an update of an existing row. IDs already stored explicitly are skipped.
• A nonzero Id is preserved and uses the existing insert-or-update behavior.
• Automatic assignment requires a public Id setter. IMoongateEntity itself exposes only a getter.
• Each table has its own sequence in its schema. Sequences use the range 1..4294967295, never cycle, and are shared across processes using that database. An entity whose serials live in a range that does not start at 1 declares it with [SerialRange(min, max)] (Moongate.Core.Attributes): a generated sequence then starts at min, so an empty table reserves min first, and a sequence left below min by an older generated schema is moved forward to it. ItemEntity declares Serial.MinItem..Serial.MaxItem. The attribute does not cap the top of the range; a reviewed migration can add a check constraint, as world.items does. Supply gameplay serials explicitly when domain rules require shared identity.
• Sequence creation belongs to schema migrations, never to UpsertAsync. The runtime role needs USAGE and SELECT on the sequences as well as the table permissions; SELECT lets a SQL backup read their values.

A failed insert restores the entity’s ID to zero. After a successful insert inside a transaction, a later rollback keeps the assigned ID on the object; retrying with that ID is an explicit upsert. Reservations are not rolled back or recycled, so gaps are expected. Commit acknowledgement failures have an unknown durable outcome and are not retried. Do not share or mutate an entity while its persistence operation is running.

SaveAllAsync requires stable nonzero IDs on captured live entities: persist a new entity with UpsertAsync before adding it to a live save collection. The advanced MoongatePersistenceService.ReserveSerialAsync<TEntity>("schema.sequence", cancellationToken) API remains available for explicitly migration-managed sequences; ordinary entities do not need it. It does not create sequences or allocate gameplay serial ranges.

Apply a database result to the game loop

A critical host operation that changes both PostgreSQL and live world state must use IPersistenceOperationBarrier. Admit the operation before database work, run it off the game loop, and keep it admitted until its owner-thread application finishes. The barrier coordinates these operations with world saves; it does not reserve gameplay objects against ordinary handlers. Existing-object workflows must separately reserve or revalidate the affected state on the loop.

This example creates a new, private Item (the mapped type above), persists it, and only then publishes it to an owner-loop dictionary. Put this service in MyShard.Plugin/Services/PersistedItemService.cs:

using Moongate.Core.Primitives;
using Moongate.Persistence.Interfaces;
using Moongate.Server.Core.Interfaces.Persistence;
using Moongate.Server.Core.Interfaces.Services;
using MyShard.Plugin.Internal;

namespace MyShard.Plugin.Services;

public sealed class PersistedItemService
{
    private readonly IPersistenceOperationBarrier _barrier;
    private readonly IDataAccess<Item> _dataAccess;
    private readonly Dictionary<Serial, Item> _items = new();
    private readonly IGameLoopService _loop;

    public IReadOnlyDictionary<Serial, Item> Items => _items;

    public PersistedItemService(
        IPersistenceOperationBarrier barrier,
        IDataAccess<Item> dataAccess,
        IGameLoopService loop)
    {
        _barrier = barrier;
        _dataAccess = dataAccess;
        _loop = loop;
    }

    public Task CreateAsync(string name, CancellationToken cancellationToken = default)
    {
        ArgumentException.ThrowIfNullOrWhiteSpace(name);
        if (_loop.IsOnLoopThread)
        {
            throw new InvalidOperationException("Call CreateAsync off the game loop.");
        }

        return _barrier.ExecuteAsync(async operationToken =>
        {
            var item = new Item { Name = name };
            await _dataAccess.UpsertAsync(item, operationToken);
            var work = new OwnerActionWorkItem(() => _items.Add(item.Id, item));
            try
            {
                await _loop.PostAsync(work, operationToken);
            }
            catch (InvalidOperationException) when (_loop.Completion.IsCompleted)
            {
                await _loop.Completion;
                throw;
            }

            await Task.WhenAny(work.Completion, _loop.Completion);
            if (!work.Completion.IsCompleted)
            {
                await _loop.Completion;
                throw new InvalidOperationException("The game loop stopped before applying the item.");
            }

            await work.Completion;
        }, cancellationToken);
    }
}

Put its synchronous work item in MyShard.Plugin/Internal/OwnerActionWorkItem.cs:

using Moongate.Server.Core.Interfaces.GameLoop;

namespace MyShard.Plugin.Internal;

public sealed class OwnerActionWorkItem : IGameLoopWorkItem
{
    private readonly Action _apply;
    private readonly TaskCompletionSource _completion =
        new(TaskCreationOptions.RunContinuationsAsynchronously);

    public Task Completion => _completion.Task;

    public OwnerActionWorkItem(Action apply)
    {
        _apply = apply;
    }

    public void Execute()
    {
        try
        {
            _apply();
            _completion.TrySetResult();
        }
        catch (Exception exception)
        {
            _completion.TrySetException(exception);
            throw;
        }
    }
}

In the plugin’s Register, use this instead of the earlier plain AddPersistenceWorld<Item>() registration. Import Moongate.Persistence.Extensions, Moongate.Server.Core.Extensions and MyShard.Plugin.Services:

container.AddMoongateService<PersistedItemService>();
container.AddPersistenceWorld<Item>(
    () => container.Resolve<PersistedItemService>().Items.Values,
    item => new Item { Id = item.Id, Name = item.Name });

The host supplies the barrier and the already-started IGameLoopService; the entity registration supplies IDataAccess<Item>. Apply the entity’s SQL migration before starting the host, as in Create a persistent entity. An off-loop console command or owned asynchronous worker can inject the service and await service.CreateAsync("Bandage", cancellationToken). On success, PostgreSQL has the row and Items contains its assigned ID. Read or modify that dictionary only on the loop. This creation example does not load existing rows; a full service also needs startup loading and shutdown admission closure/draining.

PostAsync completes when the loop accepts the item. The second await observes its actual application and also detects a loop that stops before executing it. Once admitted to the loop, the work item is not retracted by cancellation: wait for its completion without canceling that wait. See Game loop and timers for admission and completion.

Do not catch and hide a failure inside the barrier callback, retry an uncertain commit, or reenter the barrier. Report the exception to the caller. If an admitted critical callback fails or is canceled, the barrier retains the original cause and blocks queued and new critical operations, later snapshots and the final save. This prevents stale memory from overwriting a database commit whose outcome may be uncertain. Shutdown still drains and stops, but the world needs a fresh host before further persistence. The rule is conservative even when the specific transaction rolled back. Cancellation before admission and a save-only failure do not poison the barrier.

Live world snapshots

For state owned by the game loop, register a source and a detached clone:

container.AddPersistenceWorld<Item>(
    () => world.Items.Values,
    item => new Item { Id = item.Id, Name = item.Name });

The clone function must copy every mutable nested value; returning the live instance is rejected. SaveAllAsync captures sources through the supplied owner callback and later writes one transaction per active database. It upserts the captured entities; absence from a snapshot is not deletion. Issue an explicit DeleteAsync for removed rows, or register a deletion source:

container.AddPersistenceWorld<Item>(() => world.Items.Values, item => item.Snapshot(), world);

A save writes only the captured entities whose snapshot changed since the last committed save. Each snapshot gets a fingerprint, the first 128 bits of the SHA-256 of its JSON, worked out off the loop; the entities whose fingerprint differs, or that the last committed save did not capture, are upserted, and the others are skipped. The fingerprints are kept only once the transaction commits, so after a failed save the next one writes those entities again. An entity that leaves the source, such as a character logging out, loses its fingerprint and is written in full when it comes back. The first save after a start writes everything, and so does every twelfth save after it (once an hour at the default five-minute interval): a row changed behind the world save, such as by a character leaving while a save runs, is put right by then. The fingerprint covers every property with a setter, a private one included, and the values of the structs they hold, such as Serial.Value; getter-only properties, computed from the others, are left out. NaN and the infinities fingerprint like any number. The log says how many entities were captured and how many were written.

IPersistenceDeletionSource.Capture() runs on the loop with the snapshot and returns the identities the source removed; the save deletes them in the same transaction as its upserts, then calls Committed() with exactly those. A failed save does not call it, so they stay pending. IDataAccess<T>.ReserveSerialAsync() takes the next identity from the entity’s sequence without writing a row, for an entity made in memory and saved later.

How often saves run, and how the final save behaves at shutdown, is described in Operate PostgreSQL.

World items

ItemEntity (world.items) and MobileEntity (world.mobiles) are registered by the Ultima plugin for the Realm database in game and standalone modes. An item is in exactly one place, and the database checks it:

Place	Columns	Set with
On the ground	map, x, y, z	PlaceOnGround(map, location)
In a container item	container_id, grid_x, grid_y, grid_index	PutInContainer(containerId, gridLocation, gridIndex); read back as GridLocation and GridIndex, the slot (0 to 124) in the Enhanced Client’s grid
Worn by a mobile	mobile_id, layer	Equip(mobileId, layer)

Items lying on the ground live in IItemService and the sector grid while the server runs: IItemService loads them, and everything inside them, at startup (migration 0010 indexes them by map), and the world save writes them with the characters’ items. The NPCs (mobiles without an account) live in IMobileService too: INpcService loads them at startup with what they wear and carry, and spawns and removes them. A removed mobile is deleted by the next world save in its own transaction (IMobileService is the mobiles’ deletion source), and its item rows go with it through the cascading keys. Its live items must leave IItemService first: the save writes the mobiles before the items, and a live item of a deleted mobile would break every later save.

What a character drops on the ground, or a ground stack it grows, is also saved by its leave, in the same transaction as its own items and its merged stacks’ deletions: its rows would otherwise still say the character carries them until the next world save.

Each helper clears the other two groups, so move an item only through them. The database also rejects an item inside itself and two items on the same layer of one mobile. Deleting a container or a mobile deletes what it holds, recursively: a backpack and everything inside it disappear with the mobile that wears it.

A new item with Id = Serial.Zero gets its id from world.items_id_seq, which starts at Serial.MinItem; a CHECK caps it at Serial.MaxItem, and mobiles are held to Serial.MinMobile..MaxMobile the same way. Only what differs from the item template is stored: a null name, movable or visibility means the template’s value. Values only some items have, such as charges, durability and script tags, go in the props JSONB column, a key-value dictionary read and written through the item:

item.SetProp(ItemPropKeys.Quality, ItemQualityType.Exceptional); // null removes the key
var charges = item.GetProp<int>(ItemPropKeys.Charges);   // default(T) when missing
var quality = item.GetProp(ItemPropKeys.Quality, ItemQualityType.Regular);
if (item.TryGetProp<LootType>(ItemPropKeys.LootType, out var lootType)) { /* ... */ }
item.RemoveProp(ItemPropKeys.Charges);

ItemPropKeys names the keys the server reads (loot_type, charges, durability, max_durability, quality, crafter_id, crafter_name, uses_remaining, label_number, bank.worth); scripts may use any other key. quality holds an ItemQualityType (Low, Regular, Exceptional, as ModernUO); an item without it is Regular. A prop holds a string, a number, a bool or an enum; anything else is rejected when set. The column gives whole numbers back as long and enums as their number, so GetProp<T> converts to the type asked for, and throws InvalidCastException naming the key when the stored value does not convert. An item with no props stores NULL.

A mobile keeps what its template rolled when it was made: template_id (null for a player character), hits/hits_max, mana/mana_max, stamina/stamina_max, fame, karma, armor and the five resist_* columns, besides name, body, looks, stats and skills. title and notoriety are stored only when they differ from the template’s (null means the template’s); a CHECK keeps notoriety between 1 and 7. MobileEntity has the same props dictionary and SetProp/GetProp/TryGetProp/ RemoveProp helpers as items, with the same rules.

Accounts

The built-in Ultima plugin registers AccountEntity in the Accounts database and IAccountService in the container. CreateAccountAsync uses IDataAccess<AccountEntity> and lets UpsertAsync assign the new account’s Id. ListAccountsAsync wraps GetAllAsync: an unbounded, detached snapshot of every account, for administration rather than per-request lookups. LoginAsync verifies the password hash and the lock state. A successful login returns a filtered 0xA8 realm list; selection issues a one-use Redis handoff ticket that the game verifies without direct Accounts database access.

The built-in Ultima plugin also registers a command to create accounts:

account create <username> <password> [Regular|GameMaster|Administrator]

The level defaults to Regular. The interactive console masks the password while typing, and command output never repeats it. The command is also registered for in-game administrators, who type it with a leading dot (see Server commands); the api-access subcommand refuses in-game use. Usernames and passwords must each be one token because the command system separates arguments on spaces.

The username index is case-sensitive, matching the service’s lookup. Concurrent attempts to register the same username return one success and UsernameAlreadyExists for the others. Email is optional because creation does not require one. Locked accounts cannot log in; canceled requests propagate OperationCanceledException. The core auth SQL that creates this table is described in the core auth catalog.

Load testing

The persistence stress scenario runs virtual sessions through DataAccess<T> against an isolated PostgreSQL container, reports latency and throughput, and verifies committed data after reopening persistence. It is opt-in and needs no Ultima Online implementation or client files.