Admin Fees

SipSop bills regulatory fees, government passthroughs, and sales tax as separate line items on every invoice. This page documents the fee catalog schema, how to create and update fees, carrier modes, and how fees flow into Stripe invoices and the InvoiceBreakdown component.

Fase 16h

This system was implemented in Fase 16h of the roadmap. The public fee table is served at GET /api/v1/fees/public and rendered at /fees on the landing site. The invoice breakdown component (InvoiceBreakdown) reads line items from trpc.billing.invoiceLineItems.

Database schema

Four tables in packages/db/src/schema/fees.ts:

fee_definitions

The master catalog of all fees. Key columns:

Column	Type	Description
id	uuid	PK
code	text	Unique identifier, e.g. REGULATORY_RECOVERY, SALES_TAX_NJ. UPPERCASE only, letters/numbers/underscore.
name	text	Display name shown to customers
type	text	One of: percentage, flat_per_line, flat_per_invoice, percentage_of_interstate
rate	numeric(10,8)	Rate value. For percentage types: 0.09 = 9%. For flat types: USD amount.
jurisdiction	text	FEDERAL, NJ, NY, NYC, CT, or ALL
category	text	provider_fee, government_passthrough, or tax
taxable	boolean	Whether this fee is part of the sales tax base. Provider fees = true; taxes themselves = false.
compliance_status	text	active, inactive, or configured_pending_registration
requires_registration	boolean	Whether a regulatory registration (FCC 499, NJ BPU, etc.) is required before activating
registration_authority	text	FCC, NJ_BPU, NJ_DOT, NY_DTF, etc.
registration_id	text	The actual cert/filer ID once approved
registration_status	text	not_started, in_progress, pending_approval, approved
effective_from	date	When this fee version started applying
effective_to	date	When this fee version stopped (NULL = currently active)
supersedes_id	uuid	Points to the previous version when a rate change creates a new row

Business rule: Never hard-delete fee definitions. Soft-delete via compliance_status='inactive' + effective_to=today. Rate changes create a new version (closes the old row, inserts a new one with supersedes_id). This preserves the historical snapshot for every invoice_line_items.fee_definition_id.

fee_definition_history

Immutable audit log populated by a Postgres trigger on every INSERT/UPDATE/DELETE to fee_definitions. Never write to this from application code.

Column	Description
operation	INSERT, UPDATE, or DELETE
previous_values	JSONB snapshot before the change (NULL for INSERT)
new_values	JSONB snapshot after the change (NULL for DELETE)
changed_by_user_id	Set via app.current_user_id session variable
change_reason	Set via app.change_reason session variable

tenant_billing_config

One row per tenant. Stores billing address, jurisdiction code, carrier mode override, tax exempt status, and per-client fee exclusions.

Column	Description
jurisdiction_code	Derived from billing address: NJ, NY-NYC, NY-WESTCHESTER, etc.
carrier_mode	reseller_retail, reseller_wholesale, or clec_direct — can override system default
tax_exempt	If true, no sales tax line items are added
tax_exempt_certificate	Certificate number for exempt clients
excluded_fee_codes	Array of fee_definitions.code to skip for this tenant. Used for negotiated discounts. Does NOT affect sales tax (use tax_exempt for that).

invoice_line_items

One row per line on a generated invoice. Links back to the fee_definition_id that was active at the time, preserving historical rates.

Column	Description
type	service, addon, usage, fee, or tax
code	The fee code (or plan code for service/addon)
unit_amount_cents	Integer cents, never floats
amount_cents	Total for the line: unit_amount_cents × quantity
fee_definition_id	FK to fee_definitions. NULL for service, addon, usage types.
jurisdiction	Jurisdiction that triggered this line

billing_system_config

Singleton row (id = 'singleton'). Holds the global default carrier mode and regulatory cert IDs (FCC 499 Filer ID, NJ BPU CLEC cert, NJ DoT sales tax cert, NY DTF cert).

Fee types and calculation

percentage          → amount = subtotalCents × rate  (e.g. rate=0.09625 = 9.625%)
flat_per_line       → amount = rate × 100 × activeLines  (rate in USD → cents)
flat_per_invoice    → amount = rate × 100  (fixed amount per invoice)
percentage_of_interstate → amount = interstateUsageCents × rate

All amounts are stored and calculated in integer cents. No floats anywhere in the billing pipeline.

Carrier modes

The carrier mode determines which fees SipSop is responsible for collecting and remitting:

Mode	Description	Who handles gov. passthroughs
reseller_retail	SipSop resells Twilio at retail. Twilio remits E911, FUSF, Cost Recovery. SipSop only adds provider fees + sales tax.	Twilio
reseller_wholesale	SipSop registered carrier, Twilio wholesale. SipSop collects and remits ALL active fees.	SipSop
clec_direct	SipSop as a direct CLEC. Maximum control, maximum compliance burden.	SipSop

Changing carrier mode is a super_admin-only action and requires:

• For reseller_wholesale: FCC 499 Filer ID set + all government passthrough fees approved
• For clec_direct: FCC 499 Filer ID + NJ BPU CLEC Certification

The API enforces these checks and blocks the change if requirements aren't met. A change reason and the exact confirmation string "I understand this changes regulatory liability" are required.

