Skip to main content
Five tables, all append-only. Nothing is ever updated — which is what makes concurrent delivery receipts safe and lets a re-routed message keep both attempts.

​
How they join

The cdr_pnl view left-joins submit and final, so records with no outcome yet surface as PENDING — in-flight exposure stays visible rather than being silently excluded.
Every reader must take the earliest cdr_final row for a message. That is why progress receipts live in their own table: an ACCEPTD written into cdr_final would outrank the DELIVRD that followed it, in every list, every chart and the P&L.

​
cdr_submit

The load-bearing table. Part 1 is the only per-message record of what was charged.

​
Identity and shape

​
The money, both sides

All money is a bigint of ten-thousandths. Divide by 10 000 to display; never store a float.
NULL money is unknown, never zero. A rate that could not be determined stays distinguishable from a genuinely free message.Any query that coalesces these to zero reports an unpriced vendor as pure profit. margin_status exists to make that state explicit rather than inferred.
The money lives entirely on part_no = 1. Later segments are delivery rows with no money — writing amounts on every segment would multiply both sides by the segment count. Every money query needs WHERE part_no = 1.

​
Two currencies, and no conversion

cost_currency and price_currency are recorded per side. FireFlo never converts between them. A deployment buying in one currency and selling in another has a margin column that cannot be summed without doing the conversion yourself.

​
The three TLV columns

NULL means nothing recorded the column; {} means recorded and there were none. tlvs_sent is NULL for every non-SMPP vendor.

​
body, and why it is usually NULL

Recorded only if smsg.cdr.body is excerpt or full (V1.31.0). NULL on every row otherwise, and on every row written before the column existed. Searched with ILIKE over a trigram index (V1.32.0), always combined with a date range. That index needs pg_trgm; if the migration user may not create extensions, search still works, more slowly.

​
registered_delivery

The SMPP octet as the customer sent it, raw rather than decoded — it carries three independent requests and only some are acted on.
It answers a question the record could not answer before: did we owe this customer a receipt at all.NULL on rows written before the column existed and on an ingress with no such concept. That is not zero — zero is a claim about the customer, namely that they asked for nothing.

​
submit_status and vendor_attempt

Since V1.40.0. A cdr_submit row is now written for every attempt against a vendor, not only the one that finally succeeded or terminally failed — a retryable nack writes one too.
NULL is “not recorded”, never a claim of success. Rows written before V1.40.0, and every row from a non-SMPP vendor, carry NULL here — read it the same way as tlvs_sent, not as 0.
Before this column, a retry storm was visible only as an inflated submit count at the vendor, with no record of what it actually said on the attempts that failed — see Queues and retries for the retry budget these rows now make legible. (gateway_msg_id, part_no, retry_count, vendor_attempt) is the natural key as of V1.40.0, widened from (gateway_msg_id, part_no, retry_count) — retry_count alone does not change across in-worker retries at one vendor, so without vendor_attempt those rows would collide.

​
cdr_final and cdr_interim

Outcomes and progress, split for the reason above. They also have different lifetimes: dlr_state keeps the raw SMPP value alongside the coarse outcome, because EXPIRED, REJECTD and UNDELIV all bill as failures but mean different things.

​
cdr_rejected

One row per refusal, with reason and — since V1.27.0 — the vendor’s status code where the vendor was the one refusing. A refused row carries no money. price, cost and margin are NULL because credit was returned at the moment of refusal, which is what lets revenue queries sum those columns without an outcome filter.

​
usage_daily

One row per account, per product, per day. Daily rather than monthly so any period is a SUM over days, forever, with no second derived table to disagree with it. Billable columns for a finished day are final; delivery columns are restated by re-running the rollup, which is why it is idempotent.
The purge refuses to delete any day with no usage_daily row, and refuses the whole run rather than skipping. Deleting an unsummarised day destroys the consumption record permanently.

Call records

What each table is for, in prose.

Configuration tables

The other half of the schema.