RedM documentation

Murphy's Telegram & Mail

Advanced

Database

Auto-migration

Tables are created automatically on first startup. Missing columns and indexes are also auto-added on subsequent updates. No manual SQL import is required.

The reference schema is available in sql/murphy_post_schema.sql if you need it.

Schema

post_documents

Stores all letters and telegrams.

sql
CREATE TABLE IF NOT EXISTS `post_documents` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` ENUM('letter','telegram') NOT NULL,
  `sender_identifier` VARCHAR(64) NOT NULL,
  `sender_firstname` VARCHAR(64) NOT NULL,
  `sender_lastname` VARCHAR(64) NOT NULL,
  `sender_office_id` VARCHAR(64) NULL,
  `destination_mailbox_id` VARCHAR(64) NULL,
  `receiver_firstname` VARCHAR(64) NULL,
  `receiver_lastname` VARCHAR(64) NULL,
  `package_type` VARCHAR(64) NULL,
  `package_meta` TEXT NULL,
  `amount` INT NULL,
  `object` VARCHAR(128) NOT NULL,
  `body` TEXT NOT NULL,
  `signature` VARCHAR(64) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `available_at` DATETIME NULL,
  `template_id` VARCHAR(32) NULL,
  `font_id` VARCHAR(32) NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_sender` (`sender_identifier`),
  INDEX `idx_receiver_name` (`receiver_firstname`, `receiver_lastname`),
  INDEX `idx_sender_office` (`sender_office_id`),
  INDEX `idx_dest_mailbox` (`destination_mailbox_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

post_inbox

Delivery queue - tracks which documents are available to which characters.

sql
CREATE TABLE IF NOT EXISTS `post_inbox` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(64) NOT NULL,
  `document_id` INT UNSIGNED NOT NULL,
  `status` ENUM('scheduled','pending','delivered') NOT NULL DEFAULT 'scheduled',
  `scheduled_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `delivered_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_ident_status` (`identifier`, `status`),
  INDEX `idx_ident_status_avail` (`identifier`, `status`, `document_id`),
  INDEX `idx_doc` (`document_id`),
  CONSTRAINT `fk_inbox_document` FOREIGN KEY (`document_id`)
    REFERENCES `post_documents`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

post_contacts

Persistent favorite contacts per character.

sql
CREATE TABLE IF NOT EXISTS `post_contacts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `owner_identifier` VARCHAR(64) NOT NULL,
  `first_name` VARCHAR(64) NOT NULL,
  `last_name` VARCHAR(64) NOT NULL,
  `is_favorite` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_owner_name` (`owner_identifier`, `first_name`, `last_name`),
  INDEX `idx_owner_fav` (`owner_identifier`, `is_favorite`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Item metadata convention

When a letter or telegram is delivered to a player's inventory, the item carries this metadata:

lua
{
    document_id = 123,                          -- FK to post_documents.id
    description = "Subject - SenderFirst SenderLast",  -- Display text in inventory
    opened = false                              -- Flipped to true on first use
}

Useful queries

View recent documents

sql
SELECT id, type, sender_firstname, sender_lastname, receiver_firstname, receiver_lastname, object, created_at
FROM post_documents
ORDER BY id DESC
LIMIT 20;

View pending inbox entries

sql
SELECT i.id, i.identifier, i.status, d.type, d.object, d.sender_firstname, d.sender_lastname
FROM post_inbox i
JOIN post_documents d ON d.id = i.document_id
WHERE i.status = 'pending'
ORDER BY i.id DESC;

Count documents per type

sql
SELECT type, COUNT(*) as total FROM post_documents GROUP BY type;

Find mail for a specific character

sql
SELECT d.*
FROM post_inbox i
JOIN post_documents d ON d.id = i.document_id
WHERE i.identifier = 'CHARACTER_ID_HERE'
ORDER BY i.created_at DESC;

Last updated: September 9, 2026