# Database Relationships

Use this document before reading any `.sql` file or tracing a database-backed feature. It is the canonical relationship map for the application. SQL files are schema snapshots and source variants; consult them only to verify a documented difference or record a schema change.

## Database Boundaries

The application has two PDO handles:

| Handle | Usual contents | Configuration |
|---|---|---|
| `Model::$conn` | Game/source tables and shared legacy tables | `GAME_DB` |
| `Model::$connCMS` | CMS-owned tables; may be the same database | `CMS_DB` or the game connection |

The actual database boundary depends on configuration. Table names alone do not guarantee which connection is used; follow the model query and `Model::$conn`/`Model::$connCMS` call.

## Constraint Policy

The repository SQL schemas generally use logical relationships implemented by model queries, shared identifiers, and application workflows. The CMS content translation relation is an explicit exception: `cms_content_translations.node_id` has a foreign key to `cms_content_nodes.id` with delete cascade and restricted ID updates.

## Canonical CMS Map

The canonical CMS schema is defined and migrated by `installation/cms.sql`.

```mermaid
erDiagram
    ACCOUNTS ||--o{ CMS_ORDERS : "owner username"
    CMS_ORDERS ||--|{ CMS_ORDERS_PRODUCTS : contains
    CMS_ORDERS ||--o{ CMS_PAYMENTS : "order_id"
    CMS_ORDERS ||--o{ CMS_ORDERS_TEMP_TXN : "order_id + payment_method"
    CMS_ORDERS ||--o{ CMS_ORDERS_CRYPTO_DATA : "order_id"
    CMS_SHOP ||--o{ CMS_ORDERS_PRODUCTS : "product_id snapshot source"
    CMS_PAYMENTS ||--o{ PURCHASESUNCLAIMED : "txn_id"
    ACCOUNTS ||--o{ PURCHASESUNCLAIMED : "owner username or UID"
    ACCOUNTS ||--o{ CMS_NOTIFICATIONS : "UID"
    CMS_CONTENT_NODES ||--o{ CMS_CONTENT_NODES : "parent_id"
    CMS_CONTENT_NODES ||--o{ CMS_CONTENT_TRANSLATIONS : "node_id"
    CMS_RANK_GUILDS ||--o{ CMS_RANK_ENTITIES : "ID to GuildID"
    CMS_RANK_ENTITIES ||--o| CMS_RANK_ARENA : "UID"
```

### CMS Table Catalog

| Table | Primary key | Logical relationships and role |
|---|---|---|
| `cms_info` | `name` | Installation/migration metadata, including schema `version`. |
| `cms_shop` | `product_id` | Product catalog. `cms_orders_products.product_id` points here when retained; order rows also snapshot product data. Created by renaming the initial `shop` table. |
| `cms_payments` | `payment_method`, `txn_id` | Provider payment records. `order_id` points to `cms_orders.order_id`; `txn_id` is used to locate related `purchasesunclaimed` rows. Created by renaming the initial `payments` table. |
| `votes` | `voted_at`, `ip` | Vote records keyed by voter IP/time; `Username` logically identifies the account. |
| `cms_rank_entities` | `UID` | CMS ranking projection of game entities. `GuildID` points logically to `cms_rank_guilds.ID`. |
| `cms_rank_guilds` | `ID` | CMS ranking projection of guilds. |
| `cms_rank_arena` | `UID` | Arena ranking projection; `UID` points logically to `cms_rank_entities.UID`. |
| `cms_token` | `id`, `type` | Polymorphic security tokens. `id` is a caller-defined subject, not a single table foreign key. |
| `cms_rewards_logs` | `Owner`, `datetime` | Reward/vote processing log. `Owner` and `IP` are logical account/request identifiers. Created by renaming `rewards_logs`. |
| `cms_orders` | `order_id` | Order header owned by the account identifier in `owner`. |
| `cms_orders_products` | `order_product_id` | Order line items. `order_id` points to `cms_orders`; `product_id` is optional and product fields are a historical snapshot. |
| `cms_orders_temp_txn` | `order_id`, `payment_method` | Temporary provider transaction reservation. `order_id` points to `cms_orders`; `(payment_method, txn_id)` is unique. |
| `cms_orders_crypto_data` | `crypto_data_id` | Blockchain payment metadata. `order_id` points to `cms_orders`; `(payment_method, transaction_hash)` is unique. |
| `cms_rate_limit_events` | `scope`, `subject` | Account/IP/action rate-limit state. `subject` is polymorphic and is not a foreign key. |
| `cms_notifications` | `UID`, `type` | Per-account notification preference. `UID` points logically to the active account identifier. |
| `cms_content_nodes` | `id` | Category/content tree. `parent_id` is a self-reference; `children_sort` controls published child ordering; deletion is soft and batch-based. |
| `cms_content_translations` | `node_id`, `language` | One translated title/slug/body per node and language. `node_id` references `cms_content_nodes.id` with delete cascade and restricted ID updates; `slug` is globally unique across all languages. |

