Xeno Mini CRM

An AI-native Mini CRM for helping direct-to-consumer brands reach their shoppers through intelligent, data-driven campaigns.

1. System Architecture
2. Tech Stack
3. Data Model
4. Index Strategy
5. Segment Rule Engine
6. Async Delivery Loop
7. Conversion Attribution
8. Data Ingestion Quality
9. API Contracts
10. Running Locally
11. Scale Assumptions & Production Gaps

┌──────────────────────────────────────────────────────────────────┐
│                          BROWSER / CLIENT                        │
└─────────────────────────┬────────────────────────────────────────┘
                          │  REST
                          ▼
┌──────────────────────────────────────────────────────────────────┐
│                     /backend  (Node.js + Express)                │
│                                                                  │
│  ┌──────────┐  ┌──────────┐  ┌───────────┐  ┌───────────────┐  │
│  │customers │  │segments  │  │ campaigns │  │   receipts    │  │
│  │  /orders │  │  /rules  │  │ /dispatch │  │  /callback    │  │
│  └────┬─────┘  └────┬─────┘  └─────┬─────┘  └──────┬────────┘  │
│       │             │              │                │            │
│       └─────────────┴──────────────┴────────────────┘           │
│                              │                                   │
│                        Drizzle ORM                               │
│                              │                                   │
│                         pg Pool (max:10)                         │
└──────────────────────────────┬───────────────────────────────────┘
                               │
                               ▼
                   ┌───────────────────────┐
                   │  PostgreSQL (Neon /   │
                   │  Supabase)            │
                   └───────────────────────┘
                               ▲
          async POST /callback  │
                               │
┌──────────────────────────────┴───────────────────────────────────┐
│              /channel-service-stub  (Node.js)                    │
│                                                                  │
│  Receives send requests → simulates network delay → fires        │
│  status callbacks (sent, delivered, opened, clicked, failed)     │
│  back to CRM receipt endpoint                                    │
└──────────────────────────────────────────────────────────────────┘

The three services are independently deployable. In development they run on separate ports (3001, 3002). In production each is a separate Railway/Render service.

customers
  ├── orders (customer_id FK)          many orders per customer
  │     └── attributed_campaign_id FK  nullable conversion attribution
  ├── campaign_deliveries              one delivery row per campaign per customer
  │     └── campaign_id FK
  │
segments (segment_definitions)
  └── campaigns                        one saved segment can power many campaigns
        └── campaign_deliveries        one delivery row per recipient

Core shopper profile. Email is the deduplication key.

Reusable, named rule sets. Campaigns take a snapshot at launch.

A campaign ties a message template to a channel and an audience.

Purchase records. The attributed_campaign_id column implements conversion tracking.

One row per recipient per campaign. The message_id is the idempotency key for the receipt endpoint.

Every index has a specific query it's designed to accelerate:

Why not index customers.tags?
Array containment queries ($val = ANY(tags)) are not efficiently served by a standard BTREE index. A GIN index (CREATE INDEX … USING GIN (tags)) would help at scale, deferred from this scope.

The segmentCompiler.ts module converts a JSON rule array into a parameterised PostgreSQL query with no in-memory filtering.

interface SegmentRule {
  field: 'total_spend' | 'order_count' | 'last_purchase_days' | 'city' | 'tag';
  operator: 'gte' | 'lte' | 'gt' | 'lt' | 'eq' | 'neq' | 'contains';
  value: string | number;
}

Rules are classified as WHERE (non-aggregate) or HAVING (aggregate):

• city, tag → plain WHERE predicates against customers columns
• total_spend, order_count, last_purchase_days → aggregate expressions in HAVING

Fast path (no aggregate rules): Skips the JOIN and GROUP BY entirely.

SELECT id, email, name, phone, city
FROM customers c
WHERE c.city = $1

Full RFM path:

SELECT c.id, c.email, c.name, c.phone, c.city
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.city = $1
GROUP BY c.id, c.email, c.name, c.phone, c.city
HAVING COALESCE(SUM(o.total_amount::numeric), 0) >= $2
  AND COUNT(o.id) >= $3
  AND EXTRACT(EPOCH FROM (NOW() - MAX(o.created_at))) / 86400.0 <= $4

Notable choices:

