Architecture Overview
Understanding JHorizon's dual-database architecture and workflow system
System Architecture
JHorizon uses a modern, dual-database architecture optimized for both operational efficiency and high-performance analytics.
Tech Stack
Frontend
- Next.js 15 (App Router)
- React 19
- TailwindCSS v4
- shadcn/ui components
- Framer Motion
Backend
- Convex (Serverless DB)
- Clerk (Auth + Billing)
- OpenRouter (Multi-AI)
- Firecrawl (Web Scraping)
Analytics
- Tinybird (OLAP)
- Real-time queries
- Time-series data
- Aggregations
Dual-Database System
JHorizon leverages two specialized databases to optimize performance and cost:
Convex - Operational Database
Purpose: User data, configuration, and workflow orchestration
Stores:
- User accounts and companies
- Organization configurations
- Tracked queries and brand settings
- Temporary analytics during processing
- Workflow state and execution tracking
Features:
- Real-time subscriptions for live updates
- Automatic scaling
- Built-in authentication
- Serverless deployment
Convex handles all transactional operations and orchestrates the analytics pipeline.
Tinybird - Analytics Database
Purpose: High-volume analytics with fast aggregation queries
Stores:
- LLM responses (all model outputs)
- Brand rankings per response
- Sentiment analysis data
- Citation URLs and scraped content
- Brand mentions in citations
Features:
- Columnar storage optimized for analytics
- Sub-second query performance
- Time-series data handling
- Efficient aggregations
Data flows from Convex → Tinybird during Phase 3 of workflows, then cleaned from Convex in Phase 4.
Multi-Tenant Hierarchy
Company (Agency/Brand)
├── Company Members (admin/user roles)
├── Organization #1 (Report/Brand)
│ ├── Organization Brands (own + competitors)
│ ├── Tracked Queries (15 default prompts)
│ └── Analytics Data (per week)
├── Organization #2
└── ...Access Control
Admin Users:
- Full access to all organizations in their company
- Can invite new members
- Manage billing and subscriptions
Regular Users:
- Access only organizations in
organization_accesstable - View-only or edit permissions per organization
- No billing access
Access is checked before every operation using the company_members table.
Workflow System
JHorizon uses Convex Workflow v2 for reliable, multi-phase analytics processing.
Data Flow
User Creates Org
↓
Configure Queries & Brands
↓
Trigger Workflow
↓
Phase 0: Generate LLM Responses (Convex)
↓
Phase 1: Extract Analytics (Convex)
↓
Phase 2: Scrape & Analyze Citations (Convex)
↓
Phase 3: Sync to Tinybird (Tinybird)
↓
Phase 4: Cleanup Convex
↓
Dashboard Queries TinybirdDual ID System
Why? Tinybird doesn't support Convex's Id<"table"> type for relationships.
Every analytics record stores two identifiers:
- Convex ID (
_id: Id<"table">) - Internal references - UUID (
citationId: string) - Tinybird relationships
Example:
analytics_citations: {
_id: "j57abc123...", // Convex internal
citationId: "uuid-v4", // For Tinybird
url: "https://example.com",
markdown: "...",
}
analytics_citations_brands: {
citationConvexId: "j57abc123...", // Query in Convex
citationId: "uuid-v4", // Join in Tinybird
brandName: "JHorizon",
}Performance Expectations
Phase 0
75 LLM calls 30-60s execution 5KB journal size 70-90% cache hits
Citation Scraping
Per URL 3-5s via Firecrawl Markdown reuse: 70-90% Parallel: 50 concurrent
Tinybird Queries
Analytics Sub-second response Time-series optimized Efficient aggregations