Reporting & Financials
Precomputed financial reporting tables: weekly account rollups, net-revenue snapshots, and the global model inputs that feed the math.
These tables store precomputed financial reporting. Nothing here is edited by hand during normal use: rows are written by database functions and RPCs that run the financial math. This page describes what each table holds. The Data Flows page explains how the calculations produce these rows.
The functions that populate these tables are cataloged on the Automation page. Weekly Reports is filled in by generate_weekly_report(), and net_revenue_calculations is filled in by the net-revenue RPCs.
Weekly Reports
A precomputed weekly financial rollup per account for the window from date_start to date_end. Each row holds both totals for the week and the matching per-unit figures. Rows are generated by the generate_weekly_report() function, not written directly by the app.
| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key. Generated always as identity. |
| brand | integer | → references Brands. The account this rollup belongs to. |
| date_start | timestamp | Start of the reporting window. |
| date_end | timestamp | End of the reporting window. |
| total_units_shipped | bigint | Units shipped in the window. Defaults to 0. |
| total_sale_price | numeric | Total sale price for the window. Defaults to 0. |
| total_shipping_cost | numeric | Total shipping cost. Defaults to 0. |
| total_fulfillment_expense | numeric | Total fulfillment expense. Defaults to 0. |
| total_amazon_commission | numeric | Total Amazon commission. Defaults to 0. |
| total_net_revenue | numeric | Total net revenue for the window. Defaults to 0. |
| amazon_commission_percentage | numeric | Commission rate applied. Defaults to 0.15. |
| sale_price_per_unit | numeric | Sale price per unit. Defaults to 0. |
| shipping_cost_per_unit | numeric | Shipping cost per unit. Defaults to 0. |
| fulfillment_expense_per_unit | numeric | Fulfillment expense per unit. Defaults to 0. |
| amazon_commission_per_unit | numeric | Amazon commission per unit. Defaults to 0. |
| net_revenue_per_unit | numeric | Net revenue per unit. Defaults to 0. |
| created_at | timestamptz | Row creation time. Defaults to now(). |
Note that amazon_commission_percentage defaults to 0.15 (15%). Every other numeric total and per-unit column defaults to 0.
The brand column is a foreign key to Brands, so each weekly report is tied to exactly one account. There are no other foreign keys on this table.
net_revenue_calculations
A saved snapshot of one net-revenue calculation over a period. Each row records the inputs used and the averages and totals that came out of them. It mirrors the inputs and outputs of the net_revenue_calc() function. This table has no foreign keys. Snapshots stand on their own.
| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key. Generated always as identity. |
| start_of_period | timestamp | Start of the calculation period. |
| end_of_period | timestamp | End of the calculation period. |
| units_shipped | numeric | Units shipped in the period (input). |
| total_shipping_paid | numeric | Total shipping paid (input). |
| sale_price | numeric | Sale price used (input). |
| amazon_commission | numeric | Amazon commission (input). |
| gross_invoice_amount | numeric | Gross invoice amount (input). |
| invoice_credits_fulfillment_fees | numeric | Invoice credits for fulfillment fees (input). |
| amazon_commission_avg_per_item | numeric | Average Amazon commission per item (output). |
| fulfillment_expense_avg_per_item | numeric | Average fulfillment expense per item (output). |
| shipping_expense_avg_per_item | numeric | Average shipping expense per item (output). |
| net_revenue_avg_per_item | numeric | Average net revenue per item (output). |
| net_revenue_this_period | numeric | Total net revenue for the period (output). |
| created_at | timestamptz | Row creation time. Defaults to now(). |
These columns have no defaults; every value is written when the snapshot is created. Because there are no foreign keys, a snapshot is not linked to any account or brand; it captures a single calculation on its own.
Model inputs
Weekly input assumptions for the financial model. Each row holds the raw numbers for one week that the net-revenue math reads from. This table has no foreign keys.
| Column | Type | Notes |
|---|---|---|
| id | integer | Primary key. Defaults from the Model inputs_id_seq sequence. |
| Start_of_the_week | date | First day of the week these inputs cover. |
| End_of_the_week | date | Last day of the week these inputs cover. |
| Units_Shipped__Total_ | bigint | Total units shipped for the week. |
| Total_Shipping_Paid | numeric | Total shipping paid for the week. |
| Gross_Invoice_Amount__Fulfillment_Fees_ | numeric | Gross invoice amount including fulfillment fees. |
| Invoice_Credits__Fulfillment_Fees_ | numeric | Invoice credits for fulfillment fees. |
| Sale_Price__1_Pack_ | numeric | Sale price of a 1-pack. Defaults to 29.99. |
| Amazon_Commission__ | numeric | Amazon commission rate. Defaults to 0.15. |
| created_at | timestamp | Row creation time. Defaults to now(). |
| updated_at | timestamp | Last update time. |
| created_by | varchar | Who created the row. |
| updated_by | varchar | Who last updated the row. |
Two defaults drive the model: Sale_Price__1_Pack_ defaults to 29.99 and Amazon_Commission__ defaults to 0.15 (15%). If a weekly row leaves these blank, the calculation uses these values.
This table has no foreign keys. It is a global set of assumptions per week rather than a per-account record.
For how these inputs turn into net-revenue figures and weekly rollups, see Data Flows. For the functions that write these rows, see Automation.