### CMS Column Contracts

These are the relevant columns in the final CMS schema. Do not re-open `installation/cms.sql` for routine queries; update this section when a migration changes a column.

| Table | Columns |
|---|---|
| `cms_info` | `name`, `value` |
| `cms_shop` | `product_id`, `name_item`, `price`, `Type`, `Value`, `desc_item`, `image`, `sort_order`, `category`, `enabled`, `min_quantity` |
| `cms_payments` | `username`, `txn_id`, `item_number`, `item_quantity`, `item_type`, `item_value`, `item_name`, `creation_date`, `update_date`, `ip`, `payment_method`, `receiver_id`, `receiver_account`, `mc_currency`, `net_money`, `mc_fee`, `payer_email`, `payment_type`, `payment_gross`, `mc_gross`, `payer_id`, `processing_date`, `payment_status`, `order_id` |
| `votes` | `voted_at`, `ip`, `Username` |
| `cms_rank_entities` | `UID`, `Name`, `Owner`, `ConquerPoints`, `Level`, `VIPLevel`, `HairStyle`, `Class`, `Money`, `Body`, `Face`, `Spouse`, `MoneySave`, `GuildID`, `GuildRank`, `GuildPoints`, `RacePoints`, `Donation`, `PKPoints`; source projections may additionally provide `Online` and `KOCounts` |
| `cms_rank_guilds` | `ID`, `Name`, `LaderUID`, `LeaderName`, `SilverFund`, `ConquerPointFund`, `Wins`, `Losts` |
| `cms_rank_arena` | `UID`, `Name`, `Level`, `Class`, `Mesh`, `ArenaPoints`, `CurrentHonor`, `HistoryHonor`, `TodayBattles`, `TodayWin`, `TotalLose`, `TotalWin`, `LastSeasonArenaPoints`, `LastSeasonWin`, `LastSeasonLose`, `LastSeasonRank` |
| `cms_token` | `id`, `type`, `value`, `creation_date` |
| `cms_rewards_logs` | `Owner`, `datetime`, `Type`, `Value`, `Reference`, `IP` |
| `cms_orders` | `order_id`, `owner`, `currency`, `total`, `total_after_discount`, `discount_applied`, `status`, `payment_method`, `txn_id`, `creation_date`, `update_date`, `payment_date` |
| `cms_orders_products` | `order_product_id`, `order_id`, `product_id`, `name_item`, `price`, `type`, `value`, `quantity`, `fulfillment_reference` |
| `cms_orders_temp_txn` | `order_id`, `payment_method`, `txn_id`, `creation_date` |
| `cms_orders_crypto_data` | `crypto_data_id`, `order_id`, `payment_method`, `reference_id`, `network`, `currency`, `transaction_hash`, `source_address`, `destination_address`, `block_number`, `amount`, `amount_in_usd`, `confirmation_current`, `confirmation_required`, `creation_date`, `update_date` |
| `cms_rate_limit_events` | `scope`, `subject`, `attempts`, `creation_date`, `blocked_until` |
| `cms_notifications` | `UID`, `type`, `enabled` |
| `cms_content_nodes` | `id`, `node_type`, `parent_id`, `list_style`, `image`, `preview_style`, `children_sort`, `author_uid`, `created_at`, `publish_at`, `sticky`, `protected`, `modified_at`, `deleted_at`, `deletion_batch_id`, `sort_order` |
| `cms_content_translations` | `node_id`, `language`, `title`, `slug`, `body`, `is_draft` |