• LEFT JOIN (not INNER JOIN) so customers with zero orders still appear and fail aggregate conditions explicitly rather than being silently excluded.
• COALESCE(SUM(...), 0) so a total_spend gte 0 rule correctly includes customers with no orders.
• All user-supplied values are passed as $N parameters — zero risk of SQL injection.

In Phase 3, the natural language input "customers who spent over ₹5000 in the last month" is sent to Claude Haiku (claude-haiku-4-5-20251001) via a direct POST to the Anthropic API, which returns:

[
  { "field": "total_spend", "operator": "gte", "value": 5000 },
  { "field": "last_purchase_days", "operator": "lte", "value": 30 }
]

This JSON passes through validateSegmentRules() and directly into compileSegmentQuery().

CRM backend                          Channel Service Stub
──────────────                       ────────────────────
POST /api/channel/send  ──────────►  Receives message
  { messageId, recipient,            Simulates delay (1–5s)
    message, channel }               
                                     Fires webhook:
◄──────────────────────  POST /api/receipts/callback
                           { messageId, status: "sent" }
                           { messageId, status: "delivered" }
                           { messageId, status: "clicked" }  (probabilistic)

1. Callback arrives with { messageId, status }.
2. UPDATE campaign_deliveries SET status = $2, <status>_at = NOW() WHERE message_id = $1 AND status != $2
   The AND status != $2 guard means replayed callbacks with the same status are no-ops.
3. The UNIQUE index on message_id means a lookup is always O(1).
4. Status transitions are enforced at the application layer — a DELIVERED event cannot downgrade a CLICKED record.

If DELIVERED and CLICKED arrive simultaneously for the same message_id, both hit the same DB row. PostgreSQL's row-level locking ensures only one UPDATE executes at a time; the second Update targets the post-first-update state and applies cleanly (since CLICKED is a later state than DELIVERED).

When a customer places a new order, the orders service runs a lightweight attribution check:

SELECT cd.campaign_id
FROM campaign_deliveries cd
WHERE cd.customer_id = $1
  AND cd.status IN ('clicked', 'delivered')
  AND cd.clicked_at >= NOW() - INTERVAL '7 days'
ORDER BY cd.clicked_at DESC
LIMIT 1

If a row is found, orders.attributed_campaign_id is set to that campaign's ID. This is a last-touch, 7-day click-through attribution window — a standard baseline in CRM analytics.

This design keeps conversion tracking inside the CRM's relational model rather than as a synthetic channel status, which more accurately reflects real-world brands where purchases happen on the brand's own platform.

dataIngestion.ts normalises raw inputs before any DB write:

Batch ingestion deduplicates within the batch by email before the DB call. DB-level deduplication is handled by INSERT … ON CONFLICT (email) DO UPDATE (upsert pattern, implemented in Phase 2).

Analytics response shape:

{
  "funnel": { "total": 1000, "queued": 0, "sent": 950, "delivered": 900, "opened": 450, "clicked": 158, "failed": 50 },
  "rates": { "deliveryRate": 0.9474, "openRate": 0.5000, "clickRate": 0.3511, "failureRate": 0.0526 },
  "conversions": { "count": 12, "revenue": 14850.00 }
}

Valid status values: sent → delivered → opened → clicked (or failed from any non-terminal state)

# 1. Install dependencies
cd backend && npm install

# 2. Configure environment
cp .env.example .env
# Fill DATABASE_URL with your Neon/Supabase connection string
# Optional: ANTHROPIC_API_KEY for AI features

# 3. Generate and run migrations
npm run db:generate
npm run db:migrate

# 4. Start backend dev server
npm run dev
# → http://localhost:3001/health

# 5. In a second terminal — start the channel service stub
cd channel-service-stub && npm install && npm run dev
# → http://localhost:3002/health

cd frontend && npm install && npm run dev
# → http://localhost:5173

The Vite dev server proxies all /api/* requests to localhost:3001 — no CORS configuration needed.

• Audience size: up to ~100k customers per campaign.
• Campaign dispatch is synchronous-per-recipient at launch time (fan-out loop). At 100k recipients this takes ~10s with batched inserts — acceptable for a demo but not production-safe.
• The channel stub fires callbacks serially with a random 1–5s delay per message.

Authentication is deliberately omitted to keep scope focused on the CRM data model and async delivery loop. In production, all endpoints would require a JWT bearer token validated against a users table with role-based access control.