tRPC procedures (admin-fees router)

Access: billingClerkProcedure for CRUD; superAdminProcedure for carrier mode changes.

Procedure	Type	Description
adminFees.list	query	List fees with optional filters: jurisdiction, category, complianceStatus, search
adminFees.get	query	Get single fee by ID
adminFees.history	query	Paginated history from fee_definition_history
adminFees.create	mutation	Create new fee definition
adminFees.update	mutation	Update fee. If rate changes, closes old version and creates new one.
adminFees.deactivate	mutation	Soft-delete: sets compliance_status='inactive', effective_to=today
adminFees.testCalculation	query	Preview what a fee would charge given subtotal/lines/usage — no DB writes
adminFees.impactAnalysis	query	How many invoices/tenants used this fee in the last 90 days
adminFees.compliance	query	Compliance dashboard: totals by status + registration tracker by authority
adminFees.quarterlyReport	query	Sum of each fee collected in a quarter (format: Q1_2026) for remittance filing
adminFees.updateRegistration	mutation	Update registration status for all fees under an authority. Also updates billing_system_config cert IDs.
adminFees.getCarrierMode	query	Current mode + requirements checklist for wholesale/CLEC upgrade
adminFees.setCarrierMode	mutation	Change global carrier mode. super_admin only.

Creating a new fee

1. Go to /admin/billing/fees/new in the portal.
2. Required fields: code (UPPERCASE, e.g. E911_CT), name, type, rate, jurisdiction, category, taxable.
3. Set compliance_status:
   • inactive — fee exists in catalog but won't appear on invoices.
   • configured_pending_registration — fee is configured but waiting on regulatory approval.
   • active — fee will be added to new invoices. If requires_registration=true, you must have registration_status='approved' first (the API enforces this).
4. Provide a changeReason (minimum 10 characters) — this is stored in fee_definition_history.

Example: NJ 911 surcharge

code: E911_NJ
name: NJ 911 Emergency Response Surcharge
type: flat_per_line
rate: 0.90          ← $0.90 per active line per month
jurisdiction: NJ
category: government_passthrough
taxable: false
requires_registration: true
registration_authority: NJ_DOT

Updating a rate

When a government rate changes (e.g., FUSF percentage updates quarterly):

1. Navigate to /admin/billing/fees/{id} in the portal.
2. Update the rate field and provide changeReason.
3. The service will close the current version (effective_to=today) and create a new row (supersedes_id points to the old ID). The code stays on the new row.
4. Existing invoice_line_items.fee_definition_id still point to the old version — their historical rate is preserved.

Rate change versioning

Do not update rate for past periods manually. Always go through the UI/API which creates a new version. Bypassing this breaks the historical snapshot chain used for compliance reporting.

Public fee endpoint

GET /api/v1/fees/public (in apps/api/src/routes/fees-public.ts) returns all active fees with a 5-minute cache. It exposes only: code, name, type, rate, jurisdiction, category, taxable. Internal notes, registration IDs, and metadata are never exposed.

This endpoint feeds the /fees public page on the landing site.

Invoice breakdown component

apps/portal/src/components/billing/invoice-breakdown.tsx renders the per-invoice line item breakdown. It:

1. Calls trpc.billing.invoiceLineItems.useQuery({ invoiceId }).
2. Groups items by type: services/addons/usage first, then fees, then taxes.
3. Shows a subtotal after services, a subtotal before taxes, and a grand total.
4. Renders a tooltip (via the Info icon from lucide-react) for known fee codes using the FEE_INFO map hard-coded in the component.

Known fee codes with tooltips: REGULATORY_RECOVERY, ADMIN_FEE, SALES_TAX_NJ, SALES_TAX_NY_NYC, SALES_TAX_NY_STATE, E911_NJ, FUSF_FEDERAL.

Compliance dashboard

trpc.adminFees.compliance returns:

• systemConfig — current billing_system_config with cert IDs
• authorities — grouped by registration_authority, with counts of approved/pending/not-started fees
• totals — total/active/inactive/pendingRegistration fee counts

Use this to check if all required registrations are in place before switching carrier mode.

Quarterly compliance report

Run trpc.adminFees.quarterlyReport({ period: 'Q1_2026' }) to get the total amount collected for each active fee code during the quarter. Use this for:

• NJ Sales Tax quarterly filing (form ST-50) — sum of SALES_TAX_NJ line items
• FUSF remittance (if in wholesale mode)
• E911 surcharge remittance to state

The report groups by fee code and includes invoiceCount, tenantCount, and totalAmountCents.

Audit log

Every write operation (create, update, deactivate, carrier mode change, registration update) writes to audit_log. The table is append-only — no UPDATEs or DELETEs.

Actions tracked:

action	Trigger
fee.create	New fee created
fee.update	Non-rate update
fee.rate_change	Rate changed (new version created)
fee.deactivate	Fee soft-deleted
carrier_mode.change	Global carrier mode switched
registration.update	Registration status updated for an authority

To query recent audit events:

SELECT timestamp, action, entity_id, payload, ip_address
FROM audit_log
WHERE entity_type = 'fee_definition'
ORDER BY timestamp DESC
LIMIT 50;