Customer records and MariaDB

Identity and storage

Use MariaDB on existing hosting with HSLG-owned business tables. A customer has a stable random UUID. Every customer-field save inserts a separate row with another UUID. The originating local MariaDB session stamps written_at DATETIME(6) using its CURRENT_TIMESTAMP(6) default.

Selection

A customer-field read selects ORDER BY written_at DESC, record_id DESC LIMIT 1 in SQL. The UUID only breaks equal-time ties. There are no sequential customer-field version counters. Use UTC sessions. Future imports must preserve original UUID, source node and timestamp.

Why

Append-only customer-field records retain change evidence and reduce the cost of later synchronisation experiments. A database index makes latest selection efficient without copying all history to the phone.

Costs and limitations

• More records consume disk, indexes, backups and privacy-retention capacity; the owner accepts larger databases.
• Original-write timestamps do not guarantee causal order between drifting restaurant clocks.
• Replication, conflict resolution and delete/erasure policy are deferred and require explicit design.
• Do not use latest-write-wins for passwords, sessions, payment status, stock or orders. Those require transactional state and idempotency.

Current proof

Schema and repository unit tests exist, but the latest staging migrations and cross-server replication have not been validated on production MariaDB.

Which information belongs in which storage system

WordPress posts, terms, editorial events and documentation pages are WordPress content. They follow WordPress's post IDs, permission model and revision behaviour. HSLG customer identity, profile fields, account credentials, order drafts, payment state and venue business identifiers are separate operational records. They use HSLG-owned tables and do not treat a WordPress post or user ID as the long-term identifier of an HSLG customer. This separation is necessary so an editorial restore or a theme change cannot silently redefine account ownership.

Original local MariaDB write time

The agreed customer-field model records the field's original server write, including a stable customer UUID, field identifier, a unique record UUID and an originating node identifier. When writing a new customer-field value on the current server, MariaDB supplies CURRENT_TIMESTAMP(6) through its column default; the application must omit that column rather than invent a phone timestamp. Read operations select the greatest original write timestamp and then use the record UUID as a deterministic tie-breaker. The UUID tie-break does not mean the value is chronologically later—it only makes equal-time selection stable.

The earlier plan considered sequential version counters. Those were explicitly rejected for customer fields because distributed authors could not assign globally monotonic counters without additional coordination. The source migration and repository contracts follow the timestamp-plus-UUID approach. This is a design rule for customer-profile field history, not a requirement to append-only every piece of data or overwrite the current snapshot arbitrarily.

Consequences of append-only history

The database stores several values for a field instead of updating one row. This improves reconstructability of how a customer profile changed, but consumes additional disk, indexes and backup space. A latest-field query needs the correct composite index and a deterministic sort; otherwise scans will grow with accumulated history. Debugging must distinguish the original write time, the time a remote replica first saw the row, and the current read time. Later replication must preserve the original source timestamp and record ID. It must not silently restamp a copied record, because that would change which value wins.

Not all clocks are trustworthy. Two restaurant nodes could have skewed clocks, so latest timestamp alone does not guarantee the order in which a customer made changes. The current MVP deliberately avoids claiming a solved multi-node conflict model. Future replication needs source authentication, clock-skew policy, duplicate suppression and a strategy for sensitive fields whose last-write-wins result could create security problems.

The right to erasure and retention law can conflict with an indefinite immutable audit trail. The approved data model therefore still requires a lawful deletion/retention mechanism, including handling encrypted representations where used, replicas, backups and legitimate audit exceptions. 'Append-only' is not a legal exemption. The current system should not promise that historical customer data is protected by an already-implemented advanced encryption layer; the more complex application-level encryption design was deferred until after a working MVP.

Why accounts and payments use different semantics

Password hashes, sessions and recovery challenges have expiry, revocation and secrecy requirements; they cannot simply be chosen by latest profile timestamp. An order is a state machine with idempotency and monotonic business constraints. A payment requires verification of provider events and settled amounts. A stock reservation may require an atomic decrement. Applying the profile-field newest-row algorithm to any of these would be a design error. Keep the repository APIs different and test cross-module boundaries.

The account schema includes separate credentials, bearer session metadata, challenges and rate limits. The order schema introduces drafts with a durable idempotency key. Later pricing and payment migrations are additive foundations. Before deployment each migration needs a full snapshot/rollback plan, real MariaDB staging validation and duplicate-migration tests. The existence of a dbDelta installer or a PHP mock does not prove MariaDB's actual column defaults, uniqueness or index selection.

Security and operational verification

A profile read must derive the customer from a valid bearer session. The client must not choose another customer UUID and fetch the corresponding field history. Profile saves must restrict permitted field identifiers and constrain value length. Customer data should not appear in open WordPress REST responses, public page caches, Apache logs, screenshots or generated development artifacts. Query and migration tests must include unverified sessions, expired tokens, equal timestamps, UUID collisions, duplicate idempotency keys and an inaccessible database.

Before a future multi-node rollout, run a two-database simulation with intentionally skewed source clocks, interrupted transfers, duplicate replay and field-level conflict examples. That future work must be reflected in architecture and disaster recovery before being enabled. For now the acceptance standard is a single-server MariaDB-backed flow that behaves correctly during normal use and predictable failure.