Building PrismQTC: How I Built an Autonomous B2B Quote-to-Cash & Pricing Governance Platform

The platform is designed around a decoupled, high-throughput micro-modular topology:

┌──────────────────────────────────────────────────────────┐
│                      FRONTEND LAYER                      │
│  React 19 (SPA)  │  TypeScript 5.8  │  Vite 6            │
│  Tailwind CSS v4 │  Socket.IO       │  Motion + Lucide   │
│  SWR Cache       │  Axios Client    │  jsPDF + docx      │
└────────────────────────────┬─────────────────────────────┘
                             │ HTTP / WebSocket (Port 80)
                             ▼
┌──────────────────────────────────────────────────────────┐
│                 GATEWAY & REVERSE PROXY                  │
│  Nginx (Alpine) — Routing, Assets, Caching, WS Upgrade   │
└────────────────────────────┬─────────────────────────────┘
                             │
              ┌──────────────┴──────────────┐
              ▼                             ▼
┌───────────────────────────┐ ┌────────────────────────────┐
│        BACKEND API        │ │   ASYNC WORKERS & CACHE    │
│ Node.js (ESM) + Express   │ │ Redis Stack 7 (Broker/AOF) │
│ Socket.IO Real-Time Hub   │ │ BullMQ Job Queue Workers   │
│ Argon2 + Dual JWT Auth    │ │ node-cron Schedule Engine  │
│ Raw pg (withTenantContext)│ │ RedisInsight Web Dashboard │
└─────────────┬─────────────┘ └──────────────┬─────────────┘
              │                              │
              └──────────────┬───────────────┘
                             ▼
┌──────────────────────────────────────────────────────────┐
│                      DATABASE LAYER                      │
│  PostgreSQL 16 Alpine                                    │
│  ├─ Row-Level Security (RLS) & Column Grants (CLAC)      │
│  ├─ PL/pgSQL Reactive Triggers (Margin & Risk Math)      │
│  └─ Stored Procedures (Atomic Orders, Stalled Sweeps)    │
└────────────────────────────┬─────────────────────────────┘
                             │ Metrics & Log Streams
                             ▼
┌──────────────────────────────────────────────────────────┐
│                OBSERVABILITY & MONITORING                │
│  Prometheus v2.52 (Metrics) │ Loki v3.0 (Log Streams)    │
│  Grafana v11.0 (Pre-Provisioned Unified Dashboards)      │
└──────────────────────────────────────────────────────────┘

Complete Technology Stack Matrix

Why I Ditched ORMs for Native PostgreSQL Drivers

Most enterprise applications implement multi-tenancy by appending WHERE tenant_id = ? to every query via an ORM middleware. A single developer omission, an un-scoped raw query, or an ORM bug can cause cross-tenant data leaks.

In PrismQTC, multi-tenancy is enforced directly by PostgreSQL using Row-Level Security (RLS) with transaction-scoped configuration settings:

BEGIN;
  -- Scoped strictly to this transaction block
  SET LOCAL app.current_tenant_id = 'a1b2c3d4-e5f6...';
  SET LOCAL app.current_actor_type = 'staff';
  SET LOCAL app.current_user_id = 'f9e8d7c6-b5a4...';
  SET LOCAL app.current_role = 'sales_rep';
  SET LOCAL ROLE app_role_staff;
  -- The kernel guarantees no other tenant rows can be read
  SELECT id, quote_number, total_amount 
  FROM quotations 
  WHERE id = $1;
COMMIT;

Why SET LOCAL is Crucial

SET LOCAL guarantees that session configuration variables vanish automatically on COMMIT or ROLLBACK. When connections return to the node-postgres pool, no cross-tenant parameters linger.

All operational tables declare:

ALTER TABLE quotations ENABLE ROW LEVEL SECURITY;
ALTER TABLE quotations FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_policy ON quotations
    FOR ALL
    USING (
      tenant_id = current_setting('app.current_tenant_id', true)::UUID
    );

Column-Level Access Control (CLAC)

RLS restricts rows, not columns. To stop external customers from seeing sensitive columns like unit_cost, total_cost, line_margin_pct, or blended_risk_score, PrismQTC establishes two database roles:

1. app_role_staff: Full read/write access to all table columns.
2. app_role_customer_portal: Strict column whitelist:

GRANT SELECT (
    id, tenant_id, quote_number, status, 
    subtotal_amount, tax_amount, total_amount, 
    currency, customer_notes, created_at
) ON quotations TO app_role_customer_portal;
-- Customer portal role CANNOT select unit_cost or line_margin_pct
-- Attempting to query them triggers an instant database error

