> ## Documentation Index
> Fetch the complete documentation index at: https://hub.ypym.app/llms.txt
> Use this file to discover all available pages before exploring further.

# YPYM DBX Architectural Benchmark & Open-Source Lineage

> Transparent engineering comparison between SerpBear v3.1.0 and YPYM DBX Enterprise Core (v3.1.0), detailing storage, crawling governance, and modular upgrades.

## Open-Source Lineage & Engineering Philosophy

**Transparency is a core engineering principle at YPYM.**

When architecting **YPYM DBX (Digital Brand Experience)**, we consciously chose not to reinvent standard rank-tracking user interfaces from scratch. Instead, we evaluated existing open-source tracking tools and selected **SerpBear v3.1.0** (authored by Towfiq I.) as our initial foundational baseline for basic dashboard layouts and Google Search Console OAuth connectivity.

However, standard self-hosted trackers are typically designed for personal bloggers or small site owners. When evaluated against enterprise requirements—such as hundreds of monitored domains, tens of thousands of keywords, concurrent scraping pipelines, and budget compliance—traditional architectures encounter severe bottlenecks:

* Single-file SQLite databases lock under parallel write concurrency (`SQLITE_BUSY`).
* Storing years of SERP history as monolithic JSON strings in single table cells leads to memory exhaustion and database corruption.
* Unrestricted instant crawling causes accidental API budget depletion without pre-flight cost verification.

To elevate the system to an enterprise tier, we re-engineered the platform by borrowing battle-tested paradigms from established open-source projects:

1. **TimescaleDB Time-Series Engine**: Partitioned hypertables with automated 7-day chunking, automated compression, and sub-millisecond range scans.
2. **Enterprise Queueing Patterns (BullMQ / Temporal principles)**: Decoupled workers, isolated rate-limited scraping pools, and bounded retry mechanisms.
3. **Enterprise Financial Ledger Patterns**: Immutable accounting tables (`crawl_cost_ledger`) recording every single API cent expended.
4. **Relational Multi-Tenancy**: Normalized PostgreSQL schemas with AES-256 encrypted credential vaults.

***

## Comprehensive Architectural Benchmark Matrix

The following table provides an exhaustive, side-by-side comparison between the upstream baseline (**SerpBear v3.1.0**) and the current production release of **YPYM DBX (`v3.1.0`)**.

| # | Architecture Module / Capability | SerpBear v3.1.0 (Upstream Baseline) | YPYM DBX (Enterprise Core v3.1.0) | Upgrade Classification | Module Version |
| - | - | - | - | - | - |
| **1** | **Database Storage Engine** | **SQLite 3** (`database.sqlite`). Encounters file locking errors (`SQLITE_BUSY`) when parallel crawling exceeds 50 keywords. | **PostgreSQL 16 + TimescaleDB**. Supports high-throughput connection pooling via PgBouncer and millions of concurrent rows. | 🔄 **Total Overhaul** | `v2.0.0` |
| **2** | **Rank History Storage** | **Single JSON String**. The `history: string` column in the keywords table stores 365 days of SERP data inside one giant text cell (prone to corruption and memory bloat). | **TimescaleDB Hypertable** (`keyword_rank_history`). Every timestamped position is saved as an independent row with automatic 7-day chunking and 90% disk compression. | 🔄 **Total Overhaul** | `v2.0.0` |
| **3** | **Financial Cost Ledger** | ❌ **None**. Zero tracking, auditing, or logging of API crawling expenditures. | **TimescaleDB Hypertable** (`crawl_cost_ledger`). Automatically records USD spend, IDR equivalent, benchmark exchange rate, keyword count, domain, and trigger type. | ✨ **New Feature (DBX)** | `v1.0.0` |
| **4** | **Scraper Provider: DataForSEO** | ❌ **None**. Only supports SerpApi, ScrapingAnt, SearchApi, and CrazySERP (typically 5x to 10x more expensive per keyword). | **First-Class Adapter** (`DataForSeoAdapter`). Equipped with automated Base64 detection, exponential backoff retries, and live account balance telemetry. | ✨ **New Feature (DBX)** | `v1.2.0` |
| **5** | **Crawl Governance: Option C** | ❌ **Unrestricted Instant Crawl**. The *Reload All* button immediately executes all keywords without cost calculation or budget warnings. | **Hybrid Crawling & Smart Queue (Option C)**: Fixed daily schedule at **11:00 AM WIB** (04:00 UTC). Post-11 AM submissions enter `on_queue`, and *Reload All* is secured with a schedule notice. | ✨ **New Feature (DBX)** | `v1.0.0` |
| **6** | **Queue Threshold Guardrail** | ❌ **None**. Users can freely trigger manual crawls for 1 or 2 keywords repeatedly, leaking API budget on micro-batches. | **Strict ≥ 10 Keywords Gate**. Manual queue execution is disabled when queued keywords are \< 10, enforcing cost-efficient batching. | ✨ **New Feature (DBX)** | `v1.0.0` |
| **7** | **Financial Approval Modal** | ❌ **None**. Crawls begin immediately upon button press without user confirmation. | **Interactive Financial Confirmation**. Modal presents exact unit cost (\$0.0020), total USD and IDR, current DataForSEO balance, and post-crawl projected balance before execution. | ✨ **New Feature (DBX)** | `v1.0.0` |
| **8** | **Financial Status Bar UI** | ❌ **None**. No visibility into crawling costs or live provider balance. | **Real-Time Financial Status Bar**: Displays daily spend, 30-day projected monthly spend, queue counter, live provider balance, and segmented *All / Active / On-Queue* filter tabs. | ✨ **New Feature (DBX)** | `v1.0.0` |
| **9** | **Telemetry & Release Polling** | **External GitHub Polling**. Constantly polled `towfiqi/serpbear` releases, causing misleading upgrade notices (e.g. prompt to update from 1.0.0 to 3.1.0). | **Decoupled Local Changelog**. External GitHub telemetry stripped; replaced with YPYM DBX internal release registry and modular architecture inspector. | 🔧 **Adjusted** | `v1.1.0` |
| **10** | **Repository Structure** | **Standard Monolith**. All files mixed into a single directory (`pages/`, `components/`, `scrapers/`). | **NPM Workspaces Monorepo**: Decoupled into `@ypym-dbx/web`, `@ypym-dbx/database`, `@ypym-dbx/scrapers`, `@ypym-dbx/cost-governance`, and `@ypym-dbx/workers`. | 🔄 **Total Overhaul** | `v2.0.0` |
| **11** | **Multi-Tenancy & RBAC** | ❌ **Single Tenant**. Secured solely by a single global password defined in `.env`. | **Multi-Tenant Schema Foundation**: Relational tables for `tenants`, `projects`, and `tenant_credentials` with AES-256 encrypted credential vaults in PostgreSQL Drizzle. | 🏛️ **New Foundation** | `v1.0.0` |
| **12** | **Google Search Console (GSC)** | Single-user GSC OAuth and service account integration. | Preserved and reinforced with defensive Cryptr decryption fallbacks to prevent runtime crashes during credential renewal. | 🔧 **Adjusted** | `v1.0.1` |
| **13** | **AI Overview & GEO Tracker** | ❌ **Blind to AI Overview**. Only records traditional organic SERP links (positions 1–100). | **Dual-Engine AI Intelligence**: Native parser and rolling 90-day heatmap for Google AI Overviews alongside dedicated multi-LLM citation telemetry (ChatGPT, Gemini, Perplexity, Claude AI) with Dual-Phase automation and Crawl History. | ✨ **Active & Deployed** | `v3.1.0` |
| **14** | **Executive Telemetry & UI Engine** | Basic layout with static summary stats. | **Coolify-Style Telemetry & Portfolio Grouping**: Live keyword counts, active domain breakdown, cost ledgers, Top 10 penetration trajectory, and resilient multi-tenant organization. | ✨ **Active & Deployed** | `v3.1.0` |
| **15** | **Local Map Geo Grid (3x3, 5x5)** | ❌ **None**. Only supports single-location city names via text UULE parameter. | **Local Map Coordinate Grid**: Schema tables for `local_map_grids` (center lat/long, radius km, multi-coordinate pins) migrated to PostgreSQL. | 📋 **Staged in Schema (PRD)** | `v0.5.0` |

