Database

The two tables dd-cards creates, what each column holds, and how templates and orders relate.

3 min read · 456 words

dd-cards creates two InnoDB tables. Both are created automatically at boot when AutoInsertSql is true, or by importing install/sql/dd-cards.sql by hand.

Every statement is CREATE TABLE IF NOT EXISTS, so importing repeatedly is safe and never migrates or resets existing data.

`dd_card_templates`

One row per saved design. This holds both the player’s own save slots and a frozen snapshot of the design each order was placed from.

CREATE TABLE IF NOT EXISTS `dd_card_templates` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `owner_citizenid` VARCHAR(50) NOT NULL,
    `slot_index` TINYINT UNSIGNED DEFAULT NULL COMMENT '1-5 = player save slot; NULL = order snapshot, not a slot',
    `name` VARCHAR(60) NOT NULL DEFAULT 'Untitled Card',
    `canvas_json` LONGTEXT NOT NULL,
    `material_paper` VARCHAR(30) NOT NULL DEFAULT 'paper_standard',
    `material_foil` VARCHAR(30) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_dd_card_templates_owner` (`owner_citizenid`),
    UNIQUE KEY `idx_dd_card_templates_owner_slot` (`owner_citizenid`, `slot_index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Column	Notes
`owner_citizenid`	The framework’s citizenid, not the server id
`slot_index`	`1`–`5` for one of the player’s save slots, `NULL` for an order snapshot
`name`	The card name set in the designer
`canvas_json`	The serialised design — text, images, shapes, strokes and layer order
`material_paper`	`paper_standard` or `paper_glossy`
`material_foil`	`gold_foil`, or `NULL` when no foil was chosen

The unique key on (owner_citizenid, slot_index) is what enforces the five-slot limit — a player cannot occupy the same slot twice.

Snapshots exist so that editing or deleting a saved template does not retroactively change what an already-placed order will print. That is why `slot_index` is nullable rather than the table being split in two.

`dd_card_orders`

One row per order.

CREATE TABLE IF NOT EXISTS `dd_card_orders` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `owner_citizenid` VARCHAR(50) NOT NULL,
    `template_id` INT UNSIGNED NOT NULL,
    `status` ENUM('processing', 'ready', 'completed', 'cancelled') NOT NULL DEFAULT 'processing',
    `quantity` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    `rush` TINYINT(1) NOT NULL DEFAULT 0,
    `price_paid` INT UNSIGNED NOT NULL DEFAULT 0,
    `materials_used` JSON NOT NULL,
    `ready_at` INT UNSIGNED NOT NULL COMMENT 'unix timestamp, os.time()',
    `notified_at` INT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `completed_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_dd_card_orders_owner` (`owner_citizenid`),
    KEY `idx_dd_card_orders_status` (`status`),
    CONSTRAINT `fk_dd_card_orders_template` FOREIGN KEY (`template_id`)
        REFERENCES `dd_card_templates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Column	Notes
`template_id`	The snapshot this order prints from. `ON DELETE CASCADE` — removing a template removes its orders
`status`	Must match `Config.OrderStatus`; see the warning below
`quantity`	How many cards, within `Config.BoxQuantity`
`rush`	`1` if the rush surcharge was paid
`price_paid`	The total actually charged, in whole units
`materials_used`	JSON map of material key to units consumed
`ready_at`	Unix timestamp from `os.time()`, not a MySQL datetime
`notified_at`	When the “order ready” message was sent; `NULL` until then

The `status` ENUM and `Config.OrderStatus` in [`config/shared.lua`](/docs/dd-cards/configuration/shared#orderstatus) must agree. Renaming a status in the config without altering the column — or the reverse — breaks every existing order.

Useful queries

Orders currently printing, oldest first:

SELECT id, owner_citizenid, quantity, rush,
       FROM_UNIXTIME(ready_at) AS ready_at
FROM dd_card_orders
WHERE status = 'processing'
ORDER BY ready_at;

Orders that finished but were never collected:

SELECT id, owner_citizenid, quantity, price_paid,
       FROM_UNIXTIME(ready_at) AS ready_at
FROM dd_card_orders
WHERE status = 'ready'
ORDER BY ready_at;

A player’s save slots:

SELECT slot_index, name, material_paper, material_foil, updated_at
FROM dd_card_templates
WHERE owner_citizenid = 'ABC12345' AND slot_index IS NOT NULL
ORDER BY slot_index;

Deleting rows from `dd_card_templates` cascades to `dd_card_orders`. Clearing out old templates will take any orders still attached to them with it — filter on `slot_index IS NOT NULL` if you only mean to prune save slots.