Complete Schema Entity Map (18 Tables)

• tenants: Multi-tenant organization boundaries, subdomains, currencies.
• users: Internal staff members (Admins, Sales Managers, Reps, Finance).
• customers: Client organizations assigned to tiers (Bronze to Platinum).
• customer_portal_users: External customer contacts authenticated via magic links.
• product_categories: Category discount ceilings (e.g., Hardware 10%, Services 15%).
• products: Base catalog items, inventory levels, cost vs retail, tax rates.
• product_variants: Attribute variations with price deltas.
• price_lists & price_list_items: Tier-specific price overrides per currency.
• upsell_rules: Product recommendation triggers based on margin thresholds.
• quotation_requests & items: Inbound customer RFQs from the portal.
• tier_discount_ceilings: Category-level discount thresholds per customer tier.
• quotations & quotation_lines: The core living transaction document.
• approval_audit_logs: Immutable ledger of manager/finance approval decisions.
• quotation_negotiation_threads: Real-time chat threads and counter-offers.
• shipments & shipment_items: Split-warehouse dispatch tracking and backorders.
• invoices & invoice_lines: Reconciled billing statements with delivery matching.
• subscriptions: Contract recurrence schedules and seat proration parameters.
• deal_health_alerts: Proactive anomaly flags (stalled deals, margin breaches).

In high-volume B2B sales, reps often slash prices to hit quotas, eroding margins. PrismQTC automates pricing discipline directly inside the PostgreSQL database engine.

The Blended Risk Score Formula

The platform computes a mathematically weighted Blended Risk Score ($0 - 10$ scale) for every quotation:

Where:

• \(\text{Line Discount} \le \text{Tier Ceiling} \implies \text{Risk} = 0\)
• \(\text{Line Discount} > \text{Tier Ceiling} \implies \text{Risk} = \left(\frac{\text{Discount} - \text{Ceiling}}{\text{Ceiling}}\right) \times 10\)
• \(\Delta_{\text{Margin}}\) triggers if Gross Margin drops below the company floor ($18%$).
• \(\Delta_{\text{Rep}}\) factors in the rep's trailing discount history.

Visual Risk Thresholds

• 🟢 Score $< 5.0$ (Low Risk): Complies with ceilings; instant client dispatch permitted.
• 🟡 Score \(= 5.0\) (Moderate Risk): Max ceiling reached; advisory review suggested.
• 🔴 Score $> 5.0$ (Critical Risk): Threshold breached; auto-routed to approval queue.

Reactive PL/pgSQL Trigger Engine

Calculations are executed instantly by PostgreSQL triggers:

CREATE OR REPLACE FUNCTION trg_calculate_quote_line_financials()
RETURNS TRIGGER AS $$
DECLARE
    v_unit_cost NUMERIC(12, 2);
BEGIN
    SELECT unit_cost INTO v_unit_cost 
    FROM products WHERE id = NEW.product_id;
NEW.line_subtotal := NEW.quantity * NEW.unit_price;
NEW.line_discount_amount := NEW.line_subtotal * 
    (NEW.applied_discount_pct / 100.0);
NEW.line_total := NEW.line_subtotal - 
    NEW.line_discount_amount;
NEW.line_cost_subtotal := NEW.quantity * v_unit_cost;
IF NEW.line_total > 0 THEN
NEW.line_margin_pct := ((NEW.line_total -
NEW.line_cost_subtotal) / NEW.line_total) * 100.0;
ELSE
NEW.line_margin_pct := 0.0;
END IF;
RETURN NEW;

END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_quote_lines_calc
BEFORE INSERT OR UPDATE ON quotation_lines
FOR EACH ROW
EXECUTE FUNCTION trg_calculate_quote_line_financials();

Multi-Tier Approval Chain Automation

When a quote breaches risk limits:

1. Manager Tier ($>10%$ Discount): Moves to pending_manager.
2. Finance Tier ($>15%$ Discount or Gross Margin $< 20%$): Moves to pending_finance.
3. Every decision writes an immutable log to approval_audit_logs.