***

## Detailed Technical Deep Dives

### 1. Database & Persistence Overhaul

In upstream SerpBear, keyword position history was stored as a serialized JSON string inside a SQLite table cell:

```json theme={null}
// Upstream SerpBear history column format:
"{\"2026-03-01\": 4, \"2026-03-02\": 4, \"2026-03-03\": 3, ...}"
```

This design breaks down rapidly:

* Reading a single keyword's 7-day trend requires deserializing the entire historical lifecycle.
* Appending new daily positions requires reading the entire JSON, mutating it in Node.js memory, and writing it back to disk. Under concurrent scraper workers, this triggers SQLite `SQLITE_BUSY` locks and JSON corruption.

**The DBX 2.0 Solution:**
We migrated to a dedicated **TimescaleDB hypertable** (`keyword_rank_history`):

* Each daily rank observation is an immutable, indexed row containing `keyword_id`, `date`, `position`, `url`, and metadata.
* Data is automatically partitioned into 7-day chunks. Older chunks are compressed using TimescaleDB columnar compression, reducing disk footprint by up to 90% while allowing analytical aggregate queries across millions of data points in milliseconds.

### 2. Scraping Cost Efficiency

Standard SERP scrapers (such as SerpApi or ScrapingAnt) bill between **\$0.005 and \$0.010 per keyword** (\$5.00–\$10.00 per 1,000 queries). For an enterprise tracking 2,000 keywords daily across 10 domains, this translates to \$300–\$600 per month solely in scraping costs.

**The DBX 2.0 Solution:**
We introduced a native **DataForSEO v3** adapter:

* **Live Advanced Scraping**: **\$0.0020 per keyword** (\$2.00 per 1,000 queries)—a 60% to 80% reduction compared to standard third-party proxy wrappers.
* **Standard Priority Queue**: **\$0.0006 per keyword** (\$0.60 per 1,000 queries) for scheduled batch crawls where sub-second latency is not required.
* **Automated Balance Tracking**: Directly polls `/v3/appendix/user_data` to surface the exact live account balance in real time, preventing sudden provider lockouts.

### 3. Financial Cost Governance (Option C)

In raw rank trackers, users can inadvertently drain accounts by clicking *Refresh* repeatedly or triggering bulk crawls across thousands of keywords without realizing the financial implications.

**The DBX 2.0 Solution:**
We designed **Hybrid Crawling (Option C)**:

* Scheduled crawls execute once daily at **11:00 AM WIB** (04:00 UTC).
* Any keywords added after 11:00 AM WIB enter the `on_queue` state without triggering immediate billable API requests.
* Manual queue execution is guarded by a strict **10-keyword minimum threshold** and an itemized **Financial Approval Modal**, requiring the operator to review the exact USD and IDR financial commitment before any API task is dispatched.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.