### CMS Order Flow

1. `cms_shop` supplies catalog products.
2. Checkout creates one `cms_orders` row and one or more `cms_orders_products` rows.
3. A provider may reserve a transaction in `cms_orders_temp_txn`.
4. Payment processing links the provider result through `cms_payments.order_id` and `txn_id`.
5. Crypto providers store chain details in `cms_orders_crypto_data`.
6. Fulfillment writes claimable rewards to `purchasesunclaimed` using the account identifier and transaction ID.

`cms_orders_products` is intentionally denormalized: changing or deleting a catalog product must not change historical order descriptions, prices, types, or values.

### CMS Content Flow

`cms_content_nodes.parent_id` forms the category tree. `cms_content_translations.node_id` supplies language-specific content and is enforced by a foreign key that removes translations when a node is hard-deleted but rejects node-ID changes. A public item is resolved only when its translation is enabled, its node publication date is valid, all ancestors are published in the resolved language, and the node is not deleted. See [05-content-management.md](05-content-management.md) for editor and publication behavior.

## Shared Game/CMS Projection Map

The game database differs by `SOURCE_TYPE`, but the application expects these logical projections:

| Logical entity | Common tables | Relationship |
|---|---|---|
| Account | `accounts` | Primary account identity. Key may be `Username`, `ID`, `UID`, or a source-specific field; inspect the active model before writing joins. |
| Character/entity | `entities`, `characters`, or `cms_rank_entities` | Entity identity is normally `UID`; some sources use `EntityID` or a name-based projection. |
| Guild | `guilds` or `cms_rank_guilds` | Guild identity is normally `ID`; entity rows use `GuildID`. |
| Guild membership | `guildmembers` or source-specific guild columns | Name/UID relationship to character/entity and guild; source-dependent. |
| Arena | `arena` or `cms_rank_arena` | Arena row uses entity `UID`; ranking projections may join to `cms_rank_entities`. |
| Nobility | `nobility` or source-specific projection | `EntityUID` logically points to entity `UID`. |
| Inventory | `items` | `EntityID` logically points to the entity/character identity. |
| Online state | `online`, `online_players`, or `fonline` | Usually keyed by entity `UID`; schema and source differ. |
| Purchases/claims | `purchasesunclaimed` | `Owner` and `txn_id` link back to account and CMS payment/order flows. |

### Stable Application Joins

These joins are used by the active models and should be preserved when changing schemas:

```text
cms_rank_entities.GuildID -> cms_rank_guilds.ID
cms_rank_arena.UID -> cms_rank_entities.UID
nobility.EntityUID -> cms_rank_entities.UID or the active entity projection UID
items.EntityID -> the active entity/character UID
guildmembers.Name/UID -> the active entity/character identity
cms_orders_products.order_id -> cms_orders.order_id
cms_payments.order_id -> cms_orders.order_id
cms_orders_temp_txn.order_id -> cms_orders.order_id
cms_orders_crypto_data.order_id -> cms_orders.order_id
cms_content_translations.node_id -> cms_content_nodes.id
cms_content_nodes.parent_id -> cms_content_nodes.id
```

## Model-Facing Game Columns

This is the compact contract for game tables used by the models. It intentionally excludes server tables that the web application does not query.