When inventory is dispersed across regional facilities, PrismQTC dynamically splits line items across depots:

               [ PRISMQTC ORDER #PQ-9281 ]
                            |
                +-----------+-----------+
                |                       |
          [ MAIN WAREHOUSE ]      [ EAST DEPOT ]
          * 85 Units In-Stock     * 15 Units In-Stock
          * Lowest Zone Rate      * Regional Express
                |                       |
          [ SHIPMENT #01 ]        [ SHIPMENT #02 ]
                \                       /
                 +----------+----------+
                            |
                [ BACKORDER CONSOLIDATED ]
                [ TRANSIT COST MINIMIZED ]

Split Shipment Execution Flow

1. Evaluates inventory for each line item in real-time.
2. If Warehouse A has partial stock, it creates Shipment #01 and assigns a tracking number.
3. The remaining quantity routes to Warehouse B (Shipment #02) or an Automated Backorder.
4. Invoices map directly to shipment_id records, ensuring buyers are billed only for delivered inventory.

PrismQTC replaces static email PDFs with a synchronized Customer Portal (/portal).

Dual-Token Authentication

• Internal staff authenticate via /api/auth/login and receive a staff JWT in an httpOnly cookie.
• External buyers authenticate via /api/portal/auth/login or magic links, receiving a scoped customer portal token.

Bidirectional WebSocket Negotiation

Powered by Socket.IO:

const socket = io({ path: '/socket.io' });
useEffect(() => {
  socket.emit('join:quote_negotiation', { quoteId });
  socket.on('quote:counter_offer_received', (data) => {
    toast.info(`Counter-offer submitted: ₹${data.newTotal}`);
    mutate(); // Optimistic SWR refresh
  });
  socket.on('quote:locked_by_staff', () => {
    setIsLocked(true);
    toast.warning('Quote is under management review.');
  });
  return () => {
    socket.emit('leave:quote_negotiation', { quoteId });
  };
}, [quoteId]);

Atomic Confirmation via Stored Procedure

Accepting a proposal executes an atomic PostgreSQL stored procedure:

CREATE OR REPLACE PROCEDURE sp_customer_confirm_quotation(
    p_quotation_id UUID,
    p_customer_id UUID,
    p_portal_user_id UUID
)
LANGUAGE plpgsql
AS $$
BEGIN
    PERFORM 1 FROM quotations 
    WHERE id = p_quotation_id 
      AND customer_id = p_customer_id 
      AND status IN ('sent', 'under_negotiation')
    FOR UPDATE;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Quotation cannot be confirmed in current state.';
    END IF;
    UPDATE quotations 
    SET status = 'confirmed',
        confirmed_at = NOW(),
        confirmed_by_portal_user_id = p_portal_user_id
    WHERE id = p_quotation_id;
    PERFORM fn_allocate_split_warehouse_shipments(p_quotation_id);
END;
$$;

Deals bundle one-off hardware, professional services, and recurring subscriptions into a single contract schedule.

Hybrid Order Proration

Subscriptions compute day-precise seat proration when licenses are added mid-billing cycle:

4-Stage Invoicing Stepper

[ Order Confirmed ] ──▶ [ Shipped ] ──▶ [ Invoiced ] ──▶ [ Paid ]

Razorpay Integration

1. Backend creates an order via Razorpay SDK (razorpay.orders.create).
2. Client mounts checkout modal with brand accents (#ff3b30).
3. Backend cryptographically verifies the payment signature (crypto.createHmac('sha256', secret)) before marking the invoice as PAID.

The Deal Health Dashboard (/dealhealth) monitors three automated anomaly sweeps:

┌──────────────────────────────────────────────────────────┐
│                    DEAL HEALTH RADAR                     │
├──────────────────────────────────────────────────────────┤
│ [STALLED]   Quote #QT-4401 | 14 Days Inactive | Action   │
│ [MARGIN]    Quote #QT-9920 | Rep: R. Rep (-6.2% vs Avg)  │
│ [SLIPPAGE]  Depot East Shortfall on SKU: SRV-2U-PRO      │
└──────────────────────────────────────────────────────────┘

1. Stalled Deals: Flags quotations idling past expected closing dates.
2. Discount Anomalies: Rolling z-score analysis flags quotes where discounts exceed a rep's 90-day baseline.
3. Delivery Slippage: Detects backorders threatening customer delivery SLAs.
4. Autonomous Sweeper: governanceSweeper.js executes sp_flag_stalled_deals() hourly via node-cron.

Modern React 19 Architecture

• Zero Heavy UI Kits: Built with native Tailwind CSS v4 and a unified token system.
• SWR Caching: Real-time optimistic mutations across tables, modals, and badges.
• Role-Based Routing: Guards enforce route access by user role (admin, sales_manager, sales_rep, finance, customer).

Global CSS Design System (frontend/src/global css/)

• tokens.css: Color ramps, semantic states (--app-brand-red: #ff3b30), border radiuses.
• tables.css: High-density ledger styling with sticky headers and numeric alignments.
• cards.css: Clean, border-accented containers with subtle elevation.
• badges.css: High-contrast WCAG AAA compliant workflow badges.
• modals.css: Fluid backdrop blurs and focus traps.

The Landing Page Experience

• Kinetic Scramble CTA Buttons: Characters scramble on hover using monospace text manipulation.
• Word-by-Word Scroll Darkening: ManifestoScrollSection.tsx darkens words as the user scrolls.
• Crosshairs & Target Glyphs: Blueprint design accents (RedCrosshair.tsx).
• Terminal ASCII Art: High-contrast system topologies rendered in pure text.

PrismQTC performs 100% of document assembly client-side in the browser, saving significant server memory.

Vector PDF via jspdf & jspdf-autotable

Renders high-DPI vector PDFs directly in memory with branded headers and itemized financial tables:

export async function exportInvoicePDF(invoice: any) {
  const doc = new jsPDF();
doc.setFillColor(15, 23, 42);
doc.rect(0, 0, 210, 32, 'F');
doc.setTextColor(255, 255, 255);
doc.setFontSize(18);
doc.setFont('helvetica', 'bold');
doc.text('PRISMQTC', 14, 16);
autoTable(doc, {
startY: 45,
head: [['#', 'SKU / Item', 'Qty', 'Unit Price', 'Total']],
body: invoice.lines.map((l: any, i: number) => [
i + 1, l.name, l.quantity, ₹${l.unit_price}, ₹${l.total}
]),
theme: 'grid',
headStyles: { fillColor: [15, 23, 42] },
});
doc.save(Invoice_${invoice.invoice_number}.pdf);
}

Native Word (.docx) via docx

Builds real Microsoft Word .docx documents with native paragraph runs, table shading, and styling, dispatched directly via file-saver.

The entire stack is containerized with Docker and Docker Compose (docker-compose.yml):

docker compose up -d

Infrastructure Container Fleet

Real-Time Observability

• Prometheus Metrics: Backend exposes /metrics measuring response latencies, status codes, and connection pool saturation.
• Loki Log Ingestion: Structured JSON application logging with request trace IDs.
• RedisInsight: GUI exposed on port 8001 for real-time queue inspection and key-space debugging.

Distributed Rate Limiting & Brute-Force Defense

To safeguard authentication and financial endpoints across multi-tenant deployments, PrismQTC integrates distributed rate limiting:

• Redis-Backed State Store: Uses express-rate-limit paired with rate-limit-redis, synchronizing rate limit counters across all backend instances via Redis key prefix rl:auth:.
• Targeted Sliding Window: Enforces a 15-minute window permitting up to 50 attempts per IP address (windowMs: 15 * 60 * 1000, max: 50).
• Selective POST Filtering: Selectively targets credential submissions (skip: (req) => req.method !== 'POST'), preventing unnecessary throttling on read traffic.
• Fail-Open Resilience: Configured with passOnStoreError: true, ensuring enterprise users are never blocked during brief Redis reconnections.

Async Worker Queues, Schedulers & Transactional SMTP

• BullMQ Task Queues: Decouples heavy background operations (audit logs, alerts) from the HTTP cycle with Redis Stack as the message broker.
• Autonomous Node-Cron Sweepers: Runs scheduled background tasks hourly, calling sp_flag_stalled_deals() to flag inactive deals and notify account owners.
• Nodemailer Transactional SMTP: Automatically fires email dispatches when approvals are submitted, counter-offers arrive, or deals are confirmed.

💡 Key Lessons

1. Push calculations to the database kernel: Offloading margin math and risk thresholds to PL/pgSQL triggers eliminated race conditions and made discount rules tamper-proof.
2. Client-side document compilation is an enterprise superpower: Generating PDFs and DOCX files entirely in the client saved significant server memory and eliminated a major infrastructure bottleneck.
3. Multi-tenancy belongs in the database, not in application logic: Using transaction-scoped PostgreSQL session variables (SET LOCAL) provided true zero-trust isolation without the maintenance burden of manually appending tenant IDs across hundreds of queries.

🔮 Future Roadmap

• Autonomous AI RFP Parsing: Ingesting messy PDF RFQs directly into structured quote lines using Gemini Flash.
• Dynamic Currency Hedging: Automatic real-time foreign exchange adjustments on multi-currency quotations.
• EDI 850 / 810 Connectors: Native electronic data interchange integration for enterprise supply chain partners.

🏁 Summary

PrismQTC demonstrates that high-velocity B2B sales operations don't need to suffer under the weight of slow, rigid enterprise software. By pairing PostgreSQL's native security kernel with a modern React 19 real-time client, we can build sales systems that are self-governing, mathematically sound, and a pleasure to use.

Built with React 19, TypeScript, Node.js, PostgreSQL 16, Redis Stack, and Docker.