Database Schema Design for Lightning Payment Data

A Lightning Network payment is not a simple debit-credit event. It is a composite state machine spanning invoice generation, multi-path HTLC negotiation, potential retries, and eventual settlement or failure. Payment processors and exchanges that attempt to force Lightning data into a generic double-entry ledger inevitably lose the causal chain linking a user's intent to a specific routing outcome. This makes it impossible to answer critical operational questions: Why did a payment fail? What was the effective fee rate for a specific corridor? Which channels are net revenue contributors after accounting for rebalancing costs?

A purpose-built data model treats core Lightning entities—channels, HTLCs, invoices, and routing events—as first-class objects with their own lifecycles. This allows for efficient indexing on payment_hash for atomic status queries, time-series aggregation of routing revenue by channel ID, and reliable joins between internal user IDs and Lightning payment attempts. Crucially, it separates the intent of a payment (the invoice) from its execution (the HTLC set), enabling precise reconciliation even when a single invoice is settled via multiple partial payments or when a keysend payment arrives without a pre-existing invoice record.

For teams building high-throughput payment systems, the schema design directly impacts the reliability of automated rebalancing logic and the accuracy of financial reporting. A schema that cannot distinguish between a routing fee earned and a rebalancing cost paid will silently corrupt profitability analysis. Chainscore Labs reviews data model designs to ensure they capture the full state machine of Lightning payments, enabling operators to build robust reconciliation pipelines, accurate revenue dashboards, and compliant audit trails that reflect the protocol's actual behavior, not a simplified abstraction of it.

Modeling Lightning Network payment data in a relational database requires a departure from standard on-chain accounting schemas. The core challenge is that a single logical payment can be composed of multiple Multi-Path Payment (MPP) shards, each represented by a distinct Hash Time-Locked Contract (HTLC) that may be resolved across several channels. A robust schema must treat the invoice, the payment attempt, and the individual HTLCs as separate, joinable entities to allow for atomic reconciliation. This design is critical for payment processors and exchanges that need to map asynchronous Lightning events to internal user IDs, order IDs, or compliance cases without losing the cryptographic proof of payment.

The schema must also handle the dual lifecycle of channels and their underlying Unspent Transaction Outputs (UTXOs). A channels table should track the dynamic state, including the channel_point, short_channel_id, and capacity, while a separate onchain_transactions table records the funding, splicing, and force-close events that anchor these channels to the Bitcoin base layer. Indexing strategies should prioritize the payment_hash for HTLC lookups and the short_channel_id for routing revenue aggregation. For time-series analysis of forwarding events, a dedicated hypertable or partitioned table is often necessary to maintain query performance at scale, as a single routing node can generate millions of forwarding records daily.

A key design decision involves the handling of spontaneous payments via Keysend, which lack a traditional invoice. The schema must accommodate a payment_preimage as the primary proof-of-payment without a corresponding invoice record, while still allowing the payment to be linked to a known user or purpose through custom TLV (Type-Length-Value) data. This pattern is essential for integrating Lightning Address protocol payments or automated service tips into a compliant audit trail. Teams building these systems should review their data model against edge cases like stuck payments, where an HTLC is held for hours before being resolved or failed, to ensure the schema correctly reflects the unsettled liability during that window.

Chainscore Labs provides data model review for payment processors and exchanges, ensuring that the schema design correctly handles the asynchronous and multi-part nature of Lightning payments. We assess indexing strategies for high-throughput environments, validate the integrity of joins between Lightning data and internal business systems, and help teams design reconciliation logic that can withstand the operational complexities of channel closures and routing failures.