| Logical table | Required columns consumed by models | Relationship/notes |
|---|---|---|
| `accounts` | `Username`, `Password`, `Email`, `IP`, `PreferredLanguage`, `State` or `Status`, `creation_date`, plus source identity `EntityID`, `ID`, or `UID` | `AccountModel` normalizes the source identity and privilege field to `UID`/`State`. `Username` is the owner key used by the default store/order flow. |
| `entities` / `cms_rank_entities` | `UID`, `Name`, `Body`, `Face`, `Class`, `Level`, `GuildID`, `GuildRank`, `Spouse`, `VIPLevel`, `Money`, `ConquerPoints`, `PKPoints`; optional `Online`, `KOCounts` | Emulator/stream models use the CMS ranking projection. `GuildID` links to the guild projection. |
| `characters` | `UID`, `Name`, `Body`, `CPs`, `Face`, `GuildID`, `Life`, `Mana`, `Strength`, `Agility`, `Vitality`, `Spirit` | Paradise source entity table. `CPs` is mapped to `ConquerPoints`. |
| `charstats` | `Name`, `Gold`, `GuildName`, `Job`, `Level`, `PKPoints`, `Spouse`, `VipLevel` | Paradise supplementary entity data; joined to `characters` by `Name`. |
| `guildmembers` | `Name`, `GuildRank` | Paradise supplementary guild membership; joined to `characters` by `Name`. |
| `guilds` / `cms_rank_guilds` | `ID`/`id`, `Name`, `SilverFund`, `ConquerPointFund` | Guild lookup and ranking. Source models may use the game table or the CMS projection. |
| `items` | `EntityID`, `position`, plus the item payload columns returned by `SELECT *` | Equipped-item queries filter `EntityID` and `position > 0`. |
| `nobility` | `EntityUID`, `Donation` | Ranking join to the entity UID. |
| `arena` | `EntityID`, `EntityName`, `ArenaPoints` | Arena ranking joins the source row to `cms_rank_entities.Name`; `EntityID` is the stable ordering identity. |
| `unions` | `GoldBricks` | Used only by sources exposing the union capability. |
| `online_players` | `UID`, `Online` | Stream/POD online list; `UID` joins `cms_rank_entities.UID`. |
| `online` | Source-specific `Name`, `OnlineCount` or aggregate `online` | Stream uses `Name = SERVER_NAME` and `OnlineCount`; Paradise reads the aggregate `online` value. |
| `fonline` | `online` | TopCO online-count source. |
| `purchasesunclaimed` | `UID`, `Owner`, `Type`, `Value`, `Claimed`, `txn_id` | Claimable fulfillment rows. `Owner` uses the active account owner field; `txn_id` links to payment records. |

### Source Column Aliases

The following aliases are part of the model contract and must not be casually renamed:

```text
accounts.EntityID -> normalized account UID (emulator)
accounts.ID       -> normalized account UID (stream-topco/POD variants)
accounts.UID      -> normalized account UID (paradise-style variants)
accounts.Status   -> normalized account State (paradise-style variants)
characters.CPs    -> entity.ConquerPoints
characters.Life   -> entity.Hitpoints
characters.VipLevel -> entity.VIPLevel
charstats.Gold    -> entity.Money
charstats.Job     -> entity.Class
```

When adding a source-specific column, update the source model mapper and this alias list together. Do not solve a source difference by changing the shared column names expected by controllers.

## Source Schema Inventory

These files are source snapshots, not separate application relationship contracts:

| Source/schema | File |
|---|---|
| Emulator | `Databases/emulator.sql` |
| Emulator Anjor | `Databases/emulator-anjor.sql` |
| Paradise | `Databases/paradise_conquer.sql` |
| TopCO | `Databases/topco/topconquer.sql` |
| POD 5693 | `Databases/pod-5693/db.sql` |
| POD 6609 | `Databases/pod-6609/db.sql` |
| Trinity 5700 | `Databases/trinity-5700/db.sql` |
| CMS/application migrations | `installation/cms.sql` |
| Shared game update | `installation/game-update.sql` |

The source snapshots contain many game-server tables that are not queried by the web application. Their names and columns are not interchangeable. For a source-specific change, first consult this map, then the active model under `src/model/<source>/`, and only then the matching source SQL snapshot.

## Maintenance Rules

- Update this document whenever a table is added, renamed, split, merged, or its logical key changes.
- Document both the physical key and the application-level relationship when no SQL foreign key exists.
- Record table renames such as `shop` -> `cms_shop`, `payments` -> `cms_payments`, and `rewards_logs` -> `cms_rewards_logs`.
- Keep source-specific differences in the source inventory or the relevant model documentation; do not duplicate the entire game SQL dump here.
- When this map conflicts with a SQL file, verify the migration history and update this map before implementing new queries.