# Stackhouse
> AI-Native, Schema-Later Database with Automatic Evolution
This document contains the full content of all documentation pages for AI consumption.
---
## Stackhouse — Product Overview
**URL:** https://www.stackhousedb.com/docs
**Description:** AI-native, schema-later database with automatic evolution. Explore features, architecture, and API reference.
# Stackhouse Product Overview
## What is Stackhouse?
**Stackhouse** is an open-source, AI-native database that combines the flexibility of NoSQL with the power of SQL, vector search, and serverless compute — all in one unified system. Built in Rust, Stackhouse serves as a production-ready alternative to Supabase for modern applications requiring real-time capabilities and AI integration.
---
## The Problem We Solve
Traditional database architectures force developers to cobble together multiple services:
| Traditional Stack | Stackhouse |
|-------------------|--------|
| PostgreSQL + ORM/migration tools | **Unified database, Schema-Later — no migrations needed** (still PostgreSQL under the hood) |
| Pinecone/Qdrant (vector search) | **Native Qdrant integration** — one less service to wire up separately |
| AWS Lambda/Cloud Functions (compute) | **JavaScript/Boa serverless functions** |
| Firebase/Pusher (realtime) | **Built-in WebSocket/SSE** (currently single-process; multi-instance fan-out not yet implemented) |
**Result:** Stackhouse reduces infrastructure complexity from 5+ services to 1, cutting operational overhead and latency while improving developer velocity.
---
## Core Capabilities
### 1. Schema-Later™ Data Store
Eliminate schema migrations and downtime. Stackhouse automatically evolves your schema as your data grows.
```javascript
// Day 1: Simple document
await db.push('users', { name: 'Alice', age: 25 });
// Day 30: Add new fields — no migration needed
await db.push('users', {
name: 'Bob',
email: 'bob@example.com',
preferences: { theme: 'dark', notifications: true }
});
```
**Benefits:**
- Zero downtime schema changes
- No migration scripts to write or maintain
- Handles nested JSON structures natively
- Development stays fast as your app grows
---
### 2. AI-Native Vector Search
Semantic search built into the core, powered by Qdrant's HNSW algorithm. No external vector database required.
```javascript
// Store document with embedding
await db.vectors.insert('documents', {
id: 'doc1',
vector: [0.1, 0.2, 0.3, ...], // 384+ dimensional embedding
metadata: { title: 'Introduction to Stackhouse' }
});
// Semantic search
const results = await db.vectors.search('documents', {
query: embedding,
k: 10,
filter: { category: 'tutorials' }
});
```
**Use Cases:**
- RAG (Retrieval-Augmented Generation) pipelines
- Semantic document search
- Recommendation engines
- Similarity matching
- Image search (with vision embeddings)
---
### 3. Realtime 2.0
Bidirectional live data subscriptions via WebSocket or Server-Sent Events.
```javascript
const ws = new WebSocket('ws://api.stackhouse.io/v1/realtime');
ws.send(JSON.stringify({
type: 'subscribe',
table: 'users',
event: '*'
}));
ws.onmessage = (event) => {
const msg = JSON.parse(event.data);
console.log('Live update:', msg.type, msg.record ?? msg.old_record);
};
```
**Features:**
- Sub-millisecond latency for live updates
- Multiple subscriptions per connection
- Automatic reconnection handling
- Scales to millions of concurrent connections
---
### 4. Serverless JavaScript Functions
Deploy custom business logic as JavaScript/TypeScript source and execute it via the embedded Boa engine.
```bash
# Deploy a JavaScript function
curl -X POST /v1/functions/deploy \
-H "Content-Type: application/json" \
-d '{
"name": "process",
"runtime": "javascript",
"source_code": "exports.handler = (input) => ({ doubled: input.value * 2 });"
}'
# Execute with data
curl -X POST /v1/functions/invoke/process \
-d '{"value": 21}'
```
**Benefits:**
- Sandboxed execution with resource limits
- Fast cold-start times
- JavaScript/TypeScript via the embedded Boa engine
- A multi-language WASM runtime is a future roadmap item
---
### 5. PostgreSQL-Backed Storage
Stackhouse's data layer is PostgreSQL, managed through `sqlx` (`stackhouse/src/platform/db.rs`). There is no separate custom LSM storage engine in this codebase — durability, crash-safety, and WAL behavior come from Postgres itself. "Schema-Later" is implemented as dynamic DDL generation over standard Postgres tables driven by incoming JSON shape, not an alternate storage format.
---
### 6. Enterprise Security
Defense-in-depth security architecture:
| Layer | Protection |
|-------|-----------|
| **Network** | Cloudflare DDoS + WAF, TLS 1.3 only |
| **Infrastructure** | VPC isolation, Kubernetes NetworkPolicies, mTLS |
| **Application** | SQL injection detection, XSS filtering, SSRF protection |
| **Authentication** | Argon2id hashing, JWT with refresh tokens, MFA/TOTP |
| **Authorization** | Row-Level Security (RLS) policies per table |
| **Data** | AES-256 encryption at rest, PII masking in logs |
---
## API Overview
### Data Operations
```
POST /v1/push/:collection # Insert document
GET /v1/query/:collection # Query with filters
POST /v1/update/:collection/:id # Update document
POST /v1/delete/:collection/:id # Delete document
```
### Vector Search (proxied to Qdrant)
```
POST /v1/vectors/:collection/upsert # Upsert vector(s)
POST /v1/vectors/:collection/search # Semantic search
GET /v1/vectors/:collection/info # Collection info
```
There is no list-all-collections endpoint (no `GET /v1/vectors`).
### Functions — implemented, NOT currently reachable
```
POST /v1/functions/deploy # Deploy function
POST /v1/functions/invoke/:name # Execute function
GET /v1/functions # List functions
```
This router exists in `compute/functions.rs` but is never mounted in `main.rs` — none of these endpoints respond over HTTP in the current build.
### Realtime
```
WS /v1/realtime # WebSocket connection
GET /v1/stream/:collection # SSE stream
```
---
## Scale Targets
Stackhouse is designed to scale from prototype to enterprise:
| Metric | SMB (100K Users) | Enterprise (10M Users) |
|--------|------------------|------------------------|
| Concurrent users | 10,000 | 1,000,000 |
| Peak API RPS | 1,000 | 100,000 |
| WebSocket connections | 50,000 | 5,000,000 |
| DB queries/sec | 5,000 | 500,000 |
| Monthly cost (GCP) | ~$1,300 | ~$85K–$120K |
| Regions | Single | 3–5 active-active |
---
## Technology Stack
| Component | Technology |
|-----------|------------|
| **Runtime** | Rust (Axum web framework) |
| **Storage Engine** | PostgreSQL, accessed via `sqlx` — no separate custom storage engine |
| **Vector Database** | Qdrant (external, via REST) |
| **Function Runtime** | Boa (embedded JS/TS engine) |
| **Authentication** | Argon2id + JWT |
| **Observability** | Prometheus + OpenTelemetry (`Cargo.toml` deps confirmed; exporter targets like Grafana/Jaeger are deployment-side, not bundled) |
---
## Use Cases
### 1. AI-Powered Applications
Build RAG pipelines, chatbots, and recommendation systems with native vector search.
### 2. Realtime Collaboration
Live document editing, collaborative whiteboards, multiplayer games.
### 3. Modern Web Apps
Rapid prototyping with automatic schema evolution that scales to production.
### 4. Content Management
Semantic search across documents, images, and multimedia content.
### 5. E-commerce
Personalized recommendations, visual search, real-time inventory.
---
## Quick Start
```bash
# Clone and build
git clone https://github.com/ArjavDesa912/stackhouse.git
cd stackhouse-stack/stackhouse
# Requires a reachable Postgres and a JWT secret — there is no default
STACKHOUSE_URL=postgres://postgres:password@localhost:5432/stackhouse \
STACKHOUSE_JWT_SECRET=some-long-dev-secret \
cargo run --release -- serve
# Insert your first document
curl -X POST http://localhost:3000/v1/push/users \
-H "Content-Type: application/json" \
-d '{"name": "Alice", "email": "alice@example.com"}'
# Query it
curl http://localhost:3000/v1/query/users
```
---
## Related Documentation
- [System Design](/docs/architecture-deep-dive) — Enterprise architecture for 100K–10M users
- [API Reference](/docs/api-reference/api-reference) — Complete REST API documentation
- [Security](/docs/security-and-ops/security) — Security controls and hardening status
---
## License
MIT License — see LICENSE for details.
---
*Stackhouse — Schema-Later • AI-Native • Realtime*
---
## JavaScript Functions
**URL:** https://www.stackhousedb.com/docs/advanced-features/functions
**Description:** Server-side compute with JavaScript/Boa
# JavaScript Functions
## ⚡ Serverless Compute with JavaScript
Run custom logic safely at the edge.
## What are JavaScript Functions?
Stackhouse functions are written in JavaScript and executed by the embedded Boa engine. The runtime resolves a handler in one of three forms:
- a global `handler` function
- `exports.handler`
- `module.exports`
The function receives a single `input` argument (a JSON value) and should return a JSON-serializable value.
`runtime` accepts `javascript`, `typescript`, `wasm_rust`, or `wasm_js` in the request, but every value executes as raw JavaScript via Boa today — the `wasm_rust`/`wasm_js` values are recorded for forward compatibility only and do not trigger any WASM compilation or execution.
## Quick Example
### 1. Write a JavaScript function
```javascript
// process.js
exports.handler = function(input) {
return { doubled: input.value * 2 };
};
```
### 2. Deploy to Stackhouse
```bash
curl -X POST http://localhost:3000/v1/functions/deploy \
-H "Content-Type: application/json" \
-d '{
"name": "double",
"runtime": "javascript",
"source_code": "exports.handler = (input) => ({ doubled: input.value * 2 });"
}'
```
### 3. Execute
```bash
curl -X POST http://localhost:3000/v1/functions/invoke/double \
-H "Content-Type: application/json" \
-d '{"value": 21}'
# Returns: {"success": true, "output": {"doubled": 42}}
```
## Use Cases
### 1. Data Validation
```javascript
exports.handler = (input) => {
if (!input.email || !input.email.includes("@")) {
throw new Error("Invalid email");
}
return { valid: true };
};
```
### 2. Data Transformation
```javascript
exports.handler = (input) => {
return {
...input,
total: input.price * input.quantity
};
};
```
### 3. Business Logic
```javascript
exports.handler = (input) => {
if (input.amount > 1000) {
return input.amount * 0.9;
}
return input.amount;
};
```
## API Reference
### Deploy Function
```http
POST /v1/functions/deploy
Content-Type: application/json
{
"name": "myfunc",
"runtime": "javascript",
"entrypoint": "handler",
"source_code": "exports.handler = (input) => input"
}
```
`runtime` accepts `javascript`, `typescript`, `wasm_rust`, or `wasm_js` — all four are run by the Boa JS engine today; only the value is stored differently. `entrypoint` defaults to `handler`.
### Execute Function
```http
POST /v1/functions/invoke/:name
Content-Type: application/json
{
"value": 42
}
```
### List Functions
```http
GET /v1/functions
```
Returns `{"success": true, "data": [...]}` (field is `data`, not `functions`).
### Delete Function
```http
DELETE /v1/functions/:id
```
Returns `{"success": true, "message": "Function deleted"}`.
## Security
| JS sandbox security | |
|---|---|
| ✅ Memory Isolation | Boa engine isolates execution |
| ✅ Resource Limits | CPU time, memory caps |
| ✅ No File System | Cannot read/write files |
| ✅ Timeout Enforcement | Prevent infinite loops |
| ✅ Fast Execution | Compiled JS in the same process |
## Best Practices
1. **Keep functions small** - source code is stored in the database
2. **Use timeouts** - default 30s; prevent infinite loops
3. **Limit memory** - default 128MB
4. **Handle errors** - Return clear error messages
5. **Return JSON-serializable values** - Boa serializes the result to JSON
## Resources
- [Performance Guide](/docs/production/performance)
- [Realtime](/docs/advanced-features/realtime)
---
**Ready to deploy functions?** Continue to [Realtime](/docs/advanced-features/realtime) 🚀
---
## Realtime 2.0
**URL:** https://www.stackhousedb.com/docs/advanced-features/realtime
**Description:** WebSocket and SSE realtime updates via Postgres LISTEN/NOTIFY
# Realtime 2.0
## 🔌 Bidirectional WebSocket Communication
Implemented in `stackhouse/src/realtime/mod.rs`, mounted at `/v1/realtime` (`create_realtime_router`: WS upgrade at `/v1/realtime`, status at `/v1/realtime/status`). Change detection uses **PostgreSQL LISTEN/NOTIFY** fanned out over `tokio::sync::broadcast` channels per table — not Postgres logical replication.
### WebSocket vs SSE
### Protocol
Client → server messages (`SubscriptionMessage` in `realtime/mod.rs`):
```json
{ "type": "subscribe", "table": "users", "event": "INSERT" }
{ "type": "subscribe", "table": "users", "event": "*" }
{ "type": "unsubscribe", "table": "users" }
```
`event` accepts `"INSERT"`, `"UPDATE"`, `"DELETE"`, or `"*"` (all events); a `filter` field is also accepted on `subscribe` for row-level filtering.
Server → client push events (`RealtimeEvent`):
```json
{ "type": "INSERT", "table": "users", "record": {...}, "timestamp": "..." }
{ "type": "UPDATE", "table": "users", "record": {...}, "old_record": {...}, "timestamp": "..." }
{ "type": "DELETE", "table": "users", "old_record": {...}, "timestamp": "..." }
```
### JavaScript Example
```javascript
// Connect
const ws = new WebSocket('ws://localhost:3000/v1/realtime');
ws.onopen = () => {
console.log('Connected');
// Subscribe to a table
ws.send(JSON.stringify({
type: 'subscribe',
table: 'users',
event: '*'
}));
};
ws.onmessage = (event) => {
const msg = JSON.parse(event.data);
console.log('Update:', msg.type, msg.table, msg.record ?? msg.old_record);
};
// Unsubscribe
ws.send(JSON.stringify({
type: 'unsubscribe',
table: 'users'
}));
```
### Python Example
```python
async def stackhouse_client():
uri = "ws://localhost:3000/v1/realtime"
async with websockets.connect(uri) as ws:
# Subscribe
await ws.send(json.dumps({
"type": "subscribe",
"table": "users",
"event": "*"
}))
# Listen
while True:
msg = await ws.recv()
data = json.loads(msg)
print(f"Update: {data}")
asyncio.run(stackhouse_client())
```
---
**Next:** [API Reference](/docs/api-reference/api-reference) 🚀
---
## Vector Search
**URL:** https://www.stackhousedb.com/docs/advanced-features/vector-search
**Description:** AI-powered vector similarity search
# Vector Search
## 🔍 AI-Native Similarity Search
Find similar data in milliseconds: `[0.1, 0.2, 0.3] → Top K Matches`
## 📚 Table of Contents
- [Concepts](#concepts)
- [Getting Started](#getting-started)
- [API Reference](#api-reference)
- [Distance Metrics](#distance-metrics)
- [Performance](#performance)
- [Use Cases](#use-cases)
---
## 🎯 Concepts
### What is Vector Search?
### How It Works
```mermaid
flowchart TD
A["1. Text Input
"The quick brown fox jumps""] --> B["2. Embedding Model
sentence-transformers, OpenAI embeddings,
Cohere embeddings, etc."]
B --> C["3. Vector Representation
[0.23, -0.45, 0.67, 0.12, …, 0.89]
384 dimensions (example)"]
C --> D["4. HNSW Index Build
Hierarchical Navigable Small World Graph
Approximate Nearest Neighbor · O(log n) search
Fast memory access"]
D --> E["5. Similarity Search
Query: [0.25, -0.43, 0.65, …]"]
E --> F["Compare with all vectors"] --> G["Sort by similarity"] --> H["Return Top K results"]
```
### HNSW Algorithm Visualized
```mermaid
flowchart TD
subgraph L2["Layer 2 — Sparse (long connections)"]
direction LR
a2((•)) --- b2((•)) --- c2((•))
end
subgraph L1["Layer 1 — Medium density"]
direction LR
a1((•)) --- b1((•)) --- c1((•)) --- d1((•)) --- e1((•)) --- f1((•))
end
subgraph L0["Layer 0 — Dense (all points)"]
direction LR
a0((•)) --- b0((•)) --- c0((•)) --- d0((•)) --- e0((•)) --- f0((•)) --- g0((•)) --- h0((•))
end
L2 -->|entry point descends| L1 -->|refine search| L0
```
**Search process:**
1. Start at Layer 2 (entry point)
2. Greedy search to find closest point
3. Move to Layer 1, repeat
4. Move to Layer 0, refine search
5. Return nearest neighbors
**Complexity:** O(log n) vs O(n) for brute force
---
## 🚀 Getting Started
### Step 1: Generate Embeddings
First, you need an embedding model. Here are popular options:
```python
# Option 1: sentence-transformers (Python)
from sentence_transformers import SentenceTransformer
model = SentenceTransformer('all-MiniLM-L6-v2')
text = "The quick brown fox jumps over the lazy dog"
embedding = model.encode(text)
print(embedding.shape) # (384,)
print(embedding[:5]) # [0.23, -0.45, 0.67, 0.12, -0.34]
```
```javascript
// Option 2: OpenAI API (Node.js)
const openai = require('openai');
async function getEmbedding(text) {
const response = await openai.embeddings.create({
model: "text-embedding-3-small",
input: text
});
return response.data[0].embedding;
}
```
```bash
# Option 3: Use a pre-computed embedding service
curl https://api.embeddings.com/v1/embed \
-H "Content-Type: application/json" \
-d '{"text": "Your text here"}'
```
### Step 2: Insert Vectors
```bash
# Upsert a vector into the "documents" collection
curl -X POST http://localhost:3000/v1/vectors/documents/upsert \
-H "Content-Type: application/json" \
-d '{
"id": "doc1",
"embedding": [0.23, -0.45, 0.67, 0.12, -0.34, ...],
"data": {
"title": "Introduction to Stackhouse",
"content": "Stackhouse is a schema-later database...",
"category": "database",
"url": "https://stackhouse.dev/intro"
}
}'
```
**Response (201 Created):**
```json
{
"success": true,
"data": { "id": "doc1", "collection": "documents", "dimensions": 5 },
"message": "Vector upserted successfully"
}
```
`id` is optional — omit it to get an auto-generated UUID. `column` defaults to `"embedding"` and only needs to be set if a collection stores more than one named vector per point.
### Step 3: Search for Similar Vectors
```bash
curl -X POST http://localhost:3000/v1/vectors/documents/search \
-H "Content-Type: application/json" \
-d '{
"vector": [0.25, -0.43, 0.65, 0.10, -0.30, ...],
"top_k": 10,
"metric": "cosine"
}'
```
**Response:**
```json
{
"success": true,
"count": 3,
"collection": "documents",
"metric": "cosine",
"data": [
{ "id": "doc1", "similarity": 0.88, "data": { "title": "Introduction to Stackhouse", "category": "database" } },
{ "id": "doc5", "similarity": 0.77, "data": { "title": "Getting Started with Databases", "category": "database" } },
{ "id": "doc12", "similarity": 0.66, "data": { "title": "Python Programming Guide", "category": "programming" } }
]
}
```
---
## 📖 API Reference
All routes are mounted under `/v1/vectors` (`stackhouse/src/storage/vectors.rs`, `create_vector_router`). There is no list-all-collections or delete-by-id endpoint — only the four routes below exist.
### Upsert Vector
```bash
POST /v1/vectors/:collection/upsert
```
**Request Body:**
```json
{
"id": "string", // Optional: omit for an auto-generated UUID
"embedding": [float, ...], // Required: the vector
"data": {...}, // Optional: payload stored alongside the vector
"column": "embedding" // Optional: named vector column (default: "embedding")
}
```
**Example:**
```bash
curl -X POST http://localhost:3000/v1/vectors/products/upsert \
-H "Content-Type: application/json" \
-d '{
"id": "prod_12345",
"embedding": [0.12, 0.34, -0.56, ...],
"data": {
"name": "Wireless Headphones",
"price": 99.99,
"category": "Electronics"
}
}'
```
### Batch Upsert
```bash
POST /v1/vectors/:collection/batch
```
**Request Body:**
```json
{ "records": [ { "id": "...", "embedding": [...], "data": {...} }, ... ] }
```
Returns `{"success": true, "data": {"ids": [...], "collection": "...", "count": N}}`. Errors with `400` if `records` is empty.
### Search Vectors
```bash
POST /v1/vectors/:collection/search
```
**Request Body:**
```json
{
"vector": [float, ...], // Required: query vector
"top_k": 10, // Optional (default: 10)
"metric": "cosine", // Optional: "cosine" | "l2" | "inner_product" (default: "cosine")
"filters": {...}, // Optional: metadata filter, forwarded to Qdrant
"column": "embedding" // Optional: named vector column (default: "embedding")
}
```
**Response:**
```json
{
"success": true,
"count": 1,
"collection": "products",
"metric": "cosine",
"data": [
{ "id": "prod_12345", "similarity": 0.95, "data": { "name": "Wireless Headphones", "price": 99.99 } }
]
}
```
### Collection Info
```bash
GET /v1/vectors/:collection/info
```
Returns `{"success": true, "data": [...], "collection": "..."}` with per-vector-column metadata (`table`, `column`, `dimensions`, `index_type`, `row_count`).
---
## 📏 Distance Metrics
Set via the `metric` field on a search request (`DistanceMetric` in `stackhouse/src/storage/vectors.rs`); Qdrant performs the actual computation. Three metrics are supported: `cosine` (default), `l2` (Euclidean), and `inner_product` (alias `dot`).
### Cosine Similarity (Default)
Measures the angle between two vectors. Range: `[-1, 1]`
- `1` = Identical direction
- `0` = Orthogonal (uncorrelated)
- `-1` = Opposite direction
**Formula:**
```
similarity = (A · B) / (|A| × |B|)
distance = 1 - similarity
```
**Best for:**
- ✅ Semantic similarity
- ✅ Text embeddings
- ✅ Recommendation systems
**Example:** `A = [1, 0, 0]`, `B = [1, 0, 0]` → Similarity = 1.0 (same direction), Distance = 0.0
### Euclidean Distance
Measures straight-line distance. Range: `[0, ∞)`
- `0` = Identical
- Larger = More different
**Formula:**
```
distance = √Σ(Aᵢ - Bᵢ)²
```
**Best for:**
- ✅ Geometric data
- ✅ Image embeddings
- ✅ Physical coordinates
**Example:** `A = [0, 0]`, `B = [3, 4]` → Distance = 5.0 (Pythagorean theorem)
### Choosing the Right Metric
**Comparison:** `A = [1, 2, 3]`, `B = [2, 4, 6]` (A × 2) → Cosine: 0 (same direction), Euclidean: 3.74 (different)
---
## ⚡ Performance
### Benchmarks
There is no bundled benchmark suite for this path (see [Benchmarks](/docs/developer/benchmarks) for what Stackhouse does measure — it does not currently include vector search). Search performance and index-build time are governed entirely by the external Qdrant deployment's own HNSW implementation, its configured `ef_construct`/`m` parameters, and hardware — not by anything in Stackhouse's code — so no specific latency/recall numbers are quoted here. Consult Qdrant's own published benchmarks for representative figures, and measure against your own Qdrant deployment before relying on any number for capacity planning.
### Optimization Tips
**1. Vector Dimensionality**
| Dimensions | Trade-off |
|---|---|
| 128-384 | Fast, good for text |
| 768-1024 | Better accuracy |
| 1536+ | Best quality, slower |
**2. Index Size**
- More vectors = Better accuracy, slower search
- Consider sharding for >10M vectors
**3. K Value**
- Small K (5-10): Fast
- Large K (50-100): More comprehensive
**4. Batch Insertions**
```javascript
for (const doc of documents) {
await insertVector(doc);
}
// BETTER:
await insertVectorBatch(docs);
```
---
## 💡 Use Cases
### 1. Semantic Document Search
```python
STACKHOUSE_URL = "http://localhost:8080"
# Index documents
documents = [
{
"id": "doc1",
"text": "Stackhouse is a schema-later database",
"vector": encode("Stackhouse is a schema-later database")
},
{
"id": "doc2",
"text": "Python is a programming language",
"vector": encode("Python is a programming language")
}
]
# Insert vectors
for doc in documents:
requests.post(
f"{STACKHOUSE_URL}/v1/vectors/docs/upsert",
json={
"id": doc["id"],
"embedding": doc["vector"],
"data": {"text": doc["text"]}
}
)
# Semantic search
query = "database that adapts to my data"
query_vector = encode(query)
response = requests.post(
f"{STACKHOUSE_URL}/v1/vectors/docs/search",
json={"vector": query_vector, "top_k": 5}
)
print(response.json())
# Returns: {"success": true, "data": [{"id": "doc1", "similarity": 0.85, ...}], ...}
```
### 2. Product Recommendations
```javascript
// Find similar products
async function recommendProducts(productId) {
// Get product vector
const product = await getVector('products', productId);
// Search for similar products
const response = await fetch(
'http://localhost:3000/v1/vectors/products/search',
{
method: 'POST',
headers: {'Content-Type': 'application/json'},
body: JSON.stringify({
vector: product.vector,
top_k: 10
})
}
);
const results = await response.json();
// Filter out the same product
return results.data.filter(r => r.id !== productId);
}
```
### 3. Image Similarity Search
```python
from PIL import Image
# Load pre-trained ResNet
resnet = models.resnet50(pretrained=True)
resnet.eval()
# Transform and extract features
transform = transforms.Compose([
transforms.Resize(256),
transforms.CenterCrop(224),
transforms.ToTensor(),
transforms.Normalize(mean=[0.485, 0.456, 0.406],
std=[0.229, 0.224, 0.225])
])
def extract_features(image_path):
image = Image.open(image_path)
image = transform(image).unsqueeze(0)
with torch.no_grad():
features = resnet(image)
return features.flatten().tolist()
# Index images
for img_path in glob("images/*.jpg"):
features = extract_features(img_path)
requests.post(
f"{STACKHOUSE_URL}/v1/vectors/images/upsert",
json={
"id": img_path,
"embedding": features,
"data": {"path": img_path}
}
)
# Search similar images
query_features = extract_features("query.jpg")
response = requests.post(
f"{STACKHOUSE_URL}/v1/vectors/images/search",
json={"vector": query_features, "top_k": 10}
)
```
### 4. RAG (Retrieval Augmented Generation)
```python
def rag_query(question):
# 1. Encode question
question_vector = encode(question)
# 2. Retrieve relevant documents
response = requests.post(
f"{STACKHOUSE_URL}/v1/vectors/knowledge_base/search",
json={"vector": question_vector, "top_k": 5}
)
context = "\n".join([
r["data"]["text"]
for r in response.json()["data"]
])
# 3. Generate answer with context
completion = openai.ChatCompletion.create(
model="gpt-4",
messages=[
{"role": "system", "content": "Answer using this context:\n" + context},
{"role": "user", "content": question}
]
)
return completion.choices[0].message.content
```
---
## 🎓 Best Practices
### 1. Embedding Model Selection
| Model | Dim | Speed | Quality |
|---|---|---|---|
| all-MiniLM-L6-v2 | 384 | ⚡⚡⚡ | ⭐⭐⭐ |
| all-mpnet-base-v2 | 768 | ⚡⚡ | ⭐⭐⭐⭐ |
| text-embedding-3-small | 1536 | ⚡ | ⭐⭐⭐⭐⭐ |
| text-embedding-3-large | 3072 | ⚡ | ⭐⭐⭐⭐⭐ |
**Recommendations:**
- Start with all-MiniLM-L6-v2 (fast, good enough)
- Upgrade to OpenAI for production
- Use consistent model across all data
### 2. Index Organization
```python
# ✅ GOOD: Separate indexes by use case
/v1/vectors/documents # Text search
/v1/vectors/products # Product recommendations
/v1/vectors/users # User similarity
# ❌ BAD: Everything in one index
/v1/vectors/everything # Harder to manage
```
### 3. Metadata Design
```python
# ✅ GOOD: Rich payload data for filtering
{
"id": "doc123",
"embedding": [...],
"data": {
"title": "...",
"category": "tech",
"created_at": "2025-01-03",
"author": "alice",
"tags": ["database", "rust", "performance"]
}
}
# ❌ BAD: Minimal payload data
{
"id": "doc123",
"embedding": [...],
"data": {"title": "..."}
}
```
---
## 📚 Further Reading
- [JavaScript Functions](/docs/advanced-features/functions) - Process vectors with custom logic
- [Realtime](/docs/advanced-features/realtime) - Live vector updates
- [Performance Guide](/docs/production/performance) - Optimize vector operations
---
**Ready to add AI to your app?** Continue to [JavaScript Functions](/docs/advanced-features/functions) 🚀
---
## API Reference
**URL:** https://www.stackhousedb.com/docs/api-reference/api-reference
**Description:** Complete REST API documentation
# API Reference
## 📡 Complete REST API Documentation
Every endpoint, explained.
## 🔗 Base URL
```
Development: http://localhost:3000
Production: https://your-domain.com
```
## 📋 Common Headers
```http
Content-Type: application/json
Authorization: Bearer
```
## 📊 Standard Response Format
### Success Response
```json
{
"success": true,
"data": {...},
"message": "Optional message"
}
```
### Error Response
```json
{
"success": false,
"error": "Error message",
"code": "ERROR_CODE"
}
```
---
## 📝 Data Operations
### Insert Document
```http
POST /v1/push/:collection
```
**Description:** Insert a single document into a collection. Creates the collection automatically if it doesn't exist.
**Path Parameters:**
- `collection` (string) - Collection name
**Request Body:**
```json
{
"field1": "value1",
"field2": 123,
"nested": {
"key": "value"
}
}
```
**Response:**
```json
{
"success": true,
"data": {
"id": 1,
"field1": "value1",
"field2": 123,
"nested": {"key": "value"},
"created_at": "2025-01-03T12:00:00Z"
}
}
```
**Example:**
```bash
curl -X POST http://localhost:3000/v1/push/users \
-H "Content-Type: application/json" \
-d '{
"name": "Alice",
"email": "alice@example.com",
"age": 28
}'
```
---
### Batch Insert
```http
POST /v1/push/:collection/batch
```
**Description:** Insert multiple documents in a single request.
**Request Body:**
```json
[
{"name": "Alice", "age": 28},
{"name": "Bob", "age": 35},
{"name": "Charlie", "age": 42}
]
```
**Response:**
```json
{
"success": true,
"data": {
"inserted": 3,
"ids": [1, 2, 3]
}
}
```
---
### Query Collection
```http
GET /v1/query/:collection
```
**Description:** Retrieve all documents from a collection.
**Query Parameters:**
- `limit` (number, optional) - Maximum number of results
- `offset` (number, optional) - Number of results to skip
- `order_by` (string, optional) - Field to order by
- `order_dir` (string, optional) - "ASC" or "DESC" (default: "ASC")
**Example:**
```bash
# Get all users
curl http://localhost:3000/v1/query/users
# Get first 10 users, ordered by age
curl "http://localhost:3000/v1/query/users?limit=10&order_by=age&order_dir=desc"
```
**Response:**
```json
{
"success": true,
"data": [
{"id": 1, "name": "Alice", "age": 28},
{"id": 2, "name": "Bob", "age": 35}
],
"count": 2
}
```
---
### Get Document by ID
```http
GET /v1/query/:collection/:id
```
**Description:** Retrieve a specific document by ID.
**Example:**
```bash
curl http://localhost:3000/v1/query/users/1
```
**Response:**
```json
{
"success": true,
"data": {
"id": 1,
"name": "Alice",
"age": 28
}
}
```
---
### Update Document
```http
POST /v1/update/:collection/:id
```
**Description:** Update a specific document. Partial updates supported.
**Request Body:**
```json
{
"age": 29,
"city": "San Francisco"
}
```
**Example:**
```bash
curl -X POST http://localhost:3000/v1/update/users/1 \
-H "Content-Type: application/json" \
-d '{
"age": 29,
"city": "San Francisco"
}'
```
**Response:**
```json
{
"success": true,
"data": {
"id": 1,
"name": "Alice",
"age": 29,
"city": "San Francisco"
}
}
```
---
### Delete Document
```http
POST /v1/delete/:collection/:id
```
**Example:**
```bash
curl -X POST http://localhost:3000/v1/delete/users/1
```
**Response:**
```json
{
"success": true,
"message": "Document deleted successfully"
}
```
---
## 🗃️ Schema & Metadata
### List Tables
```http
GET /v1/tables
```
**Description:** List all collections/tables in the database.
**Response:**
```json
{
"success": true,
"data": ["users", "products", "orders"]
}
```
---
### Table Stats
```http
GET /v1/tables/:collection
```
**Description:** Get statistics about a collection.
**Response:**
```json
{
"success": true,
"data": {
"name": "users",
"row_count": 1250,
"size_bytes": 524288,
"indexes": ["id", "email"],
"columns": {
"id": "INTEGER",
"name": "TEXT",
"email": "TEXT",
"age": "INTEGER",
"created_at": "TIMESTAMP"
},
"created_at": "2025-01-01T00:00:00Z",
"last_updated": "2025-01-03T12:00:00Z"
}
}
```
---
### Creating Indexes
```bash
curl -X POST http://localhost:3000/v1/sql/query \
-H "Content-Type: application/json" \
-d '{"query": "CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users (email)"}'
```
---
## 🔍 Vector Search
Vector search is backed by a Qdrant instance (`storage/vectors.rs`), not an in-process index. Collections are created in Qdrant lazily on first upsert.
### Upsert Vector
```http
POST /v1/vectors/:collection/upsert
```
**Request Body:**
```json
{
"id": "doc1",
"embedding": [0.1, 0.2, 0.3, 0.4, 0.5],
"data": {
"title": "Document Title",
"category": "tech"
},
"column": "embedding"
}
```
- `id` (string, optional) — omit to auto-generate a UUID.
- `embedding` (float array, required) — the vector.
- `data` (object, optional) — payload stored alongside the vector.
- `column` (string, optional, default `"embedding"`) — name of the vector field; used to select a named vector when a collection stores more than one per point.
**Example:**
```bash
curl -X POST http://localhost:3000/v1/vectors/documents/upsert \
-H "Content-Type: application/json" \
-d '{
"id": "doc1",
"embedding": [0.1, 0.2, 0.3, 0.4, 0.5],
"data": {"title": "Introduction"}
}'
```
**Response:** `201 Created`
```json
{
"success": true,
"data": {
"id": "doc1",
"collection": "documents",
"dimensions": 5
},
"message": "Vector upserted successfully"
}
```
---
### Batch Upsert Vectors
```http
POST /v1/vectors/:collection/batch
```
**Request Body:**
```json
{
"records": [
{ "id": "doc1", "embedding": [0.1, 0.2, 0.3], "data": {"title": "One"} },
{ "id": "doc2", "embedding": [0.4, 0.5, 0.6], "data": {"title": "Two"} }
]
}
```
**Response:** `201 Created`
```json
{
"success": true,
"data": {
"ids": ["doc1", "doc2"],
"collection": "documents",
"count": 2
},
"message": "Vectors batch upserted successfully"
}
```
---
### Search Vectors
```http
POST /v1/vectors/:collection/search
```
**Request Body:**
```json
{
"vector": [0.15, 0.25, 0.35, 0.45, 0.55],
"top_k": 10,
"metric": "cosine",
"filters": { "category": "tech" },
"column": "embedding"
}
```
- `vector` (float array, required) — the query vector.
- `top_k` (number, optional, default `10`) — number of results to return.
- `metric` (string, optional, default `"cosine"`) — one of `cosine`, `l2`, `inner_product` (aliases `dot`/`inner_product` also accepted).
- `filters` (object, optional) — payload filters passed through to Qdrant.
- `column` (string, optional, default `"embedding"`) — named vector to search.
**Example:**
```bash
curl -X POST http://localhost:3000/v1/vectors/documents/search \
-H "Content-Type: application/json" \
-d '{
"vector": [0.15, 0.25, 0.35, 0.45, 0.55],
"top_k": 5
}'
```
**Response:**
```json
{
"success": true,
"data": [
{
"id": "doc1",
"similarity": 0.92,
"data": {"title": "Introduction"}
}
],
"count": 1,
"collection": "documents",
"metric": "cosine"
}
```
Note the result field is `similarity` (a score, not a distance), and each hit's payload is under `data`, not `metadata`.
---
### Vector Collection Info
```http
GET /v1/vectors/:collection/info
```
**Response:**
```json
{
"success": true,
"data": {
"table": "documents",
"column": "embedding",
"dimensions": 5,
"index_type": "hnsw",
"row_count": 1042
},
"collection": "documents"
}
```
There is no endpoint to list all vector collections/indexes (no `GET /v1/vectors`); query `/v1/vectors/:collection/info` for a known collection instead.
---
## ⚡ JavaScript Functions
### Deploy Function
```http
POST /v1/functions/deploy
```
**Content-Type:** `application/json`
**Description:** Deploy a function. The `runtime` field accepts `javascript` (default), `typescript`, `wasm_rust`, or `wasm_js`, and the `entrypoint` defaults to `handler` — but this is currently metadata only: every runtime value executes `source_code` the same way, as raw JavaScript through the embedded Boa engine (`compute/functions.rs::execute_function`). There is no TypeScript transpilation and no actual WASM execution yet, regardless of which `runtime` you set.
**Example:**
```bash
curl -X POST http://localhost:3000/v1/functions/deploy \
-H "Content-Type: application/json" \
-d '{
"name": "double",
"runtime": "javascript",
"entrypoint": "handler",
"source_code": "exports.handler = (input) => ({ result: input.value * 2 });"
}'
```
**Response:**
```json
{
"success": true,
"data": {
"id": "...",
"name": "double",
"runtime": "javascript",
"created_at": "2025-01-03T12:00:00Z",
"memory_limit_mb": 128,
"timeout_secs": 30
}
}
```
---
### Execute Function
```http
POST /v1/functions/invoke/:name
```
**Content-Type:** `application/json`
**Request Body:** `` (the raw input object) or the legacy wrapped form `{ "input": }`.
**Example:**
```bash
curl -X POST http://localhost:3000/v1/functions/invoke/double \
-H "Content-Type: application/json" \
-d '{"value": 21}'
```
**Response:**
```json
{
"success": true,
"data": {
"id": "...",
"function_id": "...",
"status": "success",
"input": {"value": 21},
"output": {"result": 42},
"error": null,
"duration_ms": 2
}
}
```
---
### List Functions
```http
GET /v1/functions
```
**Response:**
```json
{
"success": true,
"data": [
{
"id": "...",
"name": "double",
"runtime": "javascript",
"status": "active",
"version": 1,
"triggers": [],
"created_at": "2025-01-03T12:00:00Z",
"updated_at": "2025-01-03T12:00:00Z"
}
]
}
```
---
## 🔌 Realtime
### WebSocket Connection
```http
WS /v1/realtime
```
**Description:** Table-level realtime subscriptions over a single WebSocket, backed by Postgres `LISTEN`/`NOTIFY` fanned out through in-process `tokio::broadcast` channels (`realtime/mod.rs`) — not logical replication.
On connect the server immediately sends a `connected` message with a `client_id`.
**Client → server messages:**
```json
{ "type": "subscribe", "table": "users", "event": "*", "filter": "..." }
{ "type": "unsubscribe", "table": "users" }
{ "type": "ping" }
```
`event` and `filter` are accepted but currently unused by the server — every subscription receives all INSERT/UPDATE/DELETE events for the table regardless of what you pass here.
**Server → client messages:**
```json
{ "type": "connected", "message": "Connected to Stackhouse Realtime", "client_id": 1 }
{ "type": "subscribed", "table": "users", "event": "*" }
{ "type": "unsubscribed", "table": "users" }
{ "type": "pong" }
{ "type": "error", "message": "..." }
{ "type": "INSERT", "table": "users", "record": {...}, "timestamp": "2025-01-03T12:00:00Z" }
{ "type": "UPDATE", "table": "users", "record": {...}, "old_record": {...}, "timestamp": "..." }
{ "type": "DELETE", "table": "users", "old_record": {...}, "timestamp": "..." }
```
**JavaScript Example:**
```javascript
const ws = new WebSocket('ws://localhost:3000/v1/realtime');
ws.onopen = () => {
ws.send(JSON.stringify({ type: 'subscribe', table: 'users', event: '*' }));
};
ws.onmessage = (event) => {
const msg = JSON.parse(event.data);
if (msg.type === 'INSERT' || msg.type === 'UPDATE' || msg.type === 'DELETE') {
console.log('Change:', msg.table, msg.record ?? msg.old_record);
}
};
```
---
### Presence & Broadcast (REST, mounted under `/v1/realtime`)
In addition to the WebSocket above, the realtime router also mounts plain REST endpoints for presence tracking and channel broadcast (`realtime/presence.rs`, `realtime/broadcast.rs`):
| Method | Path | Description |
| --- | --- | --- |
| POST | `/v1/realtime/presence/track` | Mark a user as present on a channel |
| POST | `/v1/realtime/presence/untrack` | Remove a user's presence from a channel |
| GET | `/v1/realtime/presence/:channel` | List users currently present on a channel |
| GET | `/v1/realtime/presence` | List all active presence channels |
| POST | `/v1/realtime/broadcast/send` | Publish a message to a broadcast channel |
| GET | `/v1/realtime/broadcast/channels` | List active broadcast channels |
| GET | `/v1/realtime/broadcast/:channel/history` | Last 50 messages sent to a channel |
All responses follow `{"success": true, "data": ...}`.
---
### SSE Stream (Legacy)
```http
GET /v1/stream/:collection
```
**Description:** Server-Sent Events stream for collection updates. This is a separate, older push mechanism from the `/v1/realtime` WebSocket above — it broadcasts an event for every push/update/delete against a collection made through the `/v1/push`, `/v1/update`, `/v1/delete` handlers.
**Example:**
```bash
curl http://localhost:3000/v1/stream/users
```
**Response Stream:**
```
data: {"event":"connected","collection":"users"}
data: {"event":"insert","id":1,"data":{"name":"Alice"}}
data: {"event":"batch_insert","count":25}
```
The `event` field varies by operation (`insert`, `batch_insert`, `update`, `delete`, etc.) and the payload shape varies accordingly — it is not a fixed `key`/`value`/`seq` envelope.
---
## 🔐 Authentication
### Sign Up
```http
POST /v1/auth/signup
```
**Request Body:**
```json
{
"email": "user@example.com",
"password": "secure_password"
}
```
**Response:** `201 Created`
```json
{
"success": true,
"data": {
"access_token": "jwt_token_here",
"refresh_token": "refresh_token_here",
"expires_in": 900,
"token_type": "Bearer",
"user": {
"id": 1,
"email": "user@example.com",
"created_at": "2025-01-03T12:00:00Z",
"updated_at": "2025-01-03T12:00:00Z",
"metadata": {}
}
}
}
```
Note the token field is `access_token`, not `token`.
---
### Login
```http
POST /v1/auth/login
```
**Request Body:**
```json
{
"email": "user@example.com",
"password": "secure_password"
}
```
**Response:** same shape as Sign Up above (`access_token`, `refresh_token`, `expires_in`, `token_type`, `user`).
---
### Refresh Token
```http
POST /v1/auth/refresh
```
**Request Body:**
```json
{
"refresh_token": "your_refresh_token"
}
```
**Response:** same shape as Sign Up above — a full new token pair, not just a bare `token` field.
---
### Other auth endpoints
`signup`/`login`/`refresh`/`logout` are rate-limited. The auth router (`src/auth/mod.rs`) also exposes, none of which are detailed above:
| Method | Path | Description |
| --- | --- | --- |
| POST | `/v1/auth/logout` | Revoke a refresh token and blacklist the current access token's `jti` |
| GET | `/v1/auth/me` | Get the current authenticated user |
| PUT | `/v1/auth/user` | Update the current user |
| POST | `/v1/auth/change-password` | Change the current user's password |
| GET | `/v1/auth/sessions` | List active sessions |
| DELETE | `/v1/auth/sessions/:id` | Revoke a specific session |
Separate routers are also nested under `/v1/auth` for OAuth (`create_oauth_router`), magic links (`create_magic_link_router`), MFA (`create_mfa_router`), phone OTP (`create_phone_otp_router`), and CAPTCHA (`create_captcha_router`) — see [Authentication](/docs/security-and-ops/authentication) for those in depth.
---
## 📊 System
### Health Check
```http
GET /health
```
**Response:**
```json
{
"status": "healthy",
"database": "connected"
}
```
On failure: `{"status": "unhealthy", "database": "disconnected", "error": "..."}`. There is no `version` field.
---
### Root Endpoint
```http
GET /
```
**Response:** (this lists only the core CRUD/table/stream/health/explorer routes registered directly in `api/routes.rs` — it does not include vectors, functions, realtime, auth, or the enterprise catalog/connector/agent/workflow routes, which are mounted separately)
```json
{
"name": "Stackhouse",
"version": "1.0.0",
"description": "🛸 Schema-Later Database with Automatic Evolution",
"endpoints": {
"push": "POST /v1/push/:collection",
"batch_push": "POST /v1/push/:collection/batch",
"query": "GET /v1/query/:collection",
"get_by_id": "GET /v1/query/:collection/:id",
"update": "POST /v1/update/:collection/:id",
"bulk_update": "POST /v1/update/:collection",
"delete": "POST /v1/delete/:collection/:id",
"bulk_delete": "POST /v1/delete/:collection",
"tables": "GET /v1/tables",
"table_stats": "GET /v1/tables/:collection",
"drop_table": "DELETE /v1/tables/:collection",
"stream": "GET /v1/stream/:collection",
"health": "GET /health",
"explorer": "GET /explore"
}
}
```
---
## 🚨 Error Codes
| Code | Description |
|------|-------------|
| `INVALID_JSON` | Malformed JSON in request body |
| `COLLECTION_NOT_FOUND` | Collection doesn't exist |
| `DOCUMENT_NOT_FOUND` | Document ID doesn't exist |
| `VALIDATION_ERROR` | Input validation failed |
| `AUTHENTICATION_FAILED` | Invalid credentials |
| `AUTHORIZATION_FAILED` | Insufficient permissions |
| `VECTOR_INDEX_ERROR` | Vector operation failed |
| `FUNCTION_EXECUTION_ERROR` | Function execution failed |
| `RATE_LIMIT_EXCEEDED` | Too many requests |
---
## 📝 Status Codes
| Code | Meaning |
|------|---------|
| 200 | Success |
| 201 | Created |
| 400 | Bad Request |
| 401 | Unauthorized |
| 403 | Forbidden |
| 404 | Not Found |
| 429 | Rate Limit Exceeded |
| 500 | Internal Server Error |
---
## 🔄 Pagination
For large result sets, use pagination:
```bash
# First page
curl "http://localhost:3000/v1/query/users?limit=100&offset=0"
# Second page
curl "http://localhost:3000/v1/query/users?limit=100&offset=100"
```
**Response includes pagination info:**
```json
{
"success": true,
"data": [...],
"pagination": {
"total": 1250,
"limit": 100,
"offset": 0,
"has_more": true
}
}
```
---
## 🧪 Testing the API
### Using curl
```bash
# Health check
curl http://localhost:3000/health
# Insert data
curl -X POST http://localhost:3000/v1/push/test \
-H "Content-Type: application/json" \
-d '{"message": "Hello, Stackhouse!"}'
# Query data
curl http://localhost:3000/v1/query/test
```
### Using Postman
1. Import API endpoints
2. Set base URL to `http://localhost:3000`
3. Add `Content-Type: application/json` header
4. Send requests!
### Using JavaScript
```javascript
const BASE_URL = 'http://localhost:3000';
async function insert(collection, data) {
const response = await fetch(`${BASE_URL}/v1/push/${collection}`, {
method: 'POST',
headers: {'Content-Type': 'application/json'},
body: JSON.stringify(data)
});
return response.json();
}
async function query(collection) {
const response = await fetch(`${BASE_URL}/v1/query/${collection}`);
return response.json();
}
// Usage
await insert('users', {name: 'Alice', age: 28});
const users = await query('users');
console.log(users);
```
### Using Python
```python
BASE_URL = 'http://localhost:3000'
def insert(collection, data):
response = requests.post(
f'{BASE_URL}/v1/push/{collection}',
json=data
)
return response.json()
def query(collection):
response = requests.get(f'{BASE_URL}/v1/query/{collection}')
return response.json()
# Usage
insert('users', {'name': 'Alice', 'age': 28})
users = query('users')
print(users)
```
---
## 📚 Related Documentation
- [WebSocket API](/docs/api-reference/websocket-api) - Realtime protocol details
- [Quick Start](/docs/getting-started/quick-start) - Get started quickly
- [Examples](../examples/) - Code examples
---
## Enterprise Catalog APIs
For a human-readable seeded inventory, see [52-Enterprise-Catalog.md](/docs/api-reference/enterprise-catalog).
### GET /v1/catalog/connectors
Returns the enterprise connector catalog, including runnable native providers,
universal-rest-backed discovery entries, and planned systems.
Query parameters:
- `status`
- `category`
- `industry` (`cross_enterprise`, `education`, `energy`, `financial_services`, `healthcare`, `hospitality_travel`, `insurance`, `legal`, `life_sciences`, `logistics`, `manufacturing`, `public_sector`, `real_estate`, `retail_ecommerce`, `telecom_media`)
- `runnable`
- `native_only`
### GET /v1/catalog/agents
Returns the enterprise agent catalog, including discovery-only vertical and
regulated-enterprise agents.
Query parameters:
- `status`
- `department`
- `industry` (`cross_enterprise`, `education`, `energy`, `financial_services`, `healthcare`, `hospitality_travel`, `insurance`, `legal`, `life_sciences`, `logistics`, `manufacturing`, `public_sector`, `real_estate`, `retail_ecommerce`, `telecom_media`)
- `runnable`
### Runnable Runtime Surfaces
These endpoints remain runnable-only:
- `GET /v1/connectors/providers`
- `GET /v1/agent/roster`
---
**Need help?** Check the [examples](../examples/) or open an issue on GitHub! 🚀
---
## Enterprise Connector And Agent Inventory
**URL:** https://www.stackhousedb.com/docs/api-reference/enterprise-catalog
**Description:** Enterprise connector and agent inventory
# Enterprise Connector And Agent Inventory
This inventory is sourced from `src/enterprise_catalog/connectors.rs` and `src/enterprise_catalog/agents.rs`.
`GET /v1/connectors/providers` and `GET /v1/agent/roster` only return native and runnable entries. `GET /v1/catalog/connectors` and `GET /v1/catalog/agents` return the full enterprise universe shown below.
## Summary
- Connectors: 123 total
- Connectors: 88 native and runnable
- Connectors: 11 universal REST-backed discovery
- Connectors: 24 planned
- Agents: 58 total
- Agents: 45 native and runnable
- Agents: 3 universal REST-backed discovery
- Agents: 10 planned
The `Category`, `Type`, and `Industries` columns intentionally use the catalog's enum-style labels so they stay close to the source of truth.
## Connectors
### Native and runnable connectors (88)
| Provider | Name | Category | Type | Industries |
| --- | --- | --- | --- | --- |
| adyen | Adyen | Productivity | Payments | RetailEcommerce |
| apollo | Apollo | Productivity | Productivity | CrossEnterprise |
| asana | Asana | Productivity | Productivity | CrossEnterprise |
| bamboohr | BambooHR | Productivity | Productivity | CrossEnterprise |
| bigquery | BigQuery | DataPlatform | Database | CrossEnterprise |
| bill_com | BILL | Productivity | Payments | CrossEnterprise, FinancialServices |
| blackline | BlackLine | Productivity | Payments | CrossEnterprise, FinancialServices |
| braze | Braze | Collaboration | Communication | CrossEnterprise, RetailEcommerce |
| calendly | Calendly | Productivity | Productivity | CrossEnterprise |
| circleci | CircleCI | Engineering | Productivity | CrossEnterprise |
| coupa | Coupa | Productivity | Payments | CrossEnterprise, FinancialServices |
| crowdstrike | CrowdStrike | Security | Analytics | CrossEnterprise |
| databricks | Databricks | DataPlatform | Database | CrossEnterprise |
| datadog | Datadog | Engineering | Analytics | CrossEnterprise |
| dayforce | Dayforce | Productivity | Productivity | CrossEnterprise |
| dbt_cloud | dbt Cloud | DataPlatform | Productivity | CrossEnterprise |
| documents | Word/PDF | Productivity | Documents | CrossEnterprise |
| docusign | DocuSign | Productivity | Documents | CrossEnterprise |
| envoy | Envoy | Productivity | Productivity | CrossEnterprise |
| evernote | Evernote | Productivity | Documents | CrossEnterprise |
| excel | Excel/CSV | Productivity | Documents | CrossEnterprise |
| five9 | Five9 | Collaboration | Communication | CrossEnterprise |
| fivetran | Fivetran | DataPlatform | Productivity | CrossEnterprise |
| flexera | Flexera | Security | Productivity | CrossEnterprise |
| flexport | Flexport | Productivity | Productivity | CrossEnterprise |
| freshservice | Freshservice | Itsm | Productivity | CrossEnterprise |
| gdrive | Google Drive | Productivity | Storage | CrossEnterprise |
| github | GitHub | Engineering | Productivity | CrossEnterprise |
| gitlab | GitLab | Engineering | Productivity | CrossEnterprise |
| gmail | Gmail | Collaboration | Communication | CrossEnterprise |
| gong | Gong | Collaboration | Communication | CrossEnterprise |
| google_calendar | Google Calendar | Productivity | Productivity | CrossEnterprise |
| greenhouse | Greenhouse | Productivity | Productivity | CrossEnterprise |
| hex | Hex | DataPlatform | Analytics | CrossEnterprise |
| hubspot | HubSpot | Productivity | Productivity | CrossEnterprise |
| intercom | Intercom | Productivity | Communication | CrossEnterprise |
| jamf | Jamf | Security | Productivity | CrossEnterprise |
| jenkins | Jenkins | Engineering | Productivity | CrossEnterprise |
| jira | Jira | Productivity | Productivity | CrossEnterprise |
| klaviyo | Klaviyo | Collaboration | Communication | RetailEcommerce |
| looker | Looker | DataPlatform | Analytics | CrossEnterprise |
| loom | Loom | Collaboration | Communication | CrossEnterprise |
| marketo | Marketo | Productivity | Productivity | CrossEnterprise |
| microsoft_defender | Microsoft Defender | Security | Analytics | CrossEnterprise |
| miro | Miro | Productivity | Productivity | CrossEnterprise |
| mode | Mode | DataPlatform | Analytics | CrossEnterprise |
| mongodb | MongoDB | DataPlatform | Database | CrossEnterprise |
| mysql | MySQL | DataPlatform | Database | CrossEnterprise |
| netsuite | NetSuite | Productivity | Productivity | CrossEnterprise |
| notion | Notion | Productivity | Productivity | CrossEnterprise |
| okta | Okta | Identity | Productivity | CrossEnterprise |
| opsgenie | Opsgenie | Engineering | Productivity | CrossEnterprise |
| oracle_erp_cloud | Oracle ERP Cloud | Productivity | Productivity | CrossEnterprise |
| outlook_calendar | Outlook Calendar | Productivity | Productivity | CrossEnterprise |
| outlook_mail | Outlook Mail | Collaboration | Communication | CrossEnterprise |
| pagerduty | PagerDuty | Engineering | Productivity | CrossEnterprise |
| postgres | PostgreSQL | DataPlatform | Database | CrossEnterprise |
| powerbi | Power BI | DataPlatform | Analytics | CrossEnterprise |
| prisma_cloud | Prisma Cloud | Security | Analytics | CrossEnterprise |
| quickbooks | QuickBooks | Productivity | Payments | CrossEnterprise |
| ramp | Ramp | Productivity | Payments | CrossEnterprise |
| redshift | Amazon Redshift | DataPlatform | Database | CrossEnterprise |
| salesforce | Salesforce | Productivity | Productivity | CrossEnterprise |
| sap | SAP | Productivity | Productivity | CrossEnterprise, Manufacturing |
| segment | Segment | DataPlatform | Productivity | CrossEnterprise, RetailEcommerce |
| sentinelone | SentinelOne | Security | Analytics | CrossEnterprise |
| servicenow | ServiceNow | Itsm | Productivity | CrossEnterprise, PublicSector |
| sharepoint | SharePoint/OneDrive | Productivity | Storage | CrossEnterprise |
| shopify | Shopify | Productivity | Productivity | CrossEnterprise |
| sigma | Sigma | DataPlatform | Analytics | CrossEnterprise |
| slack | Slack | Collaboration | Communication | CrossEnterprise |
| slack_memory | Slack Memory | Collaboration | Communication | CrossEnterprise |
| snowflake | Snowflake | DataPlatform | Database | CrossEnterprise |
| splunk | Splunk | Security | Analytics | CrossEnterprise |
| sprinklr | Sprinklr | Productivity | Productivity | CrossEnterprise |
| stripe | Stripe | Productivity | Payments | CrossEnterprise |
| successfactors | SAP SuccessFactors | Productivity | Productivity | CrossEnterprise |
| tableau | Tableau | DataPlatform | Analytics | CrossEnterprise |
| teams | Microsoft Teams | Collaboration | Communication | CrossEnterprise |
| todoist | Todoist | Productivity | Productivity | CrossEnterprise |
| twilio | Twilio | Collaboration | Communication | CrossEnterprise |
| ukg | UKG | Productivity | Productivity | CrossEnterprise |
| universal_rest | Universal REST | Engineering | Productivity | CrossEnterprise |
| wiz | Wiz | Security | Analytics | CrossEnterprise |
| workday | Workday | Productivity | Productivity | CrossEnterprise |
| xero | Xero | Productivity | Payments | CrossEnterprise |
| zendesk | Zendesk | Productivity | Productivity | CrossEnterprise |
| zoom | Zoom | Collaboration | Communication | CrossEnterprise |
### Universal REST-backed discovery connectors (11)
| Provider | Name | Category | Type | Industries |
| --- | --- | --- | --- | --- |
| amadeus | Amadeus | Productivity | Productivity | HospitalityTravel |
| appfolio | AppFolio | Productivity | Productivity | RealEstate |
| applied_epic | Applied Epic | Productivity | Productivity | Insurance |
| benchling | Benchling | Productivity | Documents | LifeSciences |
| canvas | Canvas | Productivity | Productivity | Education |
| ironclad | Ironclad | Productivity | Documents | Legal |
| linear | Linear | Productivity | Productivity | CrossEnterprise |
| project44 | project44 | Productivity | Productivity | Logistics |
| sabre | Sabre | Productivity | Productivity | HospitalityTravel |
| veeva | Veeva | Productivity | Documents | LifeSciences, Healthcare |
| wideorbit | WideOrbit | Productivity | Productivity | TelecomMedia |
### Planned connectors (24)
| Provider | Name | Category | Type | Industries |
| --- | --- | --- | --- | --- |
| adobe_commerce | Adobe Commerce | Productivity | Productivity | RetailEcommerce |
| amdocs | Amdocs | Productivity | Productivity | TelecomMedia |
| athenahealth | athenahealth | Productivity | Productivity | Healthcare |
| blackbaud | Blackbaud | Productivity | Productivity | CrossEnterprise |
| blackboard | Blackboard | Productivity | Productivity | Education |
| commercetools | commercetools | Productivity | Productivity | RetailEcommerce |
| duck_creek | Duck Creek | Productivity | Productivity | Insurance |
| e2open | e2open | Productivity | Productivity | Logistics |
| epic | Epic | Productivity | Productivity | Healthcare |
| fourkites | FourKites | Productivity | Productivity | Logistics |
| ge_vernova_apm | GE Vernova APM | Productivity | Productivity | Energy, Manufacturing |
| guidewire | Guidewire | Productivity | Productivity | Insurance |
| medidata | Medidata | Productivity | Productivity | LifeSciences, Healthcare |
| mri_software | MRI Software | Productivity | Productivity | RealEstate |
| netcracker | Netcracker | Productivity | Productivity | TelecomMedia |
| netdocuments | NetDocuments | Productivity | Documents | Legal |
| onit | Onit | Productivity | Productivity | Legal |
| opera_pms | Oracle OPERA PMS | Productivity | Productivity | HospitalityTravel |
| osisoft_pi | OSIsoft PI | DataPlatform | Database | Energy, Manufacturing |
| powerschool | PowerSchool | Productivity | Productivity | Education |
| schneider_ecostruxure | Schneider EcoStruxure | DataPlatform | Analytics | Energy, Manufacturing |
| tyler_tech | Tyler Technologies | Productivity | Productivity | PublicSector |
| workiva | Workiva | Productivity | Documents | CrossEnterprise, FinancialServices |
| yardi | Yardi | Productivity | Productivity | RealEstate |
## Agents
### Native and runnable agents (45)
| ID | Name | Role | Department | Industries |
| --- | --- | --- | --- | --- |
| agent_agile_01 | Gantt (Agile Scrum Master) | Scrum Master | Product | CrossEnterprise |
| agent_aide_01 | Aide (Executive Assistant) | Virtual Personal Assistant | Executive Cabinet | CrossEnterprise |
| agent_booking_01 | Link (Outbound Coordinator) | Meeting Facilitator | Sales/Admin | CrossEnterprise |
| agent_brand_01 | Ogilvy (Global Brand Manager) | Public Relations | Marketing | CrossEnterprise |
| agent_cloud_01 | Torvalds (Cloud Reliability Engineer) | SRE & Cloud Ops | Engineering | CrossEnterprise |
| agent_communications_01 | Marconi (Communications Dispatcher) | Crisis Communications | Ops | CrossEnterprise |
| agent_crm_01 | Belfort (Sales Executive) | Sales Strategy & Operations | Sales | CrossEnterprise |
| agent_cx_01 | Zendaya (Customer Success L2) | Customer Success L2 | Customer Success | CrossEnterprise |
| agent_data_01 | Lovelace (Senior Data Analyst) | Data Analyst | Data & BI | CrossEnterprise |
| agent_data_eng_01 | Euler (Data Pipeline Engineer) | Data Engineering | Data & BI | CrossEnterprise |
| agent_design_01 | DaVinci (Design Ops) | Design Operations | Product Design | CrossEnterprise |
| agent_desktop_01 | Carmack (IT Desktop Support L2) | IT Desktop Ops | Support | CrossEnterprise |
| agent_devops_01 | Turing (Senior DevOps Engineer) | DevOps & SRE | Engineering | CrossEnterprise |
| agent_ea_01 | Burbidge (Executive Assistant) | Corporate Coordinator | Administration | CrossEnterprise |
| agent_ecom_01 | Bezos (E-Commerce Store Manager) | E-Com Operations | Revenue | CrossEnterprise |
| agent_erp_01 | Buffett (ERP & Supply Chain Specialist) | ERP Administrator | Finance & Operations | CrossEnterprise |
| agent_facilities_01 | Houdini (Facilities Manager) | Office Operations | Facilities | CrossEnterprise |
| agent_finance_01 | Hamilton (VP of Finance Ops) | Financial Administrator | Finance | CrossEnterprise |
| agent_finance_ops_02 | Keynes (Strategic Finance Systems Lead) | Finance Systems Operations | Finance | CrossEnterprise, FinancialServices |
| agent_growth_01 | Draper (VP of Growth) | Marketing & Growth | Marketing | CrossEnterprise |
| agent_hr_01 | Leslie (HR Coordinator) | Human Resources Ops | HR | CrossEnterprise |
| agent_hr_02 | Poe (Human Capital Leader) | Global HR Director | HR | CrossEnterprise |
| agent_identity_01 | Gates (Identity Admin) | IAM & Access Control | IT Security | CrossEnterprise |
| agent_itsm_01 | Pager (Service Desk Lead) | Service Desk | Operations | CrossEnterprise, PublicSector |
| agent_legal_01 | Saul (Legal Ops Coordinator) | Legal Operations | Legal | CrossEnterprise |
| agent_logistics_01 | Magellan (Logistics Master) | Supply Chain Operations | Logistics | CrossEnterprise |
| agent_mail_01 | Post (Mail Intelligence Officer) | Corporate Communications | Admin | CrossEnterprise |
| agent_nexus_01 | Nexus (Cross-Platform Hub) | Systems Integrator | IT Infrastructure | CrossEnterprise |
| agent_notes_01 | Mnemosyne (Knowledge Archivist) | Knowledge Manager | Strategy | CrossEnterprise |
| agent_people_ops_03 | Mayo (Global People Systems Lead) | People Systems Operations | HR | CrossEnterprise |
| agent_pto_01 | Tanner (PTO Coordinator) | Time-Off Coordinator | HR | CrossEnterprise |
| agent_rec_01 | Echo (Meeting Librarian) | Meeting Analyst | Product/Sales | CrossEnterprise |
| agent_recruiting_01 | Eleanor (Recruiting Coordinator) | Talent Acquisition | HR | CrossEnterprise |
| agent_release_01 | Hopper (Release Manager) | Release & CI/CD Ops | Engineering | CrossEnterprise |
| agent_scheduler_01 | Chronos (Master Scheduler) | Calendar Coordinator | Operations | CrossEnterprise |
| agent_sdr_01 | Jordan (Elite B2B SDR) | Business Development | Sales | CrossEnterprise |
| agent_secops_02 | Shannon (Regulated SecOps Lead) | Security Operations | IT Security | CrossEnterprise, FinancialServices |
| agent_security_01 | Wozniak (SecOps Automation) | Cybersecurity Analyst | IT Security | CrossEnterprise |
| agent_slack_01 | Synapse (Chat Historian) | Internal Memory Hub | IT/Admin | CrossEnterprise |
| agent_spend_01 | Rockefeller (Spend Administrator) | Finance Ops | Finance | CrossEnterprise |
| agent_support_01 | Florence (L1 Support & IT Ops) | Customer Support L1 | Support | CrossEnterprise |
| agent_tasks_01 | Checklist (Taskmaster) | Execution Specialist | Operations | CrossEnterprise |
| agent_telephony_01 | Bell (Call Center Supervisor) | Telephony Admin | Support Ops | CrossEnterprise |
| agent_universal_01 | Ada (Integration Specialist) | API Integrator | IT Services | CrossEnterprise |
| agent_video_01 | Director (Visual Communicator) | Internal Comms | Engineering/Design | CrossEnterprise |
### Universal REST-backed discovery agents (3)
| ID | Name | Role | Department | Industries |
| --- | --- | --- | --- | --- |
| agent_hospitality_ops_01 | Ritz (Hospitality Operations Director) | Hospitality and Travel Operations | Guest Operations | HospitalityTravel |
| agent_legal_ops_02 | Brandeis (Legal Systems Lead) | Legal Systems Operations | Legal | Legal |
| agent_life_sciences_ops_01 | Sanger (Life Sciences Systems Lead) | Life Sciences Operations | R&D Operations | LifeSciences |
### Planned agents (10)
| ID | Name | Role | Department | Industries |
| --- | --- | --- | --- | --- |
| agent_education_ops_01 | Dewey (Education Operations Lead) | Education Operations | Academic Services | Education |
| agent_energy_ops_01 | Drake (Energy Operations Lead) | Energy Systems Operations | Field Operations | Energy |
| agent_healthcare_ops_01 | Salk (Healthcare Operations Lead) | Healthcare Operations | Clinical Operations | Healthcare |
| agent_insurance_ops_01 | Huebner (Insurance Operations Director) | Insurance Systems Operations | Insurance | Insurance |
| agent_logistics_ops_02 | McLean (Logistics Network Director) | Logistics Network Operations | Logistics | Logistics |
| agent_manufacturing_ops_01 | Deming (Manufacturing Operations Director) | Manufacturing Operations | Supply Chain | Manufacturing |
| agent_public_sector_ops_01 | Marshall (Public Sector Program Manager) | Public Sector Operations | Government Services | PublicSector |
| agent_real_estate_ops_01 | Pei (Real Estate Portfolio Director) | Real Estate Operations | Property Operations | RealEstate |
| agent_retail_ops_01 | Walton (Retail Operations Director) | Retail Operations | Commerce | RetailEcommerce |
| agent_telecom_ops_01 | Hopper (Telecom Service Operations Director) | Telecom and Media Operations | Network Operations | TelecomMedia |
---
## WebSocket API Protocol
**URL:** https://www.stackhousedb.com/docs/api-reference/websocket-api
**Description:** Complete WebSocket message specification
# WebSocket API Protocol
## 🔌 Complete WebSocket Specification
### Connection
```
WS /v1/realtime
```
Implemented in `src/realtime/mod.rs`. On connect, table-level changes are delivered via Postgres `LISTEN`/`NOTIFY` fanned out to subscribers through in-process `tokio::broadcast` channels — not logical replication/WAL streaming.
### Message Format
All messages are JSON. Client and server messages have different shapes (there is no shared envelope with a `key`/`value`/`seq` structure).
### Client → Server Message Types
#### 1. Subscribe
```json
{
"type": "subscribe",
"table": "users",
"event": "*",
"filter": "..."
}
```
`table` is required. `event` (e.g. `"INSERT"`, `"UPDATE"`, `"DELETE"`, `"*"`) and `filter` are accepted in the message but are **not currently enforced** by the server — a subscription receives every INSERT/UPDATE/DELETE event for the table regardless of what you pass for `event`/`filter`. Filter client-side until this is implemented server-side.
#### 2. Unsubscribe
```json
{
"type": "unsubscribe",
"table": "users"
}
```
#### 3. Ping
```json
{"type": "ping"}
```
### Server → Client Message Types
#### 1. Connected (sent immediately on connect)
```json
{
"type": "connected",
"message": "Connected to Stackhouse Realtime",
"client_id": 1
}
```
#### 2. Subscribed / Unsubscribed (ack)
```json
{"type": "subscribed", "table": "users", "event": "*"}
{"type": "unsubscribed", "table": "users"}
```
#### 3. Data events
```json
{
"type": "INSERT",
"table": "users",
"record": {"id": 1, "name": "Alice"},
"timestamp": "2025-01-03T12:00:00Z"
}
```
```json
{
"type": "UPDATE",
"table": "users",
"record": {"id": 1, "name": "Alice B."},
"old_record": {"id": 1, "name": "Alice"},
"timestamp": "2025-01-03T12:00:00Z"
}
```
```json
{
"type": "DELETE",
"table": "users",
"old_record": {"id": 1, "name": "Alice B."},
"timestamp": "2025-01-03T12:00:00Z"
}
```
`record` is present for INSERT/UPDATE, `old_record` for UPDATE/DELETE.
#### 4. Error
```json
{
"type": "error",
"message": "Invalid message format: ..."
}
```
Sent for malformed JSON or an unrecognized `type`.
#### 5. Pong
```json
{"type": "pong"}
```
### Best Practices
1. **Handle reconnection**
```javascript
ws.addEventListener('close', () => {
setTimeout(() => {
ws = new WebSocket(url);
}, 1000);
});
```
2. **Resubscribe on reconnect**
```javascript
const tables = ['users', 'documents'];
ws.onopen = () => {
tables.forEach(table => {
ws.send(JSON.stringify({ type: 'subscribe', table, event: '*' }));
});
};
```
3. **Error handling**
```javascript
ws.addEventListener('error', (error) => {
console.error('WebSocket error:', error);
});
ws.onmessage = (event) => {
const msg = JSON.parse(event.data);
if (msg.type === 'error') {
console.error('Realtime error:', msg.message);
}
};
```
### Related REST Endpoints
Presence tracking and channel broadcast are separate REST endpoints (not WebSocket messages), also mounted under `/v1/realtime` — see `src/realtime/presence.rs` and `src/realtime/broadcast.rs`:
| Method | Path |
| --- | --- |
| POST | `/v1/realtime/presence/track` |
| POST | `/v1/realtime/presence/untrack` |
| GET | `/v1/realtime/presence/:channel` |
| GET | `/v1/realtime/presence` |
| POST | `/v1/realtime/broadcast/send` |
| GET | `/v1/realtime/broadcast/channels` |
| GET | `/v1/realtime/broadcast/:channel/history` |
---
**Done!** 🎉
---
## AI, RAG & Enterprise AI Brain
**URL:** https://www.stackhousedb.com/docs/architecture-deep-dive/ai-and-rag
**Description:** RAG pipeline, embeddings, Qdrant, and the Enterprise AI Brain.
## 10. AI / RAG Pipeline
### AI Service Architecture
```mermaid
flowchart TD
subgraph Ingest["Document Ingest Pipeline"]
direction TD
I1["Upload (PDF/TXT/IMG)"] --> I2["DocumentParser
extract text / vision LLM for images"]
I2 --> I3["Chunker
Recursive/Fixed/Semantic, ~500 char chunks"]
I3 --> I4["EmbeddingService
OpenAI/Cohere/Gemini API, batched up to 2048 texts/call"]
I4 --> I5["Qdrant
HNSW index, cosine/L2/dot distance
decoupled from PostgreSQL"]
end
subgraph Query["Query Pipeline (RAG)"]
direction TD
Q1["User query:
"How do I configure auth?""] -.->|optional| Q2["Query Rewriter
LLM rewrites query for better search"]
Q2 --> Q3["Embed query vector
same model as docs"]
Q3 --> Q4["Hybrid Search
Qdrant (70%, cosine) + Keyword/BM25 in Postgres (30%)
combined via Reciprocal Rank Fusion"]
Q4 --> Q5["Reranker
CrossEncoder or LearningToRank"]
Q5 --> Q6["Top-K sources → Build context with citations"]
Q6 --> Q7["LLM (GPT/Claude/Gemini) → Generate answer"]
Q7 --> Q8["Return: {answer, sources[], usage}"]
end
```
**External API calls** (all rate-limited and cached):
- OpenAI: $0.0001/1K tokens (embedding), $0.002/1K (GPT)
- Anthropic: $0.008/1K tokens (Claude Sonnet)
- Cohere: $0.001/1K tokens (Rerank API)
### AI Pod Configuration (GPU vs CPU)
AI endpoints can run on CPU (small models) or GPU (large models):
**CPU Tier (standard nodes):**
- Embedding generation (text-embedding-3-small via API)
- Chunking (pure CPU, ~100K chunks/second)
- Reranking (heuristic, no model)
- LLM via API (network call, no local GPU needed)
**GPU Tier (optional, for local models):**
- Local embedding model (e.g., all-MiniLM-L6-v2)
- Local LLM (e.g., Llama 4 8B via Ollama)
- Only cost-effective at very high volume (>1M tokens/day)
At 100k users → Use API-based AI (OpenAI/Anthropic/Cohere)
At 10M users → Hybrid AI (Local GPU cluster + API routing) — see Enterprise AI Brain section
---
## Enterprise AI Brain & Digital Workforce
> **Enterprise Only**: This section covers Stackhouse's enterprise AI pivot — transforming from an AI-enhanced database to an **AI Orchestrator** for large organizations.
### 1. Enterprise AI Pivot
As Stackhouse scales into an Enterprise context, simple RAG and vector queries are not enough. Large organizations require autonomous problem-solving capabilities deeply integrated into their proprietary data silos.
The Enterprise AI Brain shifts Stackhouse from an AI-enhanced database to an **AI Orchestrator**.
| Component | Role |
|---|---|
| StackhouseBrain | Central LLM routing & logic |
| Digital Workforce | Fleet of autonomous agents |
| 47 SaaS Connectors | Native enterprise integrations |
| DLP Security | Audit & PII masking pipeline |
### 2. The Native 47 SaaS Connectors
Stackhouse natively embeds 47 enterprise connectors via the `stackhouse_connectors` module. These eliminate the need to pipe data through external ETLs (like Fivetran or Airbyte) before it reaches the AI.
**Connector Architecture:**
- **Universal REST Module:** Dynamic OAuth handling via `oauth_vault.rs`
- **Bidirectional:** Connectors don't just *pull* for RAG; they also *push* actions (e.g., "Create Jira Ticket", "Update Salesforce Lead")
- **Cost Scaling:** At 10M users, hosting a centralized Fivetran cluster for thousands of tenants becomes prohibitive. Stackhouse executes lightweight native polling/webhooks per tenant directly into the unified data model.
**Example Enterprise Connectors:**
- **CRM**: Salesforce, HubSpot, Pipedrive
- **Support**: Zendesk, Intercom, Freshdesk
- **DevOps**: Jira, GitHub, GitLab, Linear
- **Communication**: Slack, Teams, Discord
- **Storage**: Google Drive, Dropbox, Box, SharePoint
- **Finance**: Stripe, QuickBooks, Xero
- **Analytics**: Mixpanel, Amplitude, Segment
### 3. Autonomous Agents (Digital Workforce)
Stackhouse provides specialized domain experts (agents) that can be dispatched by users or workflows.
- **Agent Registry:** A compiled list of specialized agents (Support, Marketing, DevOps, Legal, Finance, etc.)
- **Tasks & Execution:** Built into the core runtime (`agent_registry.rs`), tasks are spawned as async Rust tasks interacting with the Connectors and the Brain
- **Human-in-the-loop:** `POST /v1/agent/task/:id/approve` allows agents to pause execution before performing destructive actions (e.g., dropping SaaS data, launching a mass email campaign) until a human approves
**Sample Agent Types:**
| Agent | Role | Example Task |
|-------|------|--------------|
| SupportAgent | Customer support | Analyze churn tickets, suggest responses |
| DataAnalyst | Business intelligence | Cross-reference Stripe + Zendesk for churn analysis |
| DevOpsAgent | Infrastructure | Monitor alerts, create incident tickets |
| MarketingAgent | Campaigns | Draft personalized email sequences |
| LegalAgent | Compliance | Review contracts against clause library |
| FinanceAgent | Accounting | Reconcile transactions across platforms |
### 4. Integration with PostgreSQL & Qdrant
While the RAG pipeline leverages Qdrant for semantic search and PostgreSQL for structured data, the Enterprise AI Brain acts as the overarching intelligence layer.
**Example Workflow:**
1. **User asks:** "Analyze churn rate based on recent Stripe cancellations and matching Zendesk tickets."
2. **StackhouseBrain:** Understands the intent
3. **Execution:**
- Dispatches the *Data Analyst Agent*
- Agent triggers the Stripe connector (pulls churn events)
- Agent triggers the Zendesk connector (pulls tickets)
- Joins data in an ephemeral SQLite / LSM table
- Computes analysis via local Llama 3 or GPT-4
- Returns result to user
### 5. Enterprise AI Architecture at 10M Scale
```mermaid
flowchart TD
R["Request arrives at /v1/ai/query
or via Agent dispatch"] --> Router
subgraph Router["StackhouseBrain AI Router (Rust service)"]
direction TD
R1["Intent classification (fast local model)"]
R2["User tier:
Free → local LLM
Pro → Claude Haiku or Gemini Flash
Enterprise → GPT-4o / Claude Opus"]
R3["Connector selection (which SaaS to query)"]
R4["Budget check: user monthly AI spend"]
end
Router --> GPU
Router --> API
subgraph GPU["Local GPU Cluster (H100)"]
direction TD
G1["Llama 3 70B"]
G2["Mistral 22B"]
G3["nomic-embed"]
end
subgraph API["External API Pool"]
direction TD
A1["OpenAI GPT-4o"]
A2["Anthropic Claude"]
A3["Google Gemini Pro"]
A4["Cohere embeddings"]
end
GPU --> Connectors
API --> Connectors
subgraph Connectors["47 SaaS Connectors (stackhouse_connectors module)"]
direction TD
C1["OAuth Vault (secure token management)"]
C2["Rate limiting per-connector"]
C3["Webhook handlers for real-time updates"]
C4["Unified data model → PostgreSQL + Qdrant"]
end
```
### 6. AI Cost Control at Enterprise Scale
| Measure | Description | Savings |
|---------|-------------|---------|
| Embedding cache | Redis, 7-day TTL | 70% reduction |
| Response cache | Cache LLM outputs for identical prompts (30min TTL) | 20-30% reduction |
| Tier routing | Free users → local Llama, not GPT-4 | 80% cost reduction for free tier |
| Prompt compression | LLMLingua/token compression for long context | 50% token reduction |
| Streaming | Don't cache streams — process quicker, cancel on disconnect | Reduces wasted tokens |
| Budget limits | Per-user monthly AI token quota (enforced in Redis) | Hard cost ceiling |
| GPU scaling | Scale AI GPU nodes to zero during off-peak (KEDA) | ~$8,000/month savings |
**Scale Economics:**
- At 100K users: API-only AI (~$500-2,000/month)
- At 10M users: Hybrid AI (Local GPU cluster + API) (~$14,000/month GPU + API costs)
- Break-even: ~5M tokens/day justifies local GPU investment
---
---
## Database & Caching Layer
**URL:** https://www.stackhousedb.com/docs/architecture-deep-dive/data-layer
**Description:** PostgreSQL high-availability, PgBouncer, Redis caching, and cache warming.
## 6. Database Layer
### PostgreSQL High Availability Setup
```mermaid
flowchart TD
PB["PgBouncer (Connection Pooler)
Mode: Transaction pooling · Pool size: 50 max
stackhouse pods (up to 30) → 50 real DB connections
Listen port: 5432 (internal only)"]
PB -->|Writes| Primary["PostgreSQL PRIMARY
n2-highmem-4 · 4 vCPU / 32GB
500GB SSD · wal_level=replica"]
PB -->|Reads| RR1["Read Replica 1
Async stream"]
PB -->|Reads| RR2["Read Rep 2
Async"]
Primary -->|Streaming Replication| RR1
Primary -->|Streaming Replication| RR2
Primary -->|WAL archiving| Storage["Object Storage
PITR: 7 days · WAL archives compressed with zstd
Enables point-in-time recovery"]
```
### Connection Flow with PgBouncer
```mermaid
flowchart TD
A["30 Stackhouse pods × 10 connections each
= 300 connection attempts"] --> B["PgBouncer (transaction pool)"]
B -->|multiplexed to| C["50 real PostgreSQL connections"]
```
`PostgreSQL max_connections = 100` (50 for app, 20 for admin, 20 for monitoring, 10 spare)
### Database Provider Comparison
| Feature | AWS RDS | GCP Cloud SQL | Hostinger MySQL | Hetzner (Self) | Heroku Postgres |
|---------|---------|---------------|-----------------|----------------|-----------------|
| PostgreSQL version | 16 | 16 | ❌ MySQL only | 16 (self-managed) | 15 |
| PITR | ✅ 35 days | ✅ 7 days | ❌ | Manual | ✅ 4 days |
| Read replicas | ✅ | ✅ | ❌ | Manual | ✅ (Premium) |
| Auto-failover | ✅ ~30s | ✅ ~60s | ❌ | Manual | ✅ |
| Connection pooling | PgBouncer addon | Built-in proxy | Limited | Self | Built-in |
| Encryption at rest | ✅ | ✅ | ✅ | Manual | ✅ |
| VPC isolation | ✅ | ✅ | ❌ shared | ✅ private net | ✅ |
| Monthly cost (4vCPU/32GB) | ~$350 | ~$300 | ~$50 (limited) | ~$120 | ~$400 |
---
## 7. Caching Layer
### Redis Cache Architecture
**Topology:** Redis Sentinel (3 nodes: 1 primary + 2 replicas), or Redis Cluster for >50GB cache.
**Cache key namespaces:**
| Key pattern | TTL | Purpose |
|---|---|---|
| `user:{id}` | 300s | user profile |
| `query:{collection}:{hash}` | 60s | query results |
| `ratelimit:{ip}:{endpoint}` | 120s | rate state |
| `session:{token_hash}` | 604800s | 7d refresh |
| `otp:{email}` | 600s | 10min OTP |
| `embedding:{text_hash}` | 86400s | embeddings |
| `pubsub:{collection}` | none | realtime channel |
```mermaid
flowchart LR
subgraph Hit["Cache hit flow"]
H1["Request"] --> H2["Redis GET"] --> H3["HIT"] --> H4["Return JSON (<1ms)"]
end
subgraph Miss["Cache miss flow"]
M1["Request"] --> M2["Redis GET"] --> M3["MISS"] --> M4["PostgreSQL"] --> M5["SET Redis"] --> M6["Return"]
end
subgraph Invalidate["Cache invalidation"]
I1["Write"] --> I2["PostgreSQL"] --> I3["DEL Redis key"] --> I4["PubSub 'invalidate' event"] --> I5["All pods invalidate local cache"]
end
```
**Cache sizing at 100k users:**
- Active users (10k concurrent) × avg session data (2KB) = 20MB
- Hot query results × avg result (10KB) = ~500MB
- Rate limit keys (100k IPs) × 64 bytes = 6.4MB
- **Total recommended:** 4-8GB Redis instance
### Cache Warming Strategy
**Cold start problem:** first request always hits the DB. **Solution:** predictive warming.
**Startup:**
1. Load top 100 most-queried collections into cache
2. Pre-warm user sessions from active refresh tokens
3. Pre-load AI embedding models into memory
**Rolling deploys:**
- New pod comes up → subscribes to Redis PubSub channels
- Serves requests immediately (cache shared across pods)
- No thundering herd (Redis absorbs load)
---
---
## Architecture Deep Dive
**URL:** https://www.stackhousedb.com/docs/architecture-deep-dive
**Description:** Enterprise production system design for Stackhouse: 100K–10M users, cloud provider analysis, and production architecture.
---
## Networking, Load Balancing & Security
**URL:** https://www.stackhousedb.com/docs/architecture-deep-dive/networking
**Description:** Cloudflare, load balancers, rate limiting, and defense-in-depth security.
## 3. Load Balancing
### Layer 4 vs Layer 7
```mermaid
flowchart TD
A["Incoming Connection"] --> B{"Protocol Detection
WebSocket?"}
B -->|YES| C["L4 / IP Hash / Sticky
No termination, pure tunnel"]
B -->|"HTTP/HTTPS"| D["L7 Load Balancer
(Path-aware routing)"]
D -->|"/v1/auth/*"| P1["Auth Pod Pool"]
D -->|"/v1/storage/*"| P2["Storage Pod Pool"]
D -->|"/v1/ai/*"| P3["AI Pod Pool (GPU nodes)"]
D -->|"/v1/realtime"| P4["Realtime Pod Pool"]
D -->|"/v1/*"| P5["General API Pod Pool"]
D -->|"/explore"| P6["Static Asset (CDN)"]
```
**Algorithm per pool:**
- General API: Round Robin (stateless)
- Realtime WS: Least Connections + IP Hash
- AI endpoints: Least Connections (variable load)
- Auth: Round Robin
### Health Check Configuration
| Setting | Value |
|---|---|
| Endpoint | `GET /health` |
| Interval | 10 seconds |
| Timeout | 3 seconds |
| Unhealthy | 2 consecutive failures |
| Healthy | 3 consecutive successes |
**Response checks:**
- HTTP 200 OK
- Body: `{"status":"healthy"}`
- Response time <500ms
- DB connectivity confirmed
**On failure:**
- Remove pod from rotation
- Alert PagerDuty (if >30% pods down)
- HPA triggers new pod immediately
### Provider Load Balancer Comparison
| Feature | AWS ALB | GCP Cloud LB | Hostinger LB | Hetzner LB | Heroku Router |
|---------|---------|--------------|--------------|------------|---------------|
| Layer | L7 | L7 (global) | L4/L7 | L4/L7 | L7 (dyno mesh) |
| WebSocket | ✅ | ✅ | ✅ | ✅ | ✅ (60s timeout) |
| Path routing | ✅ | ✅ | ⚠️ limited | ⚠️ limited | ❌ |
| Global anycast | ✅ | ✅ (best) | ❌ | ❌ | ❌ |
| SSL termination | ✅ | ✅ | ✅ | ✅ | ✅ |
| Cost/month | ~$20 base | ~$18 base | ~$5 | ~$6 | Included |
| Max connections | 60,000 | Unlimited | 10,000 | 10,000 | ~25,000 |
---
## 4. Rate Limiting
### Three-Layer Rate Limiting Strategy
```mermaid
flowchart TD
L1["Layer 1: Cloudflare Edge (Network Level)"] -->|Passes through| L2["Layer 2: Load Balancer (Connection Level)"]
L2 -->|Passes through| L3["Layer 3: Application (Stackhouse Governor Middleware)"]
```
### Rate Limit State — Redis Distributed Token Bucket
| Field | Value |
|---|---|
| Key | `ratelimit:{user_id}:{endpoint_group}` |
| Value | `{tokens_remaining, last_refill_timestamp}` |
| TTL | 120 seconds (auto-expire unused keys) |
**Example keys:**
- `ratelimit:usr_1234:api` → `{tokens: 87, ts: 172...}`
- `ratelimit:ip_1.2.3.4:auth` → `{tokens: 2, ts: 172...}`
- `ratelimit:usr_1234:ai` → `{tokens: 15, ts: 172...}`
Lua script (atomic check-and-decrement):
```lua
local tokens = redis.call('GET', key)
if tokens == false then tokens = max_tokens end
if tonumber(tokens) > 0 then
redis.call('DECR', key)
redis.call('EXPIRE', key, 120)
return 1 -- ALLOWED
else
return 0 -- BLOCKED
end
```
---
## 5. Security Architecture
### Defense-in-Depth Model
Five layers, outside-in (onion model):
```mermaid
flowchart TD
A["Layer 1: Network Perimeter"] --> B["Layer 2: Infrastructure"] --> C["Layer 3: Application"] --> D["Layer 4: Authentication & Authorization"] --> E["Layer 5: Data Security"]
```
**Layer 1 — Network Perimeter**
- Cloudflare DDoS mitigation (L3/L4/L7)
- Cloudflare WAF (OWASP CRS 3.3)
- Geo-blocking (optional, configurable per endpoint)
- IP reputation blocking (Cloudflare Threat Intelligence)
- TLS 1.3 only (TLS 1.0/1.1 disabled)
**Layer 2 — Infrastructure**
- VPC / Private Network (pods not publicly accessible)
- Network Policies (Kubernetes: pod-to-pod firewall)
- Security Groups (only LB → pod on port 3000 open)
- Secrets in Vault / KMS (not env vars in plain text)
- mTLS between internal services (Istio/Linkerd)
**Layer 3 — Application (Stackhouse Middleware)**
- SQL injection detection (pattern matching)
- XSS detection and output encoding
- Path traversal detection
- SSRF protection (URL validation, blocked internal IPs)
- Request size limits (10MB hard cap)
- Security response headers (CSP, HSTS, X-Frame-Options)
- Content-Type enforcement
- Security event logging (all violations logged)
**Layer 4 — Authentication & Authorization**
- Argon2id password hashing (memory: 64MB, iter: 3)
- JWT HS256 (1 hour access token)
- Refresh tokens (7 day, single-use, rotation on refresh)
- Brute force protection (exponential backoff lockout)
- MFA/TOTP with recovery codes
- OAuth2 PKCE + HMAC state verification
- Magic link (hashed tokens, rate limited)
- CAPTCHA (hCaptcha/reCAPTCHA/Turnstile)
- Row Level Security (per-table, per-user policies)
**Layer 5 — Data Security**
- Encryption at rest (disk-level AES-256)
- Encryption in transit (TLS 1.3 end-to-end)
- Password hashes never returned in API responses
- PII fields excluded from logs
- Database network isolated (no public internet access)
### JWT Token Lifecycle
```mermaid
flowchart TD
A["Login Request"] --> B["Verify Argon2 hash
60-120ms intentional delay"]
B --> C["Issue: Access Token (JWT, 1hr) +
Refresh Token (random 256-bit, 7d)"]
C --> AT["Access Token
Used in every API request
Header: Authorization: Bearer token
Validated in middleware (pure Rust, no DB hit)
Claims: sub=user_id, email, iat, exp"]
C --> RT["Refresh Token
Stored in stackhouse_sessions
HttpOnly cookie (recommended)
Used once → new pair issued
Rotated on every use"]
AT --> AE["Expired (1hr)"] --> AR["POST /v1/auth/refresh
+ refresh_token
→ New access_token + New refresh_token"]
RT --> RE["Expired (7d)"] --> RL["Re-login required"]
```
### Network Policy (Kubernetes)
```mermaid
flowchart TD
LB["Load Balancer"] -->|ALLOW| Pods["stackhouse pods :3000"]
Pods -->|ALLOW| Backends["postgres :5432
redis :6379
qdrant :6333 / :6334
(same namespace only)"]
```
**Denied by default:**
- Internet → postgres (no public port)
- Internet → redis (no public port)
- Internet → qdrant (no public port)
- Pod → Pod cross-namespace (unless explicitly allowed)
- Egress to internal AWS metadata (169.254.169.254)
---
---
## Observability, CI/CD, DR & Auto-scaling
**URL:** https://www.stackhousedb.com/docs/architecture-deep-dive/operations
**Description:** Prometheus/Grafana, CI/CD, disaster recovery, backups, and auto-scaling.
## 11. Observability (Metrics, Logs, Tracing)
**Metrics (Prometheus + Grafana)**
Stackhouse exposes `/v1/stats` → Prometheus scrapes every 15s.
Key metrics: `stackhouse_requests_total{method,path,status}`, `stackhouse_request_duration_seconds` (histogram), `stackhouse_active_connections`, `stackhouse_db_query_duration_seconds`, `stackhouse_cache_hit_rate`, `stackhouse_rate_limited_requests_total`, `stackhouse_auth_failures_total`, `container_{cpu,memory}_usage` (from cAdvisor).
Alerting rules (Alertmanager → PagerDuty):
- p99 latency >500ms for 5 minutes
- Error rate >5% for 2 minutes
- DB connection pool utilization >80%
- Pod restart count >3 in 10 minutes
- Disk usage >80%
**Logs (Structured JSON → Log Aggregator)**
Format: `{"ts":"2026-03-24T10:00:00Z","level":"info","method":"POST","path":"/v1/push/users","status":201,"latency_ms":12,"user_id":1234}`
Log levels: ERROR → WARN → INFO → DEBUG (configurable). Separate log files:
- `stackhouse.log` (all application logs)
- `error.log` (ERROR level only)
- `query.log` (slow queries >100ms)
Shipping: Fluentd/Vector → Elasticsearch/Loki/Datadog.
**Tracing (OpenTelemetry → Jaeger/Tempo)**
Every request gets an `X-Request-ID` header. Traces: HTTP request → DB query → Cache → Response. Useful for finding bottlenecks and debugging slow requests.
### Provider Observability Options
| Feature | AWS | GCP | Hetzner | Hostinger | Heroku |
|---------|-----|-----|---------|-----------|--------|
| Native metrics | CloudWatch | Cloud Monitoring | ❌ self | ❌ self | Basic |
| Log aggregation | CloudWatch Logs | Cloud Logging | Self (ELK) | Self | Logplex |
| APM/Tracing | X-Ray | Cloud Trace | Self (Jaeger) | Self | ❌ |
| Cost/month | ~$50-200 | ~$30-100 | ~$20 self-hosted | ~$20 | $0-75 |
| Alerting | CloudWatch Alerts | Cloud Alerting | Grafana | Grafana | Papertrail |
---
## 12. CI/CD Pipeline
```mermaid
flowchart TD
Dev["Developer"] -->|git push| GH["GitHub"]
GH -->|Trigger| CI["CI Runner"]
CI --> S1["Stage 1: Test (5min)
cargo test --all · cargo clippy
cargo audit · Integration tests"]
S1 -->|PASS| S2["Stage 2: Build (3min)
docker buildx build
--platform linux/amd64,linux/arm64
Push to registry"]
S2 --> S3["Stage 3: Deploy
main branch only"]
S3 --> Staging["Staging first:
kubectl set image …
Wait 2 min"]
Staging --> Smoke{"Smoke tests pass?"}
Smoke -->|YES| Prod["Production rollout:
Rolling deploy 25% → 50% → 100%
(automated rollback on error)"]
```
**Rollback trigger:** error rate >10% OR p99 latency >2s after deploy → `kubectl rollout undo deployment/stackhouse`
### Docker Build — Multi-stage for Minimal Image
```dockerfile
# Build stage (cargo build)
FROM rust:1.82 AS builder
WORKDIR /app
COPY Cargo.toml Cargo.lock ./
RUN cargo fetch
COPY src/ ./src/
RUN cargo build --release --bin stackhouse
# Runtime stage (minimal image)
FROM debian:bookworm-slim
RUN apt-get update && apt-get install -y ca-certificates && rm -rf /var/lib/apt/lists/*
COPY --from=builder /app/target/release/stackhouse /usr/local/bin/stackhouse
EXPOSE 3000
USER 1000 # Non-root
ENTRYPOINT ["stackhouse"]
# Final image size: ~50MB vs 2GB+ for full Rust image
```
---
## 13. Disaster Recovery & Backups
### Backup Strategy
**RPO** = Recovery Point Objective (max data loss acceptable). **RTO** = Recovery Time Objective (max downtime acceptable).
**Target:** RPO <5 minutes, RTO <30 minutes.
**Backup types:**
1. **PostgreSQL WAL Archiving** (continuous, scaling target) — WAL segments shipped to object storage; would enable PITR to any second within the retention window; not wired into Stackhouse's own code today (external Postgres tooling would provide this)
2. **Application-level logical backups** (`stackhouse_backups` table + `/v1/admin/backups/*`, real, implemented today) — `BackupManager` runs `SELECT * FROM ""` per table and writes the result to a backup file; restore and delete are exposed via `POST /v1/admin/backups/:id/restore` and `DELETE /v1/admin/backups/:id`. This is a logical snapshot mechanism, not WAL-based — no continuous point-in-time granularity between snapshots.
3. **`PitrService`** (`stackhouse/src/storage/backups/pitr.rs`, real WAL-replay logic, but **not wired to any HTTP route**) — implements real point-in-time restore by replaying WAL from a logical replication slot to a target timestamp. The logic exists and works when called directly, but there is no `/v1/admin/*` (or any other) endpoint that invokes it today, so it isn't reachable in a running server.
4. **Object Storage Versioning** — all file uploads versioned; 30-day version retention; cross-region replication for critical buckets (scaling target, not verified against current object-storage code)
**Recovery scenarios:**
| Scenario | Response | RTO / RPO |
|---|---|---|
| Pod crashes | Kubernetes restarts pod (<30s), no data loss; health check removes bad pod immediately | <30s |
| DB primary fails | Automatic failover to read replica; PgBouncer reconnects to new primary; brief write unavailability | ~60s RTO |
| Region outage | DNS failover to backup region (manual or Route 53) | RTO 15-30 min · RPO up to 5 min (last WAL archive) |
| Data corruption | PITR restore to pre-corruption timestamp | RTO 30-60 min |
---
## 14. Auto-scaling Strategy
### Horizontal Pod Autoscaler Logic
Metrics collected every 15 seconds: CPU utilization per pod, memory utilization per pod, requests per second (custom metric via Prometheus adapter), WebSocket connection count.
**Pod count limits:**
| Pool | Min | Max |
|---|---|---|
| API pods | 3 | 30 |
| AI pods | 1 | 10 |
| Realtime pods | 2 | 15 |
| DB pods | 1 | 1 (vertical scale instead) |
**Scale simulation at 100k users:**
| Users | Concurrent | RPS | API Pods | DB Conns | Redis |
|---|---|---|---|---|---|
| 10k | 1,000 | 100 | 3 | 15 | 512MB |
| 50k | 5,000 | 500 | 7 | 30 | 2GB |
| 100k | 10,000 | 1,000 | 13 | 50 | 4GB |
| 200k | 20,000 | 2,000 | 26 | 50* | 8GB |
\* PgBouncer limit
### Cluster Autoscaler (Node-level)
```mermaid
flowchart LR
A["Pod needs more resources"] --> B["HPA adds pod"] --> C{"No node capacity?"}
C --> D["Cluster Autoscaler provisions new node
GKE: ~2-3 min · AWS EKS: ~2-4 min
Hetzner K3s: ~3-5 min (slower cold)"]
```
**Node pool config:** min nodes 3 (HA, spread across 3 zones) · max nodes 10 (cost ceiling) · node type `e2-standard-4` (4vCPU, 16GB) on GCP
---
---
## Architecture Overview & Traffic Flow
**URL:** https://www.stackhousedb.com/docs/architecture-deep-dive/overview
**Description:** Architecture overview, scale targets from 100K to 10M users, and request lifecycle.
## 1. Architecture Overview
Stackhouse is a Rust-based Supabase alternative for enterprise with:
### Scale Targets: 100K → 10M
| Metric | 100K Baseline (SMB) | 10M Enterprise 🏢 |
|--------|---------------------|-------------------|
| Total users | 100,000 | 10,000,000 |
| Concurrent users | 10,000 | 1,000,000 |
| Peak RPS (API) | 1,000 | 100,000 |
| WebSocket connections | 50,000 | 5,000,000 |
| DB queries/sec | 5,000 | 500,000 |
| Redis cache size | 4–8 GB | 500 GB–2 TB |
| API pods | 13 | 300–1,000 |
| PostgreSQL primaries | 1 | 10–20 (shards) |
| Regions | 1 | 3–5 (active-active) |
| Monthly cost (GCP) | ~$1,295 | ~$85,000–$120,000 |
**Architecture Differences by Scale:**
- **100K**: Single region, single PostgreSQL primary + replicas, Redis Sentinel
- **10M Enterprise 🏢**: Multi-region active-active, Citus sharding, Kafka event bus, Istio service mesh
Stackhouse core components:
- **HTTP/WS API** — Axum (Rust)
- **Auth** — Argon2 + JWT
- **Database** — PostgreSQL via `sqlx` (no separate custom storage engine — see the current-state note below)
- **Vector Search** — Qdrant (dedicated vector database)
- **Realtime** — WebSocket + SSE broadcast (in-process `tokio::broadcast`, not Redis pub/sub, today)
- **Storage** — S3-compatible bucket system
- **AI** — RAG pipeline, embeddings, LLM routing
- **Edge Functions** — JavaScript runtime (embedded Boa engine); router mounted under `/v1/functions` in `main.rs`
### High-Level System Diagram
```mermaid
flowchart TD
Internet["Internet"] --> CF["Cloudflare (All Providers)
DDoS Protection · WAF · CDN · DNS · TLS Termination"]
CF --> LB["Load Balancer
L4/L7 · Health Checks · SSL Offload · Sticky"]
LB --> Pod1["Stackhouse Pod 1
Rust/Axum · Port 3000"]
LB --> Pod2["Stackhouse Pod 2
Rust/Axum · Port 3000"]
LB --> Pod3["Stackhouse Pod 3
Rust/Axum · Port 3000"]
Pod1 --> Mesh["Internal Service Mesh"]
Pod2 --> Mesh
Pod3 --> Mesh
Mesh --> PG["PostgreSQL
Primary + 2 RR · PgBouncer Pool"]
Mesh --> Redis["Redis Cluster
Cache + PubSub · Rate Limit State"]
Mesh --> Obj["Object Storage
S3/GCS/MinIO · Bucket-based Files"]
```
---
## 2. Traffic Flow & Request Lifecycle
```mermaid
flowchart TD
Req["User Request"] --> S1
S1["Step 1: DNS Resolution
api.stackhouse.io → Cloudflare Edge IP (Anycast)"] --> S2
S2["Step 2: Cloudflare Edge Processing
TLS 1.3 Termination (ECDSA) · DDoS L3/L4/L7 mitigation
WAF rules (OWASP Top 10) · Bot detection (JS Challenge)
Rate limit: 100 req/s per IP · Cache static assets (CDN HIT → return)"]
S2 -->|Cache MISS → forward| S3
S3["Step 3: Load Balancer (Layer 7)
Health check routing · Sticky sessions for WebSocket
SSL re-encryption (LB → Pod mTLS) · Header injection (X-Real-IP, X-Request-ID)"] --> S4
S4["Step 4: Stackhouse Rust Middleware (Axum)
Security headers · Request size limit (10MB cap)
Input validation (SQLi, XSS, path traversal) · JWT validation → user_id
Rate limit (Governor: 100 RPS/IP) · RLS policy evaluation
Route dispatch → handler · Response security headers (CSP, HSTS, X-Frame)"] --> S5
S5["Step 5: Data Layer
Redis cache lookup (L1, TTL 60s) · HIT → return from cache
MISS → PgBouncer pool → PostgreSQL query
Write → invalidate cache → broadcast via Redis PubSub"] --> S6
S6["Step 6: Response Path
Compress (gzip/brotli if >1KB) · Set cache headers (Cache-Control, ETag)
Emit metrics to Prometheus · Log structured JSON to aggregator"]
```
**Total target latency:** p50 < 20ms · p95 < 100ms · p99 < 300ms
---
---
## Provider Breakdown, Cost Analysis & GCP Reference
**URL:** https://www.stackhousedb.com/docs/architecture-deep-dive/providers-and-costs
**Description:** AWS, GCP, Hetzner, Hostinger, Heroku comparison, cost tables, and GKE manifests.
## 15. Provider-by-Provider Breakdown
### AWS Architecture
**Region:** us-east-1 (primary) + us-west-2 (DR)
**Networking:** VPC `10.0.0.0/16` · Public subnets `10.0.1.0/24`, `10.0.2.0/24` (ALB, NAT Gateway) · Private subnets `10.0.10.0/24`, `10.0.11.0/24` (EKS nodes, RDS, ElastiCache) · no direct internet access to private subnets
**Compute:** EKS (Elastic Kubernetes Service) — node group 3-10× m6i.xlarge (4vCPU/16GB) at $0.192/hr each · 50% spot instances (saves ~70%) with fallback · Fargate for burst workloads (AI jobs)
**Database:** RDS PostgreSQL 16 — db.r6g.xlarge (4vCPU/32GB) at $0.48/hr · Multi-AZ synchronous standby (30s failover) · 2× read replicas (db.r6g.large) · 500GB gp3 SSD with auto-scaling · automated backups with 35-day PITR
**Cache:** ElastiCache Redis 7 — cache.r6g.large (2vCPU/13GB) at $0.166/hr · cluster mode with 3 shards × 2 replicas
**Storage:** S3 — standard $0.023/GB/month · egress $0.09/GB (expensive!) · CloudFront CDN for public assets
**Load Balancer:** ALB — $0.008/LCU/hour + $0.016/hour base · path-based routing · WebSocket support (upgrade headers pass-through)
**Secrets:** AWS Secrets Manager + KMS · **Logs:** CloudWatch Logs ($0.50/GB ingested) · **Metrics:** CloudWatch + Prometheus on EKS · **CI/CD:** CodePipeline + ECR
### GCP Architecture
**Region:** us-central1 (primary) + us-east1 (DR)
**Networking:** VPC custom mode, global · subnets us-central1 `10.10.0.0/20` · Private Google Access enabled · Cloud NAT for outbound · VPC Service Controls (data exfil prevention)
**Compute:** GKE Autopilot (recommended) or Standard — Autopilot pays per pod (CPU+Memory), no node management · Standard: 3-10× e2-standard-4 (4vCPU/16GB) at $0.134/hr · Spot VMs: 60-80% discount with preemption handling
**Database:** Cloud SQL for PostgreSQL 16 — db-custom-4-26624 (4vCPU/26GB) at ~$300/month · HA synchronous replica with auto-failover · 2× read replicas in same region · built-in connection proxy (no separate PgBouncer needed) · 500GB SSD with auto-increase · automated backups with 7-day PITR
**Cache:** Memorystore for Redis — 5GB standard tier at ~$150/month · high availability mode (with replica)
**Storage:** GCS — standard $0.020/GB/month · egress $0.12/GB (between regions) · Cloud CDN for public assets
**Load Balancer:** Cloud Load Balancing (Global HTTP(S)) — ~$0.025/million requests · global anycast (best-in-class routing) · integrated with Cloud Armor WAF
**Security:** Cloud Armor (WAF) — $5/policy + $0.75/million req · **Secrets:** Secret Manager ($0.06/10k access) · **Logs:** Cloud Logging (first 50GB/month free) · **CI/CD:** Cloud Build + Artifact Registry
### Hetzner Architecture
**Location:** Nuremberg (EU) + Hillsboro (US)
**Compute:** Hetzner Cloud + k3s (lightweight Kubernetes) — 3× CPX31 nodes (4vCPU/8GB) at €12.20/month each, or 3× CCX23 (4vCPU/16GB dedicated) at €50.40/month each · k3s (not full K8s), simpler and great for small clusters
**Database:** Managed PostgreSQL (Hetzner DB) — 4GB/2vCPU at ~€20/month (very affordable), or self-managed on dedicated CPX51 (8vCPU/32GB) at €85/mo · no built-in PITR (manual WAL archiving needed) · no read replicas in managed offering
**Cache:** Redis on separate VM — CPX21 (3vCPU/4GB) at €9.90/month, self-managed (install, configure, monitor yourself)
**Storage:** Hetzner Object Storage — S3-compatible API ✅ · €0.0119/GB/month (much cheaper than AWS S3) · free egress within Hetzner network
**Load Balancer:** Hetzner Load Balancer — LB11 €5.39/month (5 targets, 20M requests) · WebSocket support ✅
**Firewall:** Hetzner Cloud Firewall (free) · **CDN:** Cloudflare (free tier works fine) · **WAF:** Cloudflare WAF ($20/month Pro plan) · **Secrets:** self-managed Vault or env vars in k3s secrets · **Logs:** self-hosted Grafana + Loki + Promtail · **Metrics:** self-hosted Prometheus + Grafana
### Hostinger Architecture
**Compute:** VPS plans — KVM 8: 8vCPU/32GB at $9.99/month (shared, not dedicated) · no Kubernetes support · Docker possible, but no orchestration
**Database:** MySQL only (no PostgreSQL in managed offering) ❌ · can self-install PostgreSQL on VPS · no PITR, no read replicas, no auto-failover
**Storage:** limited local disk storage · no S3-compatible object storage
**Networking:** shared IPv4 on cheaper plans · 1Gbps uplink · no VPC / private networking
**Verdict for Stackhouse:** ✅ good for dev environment, demo, personal projects · ✅ good for landing page, static site · ❌ bad for production Stackhouse serving 100k users
Alternative use: Hostinger for frontend (Next.js dashboard) while backend runs on GCP/Hetzner.
### Heroku Architecture
Heroku is PaaS (Platform as a Service) — managed deployment, owned by Salesforce. Great DX, limited customization.
**Compute:** Dynos — Standard-2X (1vCPU/1GB) $50/month/dyno · Performance-M (2vCPU/2.5GB) $250/month/dyno · for 100k users need ~13 Performance-M dynos = $3,250/month · autoscaling ✅ (Heroku Autoscale add-on, $10/month)
**Database:** Heroku Postgres — Standard-7 (4vCPU/7.5GB) $175/month · Standard-15 (4vCPU/15GB) $350/month · Premium-0 (4vCPU/15GB + follower) $400/month · PITR ✅ 4 days on Standard+ · max connections 120 (Standard-7), 500 (Premium)
**Cache:** Heroku Data for Redis — Premium-0 (100MB) $30/month · Premium-1 (1GB) $75/month · Premium-3 (5GB) $250/month
**Networking:** Heroku Router handles load balancing (included) · Private Spaces $1,700/month (VPC-like isolation) · no custom TCP/IP configuration
**Storage:** no native file storage (use AWS S3 add-on) · Cloudinary/Backblaze as add-ons
**Logging:** Logplex → add-ons (Papertrail $7/month, Datadog) · **Metrics:** AppSignal add-on (~$19/month)
---
## 16. Cost Comparison at 100k Users
### Monthly Cost Breakdown
**Configuration basis:** 13 API pods (4vCPU/16GB nodes) · 1 PostgreSQL primary + 2 read replicas (4vCPU/32GB) · 1 Redis cluster (5GB) · 100GB object storage + 500GB egress · load balancer · monitoring, logging, backups
| Component | AWS (us-e-1) | GCP (us-cen) | Hetzner (EU) | Heroku |
|---|---|---|---|---|
| Compute (pods) | $430 | $380 | $120 | $3,250 |
| PostgreSQL Primary | $350 | $300 | $85 | $400 |
| PostgreSQL Replicas | $200 | $150 | $120 (self) | Included |
| PgBouncer | $50 | $0 (built-in) | $10 | N/A |
| Redis | $120 | $150 | $30 | $250 |
| Load Balancer | $40 | $30 | $15 | Included |
| Object Storage | $90 | $80 | $20 | $60 (S3) |
| Egress | $45 | $60 | $0 | $25 |
| WAF/DDoS | $50 | $35 | $0 (CF free) | $0 |
| Cloudflare Pro | $20 | $20 | $20 | $20 |
| Monitoring | $80 | $50 | $20 (self) | $75 |
| Backups/Storage | $30 | $20 | $15 | Included |
| Secrets Manager | $10 | $5 | $0 (self) | $0 |
| CI/CD | $20 | $15 | $0 (GitHub) | $0 |
| Support Plan | $0-$100 | $0 | $0 | $0 |
| **Total/month** | **~$1,535** | **~$1,295** | **~$455** | **~$4,080** |
| **Total/year** | **~$18,420** | **~$15,540** | **~$5,460** | **~$48,960** |
**Hostinger:** Not comparable — insufficient for this use case. Estimated ~$200/month but requires significant additional services.
**Notes:**
- AWS: Spot instances can reduce compute by 60% → ~$1,200/month
- GCP: Sustained use discounts apply automatically — already reflected
- Hetzner: Requires more DevOps time (factor in ~$500-1000/month engineering)
- Heroku: Prices increase linearly with users — very expensive at scale
### Cost Efficiency per User
**Monthly cost / 100,000 users:**
| Provider | Cost | Per user/month |
|---|---|---|
| AWS | $1,535 / 100,000 | $0.015 |
| GCP | $1,295 / 100,000 | $0.013 ← best managed cloud |
| Hetzner | $455 / 100,000 | $0.0046 ← cheapest total |
| Heroku | $4,080 / 100,000 | $0.041 ← most expensive |
**Engineering cost consideration:** Hetzner requires 5+ additional engineering hours/month vs GCP. At $100/hr, that's $500 extra → Hetzner's real cost is ≈$955/month, still 26% cheaper than GCP, but the gap narrows significantly.
---
## 17. Decision Matrix
Score: 1 (poor) to 5 (excellent)
| Criterion | AWS | GCP | Hetzner | Hostinger | Heroku | Weight |
|---|---|---|---|---|---|---|
| Cost efficiency | 3 | 4 | 5 | 4 | 1 | 20% |
| PostgreSQL maturity | 5 | 5 | 3 | 1 | 4 | 15% |
| Kubernetes support | 5 | 5 | 3 | 1 | 2 | 15% |
| Auto-scaling ease | 5 | 5 | 2 | 1 | 4 | 10% |
| Global availability | 5 | 5 | 3 | 2 | 3 | 10% |
| WebSocket support | 5 | 5 | 5 | 3 | 3 | 8% |
| Managed services depth | 5 | 5 | 2 | 2 | 4 | 8% |
| Security features | 5 | 5 | 3 | 2 | 3 | 7% |
| Developer experience | 4 | 4 | 3 | 4 | 5 | 5% |
| Disaster recovery | 5 | 5 | 2 | 1 | 4 | 2% |
| **Weighted score** | **4.6** | **4.8** | **3.1** | **1.9** | **2.8** | |
| **Recommendation** | ✅ Good | ⭐ Best | ✅$ Budget | ❌ Dev only | ❌ Staging | |
⭐ = Best overall (GCP) · ✅ = Good choice (AWS) · ✅$ = Best if budget-constrained + DevOps capacity (Hetzner) · ❌ = Not recommended for production at scale
### Use Case Recommendations
| If you are… | Choose |
|---|---|
| Startup, tight budget, EU users, DevOps-capable team | Hetzner + Cloudflare |
| Startup, scaling fast, want managed, global reach | GCP (best all-around) |
| Enterprise, AWS already in stack, need compliance (SOC2, HIPAA) | AWS |
| Just getting started, need to ship fast, <5k users | Heroku (then migrate) |
| Personal project, portfolio, demos | Hostinger / Railway / Fly.io |
| EU data residency required, GDPR priority | Hetzner (Nuremberg DC) |
---
## 18. Recommended Architecture: GCP
### Full GCP Production Architecture
Stackhouse @ 100k Users.
```mermaid
flowchart TD
CF["Cloudflare (CDN + WAF + DDoS)
api.stackhouse.io → proxied through Cloudflare"] --> LB
LB["Google Cloud Load Balancing (Global HTTP(S))
Cloud Armor WAF (OWASP rules)
SSL certificate (managed Let's Encrypt)
Backend service → NEG (Network Endpoint Group) → GKE pods"] --> GKE
subgraph GKE["GKE Cluster (us-central1, 3 zones)"]
NP1["Node Pool 1: API Tier (e2-standard-4, 3-10 nodes)
stackhouse-api pod×3..10 · stackhouse-auth pod×2 · stackhouse-storage pod×2
Each pod: 500m CPU request / 2000m limit · 256MB / 512MB memory"]
NP2["Node Pool 2: Realtime Tier (e2-standard-2, 2-5 nodes)
stackhouse-realtime pod×2..5 — WebSocket handlers with sticky sessions"]
NP3["Node Pool 3: AI Tier (n2-standard-4, 0-3 nodes)
autoscale to zero · stackhouse-ai pod×0..3 — RAG pipeline, embedding, LLM routing"]
SYS["System pods
cert-manager, prometheus, grafana, fluentd, pgbouncer"]
end
GKE --> PG["Cloud SQL PostgreSQL Primary
db-custom-4/26624 · 500GB SSD
+ Read Rep 1, Read Rep 2 (streaming replicas)
Cloud SQL Proxy sidecar in each pod → IAM auth, no hardcoded passwords"]
GKE --> Redis["Memorystore Redis 7
5GB HA (primary + replica)"]
GKE --> GCS["GCS Bucket stackhouse-store
Multi-regional · $0.026/GB"]
```
**Supporting services:** Secret Manager (JWT secret, DB passwords, API keys) · Cloud Monitoring (metrics dashboards + alerting) · Cloud Logging (centralized logs, 50GB free/month) · Artifact Registry (Docker images) · Cloud Build (CI/CD pipeline) · Cloud Scheduler (backup jobs, cleanup tasks)
---
## 19. Kubernetes Manifests (GKE)
### Deployment
```yaml
# stackhouse-deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
name: stackhouse-api
namespace: stackhouse
labels:
app: stackhouse-api
version: "1.0.0"
spec:
replicas: 3
selector:
matchLabels:
app: stackhouse-api
strategy:
type: RollingUpdate
rollingUpdate:
maxSurge: 1
maxUnavailable: 0 # Zero downtime deployments
template:
metadata:
labels:
app: stackhouse-api
annotations:
prometheus.io/scrape: "true"
prometheus.io/port: "3000"
prometheus.io/path: "/v1/stats"
spec:
serviceAccountName: stackhouse-sa
terminationGracePeriodSeconds: 30
topologySpreadConstraints:
- maxSkew: 1
topologyKey: topology.kubernetes.io/zone
whenUnsatisfiable: DoNotSchedule
labelSelector:
matchLabels:
app: stackhouse-api
containers:
- name: stackhouse
image: us-central1-docker.pkg.dev/PROJECT/stackhouse/api:latest
imagePullPolicy: Always
ports:
- containerPort: 3000
env:
- name: STACKHOUSE_PORT
value: "3000"
- name: STACKHOUSE_HOST
value: "0.0.0.0"
- name: STACKHOUSE_URL
valueFrom:
secretKeyRef:
name: stackhouse-secrets
key: database-url
- name: STACKHOUSE_JWT_SECRET
valueFrom:
secretKeyRef:
name: stackhouse-secrets
key: jwt-secret
- name: REDIS_URL
valueFrom:
secretKeyRef:
name: stackhouse-secrets
key: redis-url
resources:
requests:
cpu: "500m"
memory: "256Mi"
limits:
cpu: "2000m"
memory: "512Mi"
livenessProbe:
httpGet:
path: /health
port: 3000
initialDelaySeconds: 10
periodSeconds: 10
failureThreshold: 3
readinessProbe:
httpGet:
path: /health
port: 3000
initialDelaySeconds: 5
periodSeconds: 5
successThreshold: 1
lifecycle:
preStop:
exec:
command: ["/bin/sh", "-c", "sleep 5"] # Drain requests
securityContext:
runAsNonRoot: true
runAsUser: 1000
readOnlyRootFilesystem: true
allowPrivilegeEscalation: false
capabilities:
drop: ["ALL"]
- name: cloud-sql-proxy # Sidecar for DB
image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:latest
args:
- "--structured-logs"
- "--port=5432"
- "PROJECT:us-central1:stackhouse-pg"
resources:
requests:
cpu: "50m"
memory: "64Mi"
limits:
cpu: "200m"
memory: "128Mi"
---
apiVersion: v1
kind: Service
metadata:
name: stackhouse-api
namespace: stackhouse
spec:
selector:
app: stackhouse-api
ports:
- port: 80
targetPort: 3000
protocol: TCP
type: ClusterIP
---
apiVersion: autoscaling/v2
kind: HorizontalPodAutoscaler
metadata:
name: stackhouse-api-hpa
namespace: stackhouse
spec:
scaleTargetRef:
apiVersion: apps/v1
kind: Deployment
name: stackhouse-api
minReplicas: 3
maxReplicas: 30
metrics:
- type: Resource
resource:
name: cpu
target:
type: Utilization
averageUtilization: 60
- type: Resource
resource:
name: memory
target:
type: Utilization
averageUtilization: 70
behavior:
scaleUp:
stabilizationWindowSeconds: 60
policies:
- type: Pods
value: 3
periodSeconds: 60
scaleDown:
stabilizationWindowSeconds: 600 # Wait 10 min before scaling down
policies:
- type: Pods
value: 1
periodSeconds: 120
---
apiVersion: policy/v1
kind: PodDisruptionBudget
metadata:
name: stackhouse-api-pdb
namespace: stackhouse
spec:
minAvailable: 2 # Always keep 2 pods up
selector:
matchLabels:
app: stackhouse-api
---
apiVersion: networking.k8s.io/v1
kind: NetworkPolicy
metadata:
name: stackhouse-netpol
namespace: stackhouse
spec:
podSelector:
matchLabels:
app: stackhouse-api
policyTypes:
- Ingress
- Egress
ingress:
- from:
- namespaceSelector:
matchLabels:
name: ingress-nginx
ports:
- port: 3000
egress:
- to:
- podSelector:
matchLabels:
app: stackhouse-api # Allow pod-to-pod (same app)
- ports:
- port: 5432 # PostgreSQL (via Cloud SQL Proxy)
- port: 6379 # Redis
- port: 6333 # Qdrant REST API
- port: 6334 # Qdrant gRPC API
- port: 443 # External HTTPS (OpenAI, etc.)
- port: 53 # DNS
```
### PgBouncer ConfigMap
```yaml
# pgbouncer-config.yaml
apiVersion: v1
kind: ConfigMap
metadata:
name: pgbouncer-config
namespace: stackhouse
data:
pgbouncer.ini: |
[databases]
stackhouse = host=127.0.0.1 port=5432 dbname=stackhouse
[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
reserve_pool_size = 10
reserve_pool_timeout = 3
server_lifetime = 3600
server_idle_timeout = 600
log_connections = 0
log_disconnections = 0
stats_period = 60
```
---
## 20. Environment Variables & Secrets
### Complete Environment Reference
```bash
# ================================================
# STACKHOUSE PRODUCTION ENVIRONMENT VARIABLES
# Store in GCP Secret Manager / AWS Secrets Manager
# NEVER commit to git
# ================================================
# Server
STACKHOUSE_PORT=3000
STACKHOUSE_HOST=0.0.0.0
STACKHOUSE_LOG_DIR=/var/log/stackhouse
# Database (via Cloud SQL Proxy or direct)
STACKHOUSE_URL=postgres://stackhouse_user:PASSWORD@127.0.0.1:5432/stackhouse
# Authentication
STACKHOUSE_JWT_SECRET=<256-bit random hex, generated at deploy>
# Generate: openssl rand -hex 32
# Redis
REDIS_URL=redis://:PASSWORD@10.0.0.5:6379/0
# Qdrant (Vector Database)
QDRANT_URL=http://qdrant.stackhouse.svc.cluster.local:6333
# Object Storage
GCS_BUCKET=stackhouse-storage-prod
GCS_PROJECT=your-gcp-project
# OR for AWS S3:
AWS_S3_BUCKET=stackhouse-storage-prod
AWS_REGION=us-east-1
AWS_ACCESS_KEY_ID=
AWS_SECRET_ACCESS_KEY=
# AI Services (all optional)
OPENAI_API_KEY=sk-...
ANTHROPIC_API_KEY=sk-ant-...
GEMINI_API_KEY=AIza...
COHERE_API_KEY=...
# OAuth Providers
GOOGLE_CLIENT_ID=xxx.apps.googleusercontent.com
GOOGLE_CLIENT_SECRET=xxx
GITHUB_CLIENT_ID=xxx
GITHUB_CLIENT_SECRET=xxx
# CAPTCHA
HCAPTCHA_SECRET=xxx
RECAPTCHA_SECRET_KEY=xxx
TURNSTILE_SECRET_KEY=xxx
# Twilio (phone OTP)
TWILIO_ACCOUNT_SID=xxx
TWILIO_AUTH_TOKEN=xxx
TWILIO_PHONE_NUMBER=+1xxx
# Monitoring
SENTRY_DSN=https://xxx@o0.ingest.sentry.io/0
DATADOG_API_KEY=xxx
# Feature flags
STACKHOUSE_ENABLE_FUNCTIONS=true
STACKHOUSE_ENABLE_AI_ENDPOINTS=true
STACKHOUSE_ENABLE_REALTIME=true
STACKHOUSE_MAX_FILE_SIZE_MB=100
STACKHOUSE_RATE_LIMIT_RPS=100
```
---
## Summary Comparison Table
| Feature | AWS | GCP ⭐ | Hetzner | Hostinger | Heroku |
|---|---|---|---|---|---|
| Monthly cost @100k | ~$1,535 | ~$1,295 | ~$455 | N/A | ~$4,080 |
| PostgreSQL managed | RDS ✅ | Cloud SQL ✅ | Self-mgd ⚠️ | MySQL only ❌ | Heroku PG ✅ |
| Kubernetes | EKS ✅ | GKE ✅ | k3s ⚠️ | ❌ | ❌ |
| Auto-scaling | ✅ | ✅ | Manual ⚠️ | ❌ | ✅ (add-on) |
| Global CDN | CloudFront | Cloud CDN | Cloudflare | Cloudflare | Cloudflare |
| WAF | AWS WAF | Cloud Armor | CF Free | CF Free | CF Free |
| WebSocket support | ✅ | ✅ | ✅ | ✅ (limited) | ⚠️ (55s TO) |
| Object storage | S3 ✅ | GCS ✅ | Hetzner S3 ✅ | ❌ self | Need S3 add-on |
| Redis managed | ElastiCache | Memorystore | Self ⚠️ | Self ⚠️ | Heroku Redis |
| PITR (DB backups) | 35 days ✅ | 7 days ✅ | Manual ⚠️ | ❌ | 4 days ✅ |
| Read replicas | ✅ | ✅ | Self ⚠️ | ❌ | ✅ (Premium) |
| Multi-zone HA | Multi-AZ ✅ | Multi-zone ✅ | Manual ⚠️ | ❌ | ✅ |
| VPC / Private network | ✅ | ✅ | ✅ | ❌ | Private Sp. |
| Secrets management | Secrets Mgr | Secret Mgr | Self/Vault | ❌ | Config Vars |
| DevOps complexity | Medium | Medium | High | Low (limited) | Low |
| Compliance (SOC2) | ✅ | ✅ | ⚠️ manual | ❌ | ✅ |
| GDPR data residency | EU regions ✅ | EU regions ✅ | Nuremberg ✅ | EU ✅ | US only ⚠️ |
| Overall rating | 4.6/5 | 4.8/5 ⭐ | 3.1/5 | 1.9/5 | 2.8/5 |
**Final recommendation:**
- 🥇 **Production (funded startup/enterprise): GCP** — best managed services, GKE is easiest Kubernetes, Cloud SQL is excellent
- 🥈 **Production (bootstrapped, EU market): Hetzner + Cloudflare** — 65% cheaper, requires more DevOps skill, great EU GDPR story
- 🥉 **Production (AWS-first company): AWS** — slightly more expensive but massive ecosystem and compliance tooling
- 🚫 **Avoid for Stackhouse production:** Heroku (too expensive), Hostinger (too limited)
---
*Document version: 1.0 — March 2026*
*Stackhouse targeting 100,000 users with Rust middleware + PostgreSQL backend*
*Architecture designed for horizontal scalability to 1M+ users without re-architecture*
---
## Realtime, WebSocket & Object Storage
**URL:** https://www.stackhousedb.com/docs/architecture-deep-dive/realtime-and-storage
**Description:** WebSocket/SSE architecture and S3-compatible object storage.
## 8. Realtime & WebSocket
### WebSocket Architecture at Scale
```mermaid
sequenceDiagram
participant C1 as Client 1
participant P1 as Stackhouse Pod 1
participant R as Redis PubSub
participant P2 as Stackhouse Pod 2
participant C2 as Client 2
participant Admin
C1->>P1: WS Connect
P1->>R: SUBSCRIBE channel: users
C2->>P2: WS Connect
P2->>R: SUBSCRIBE channel: users (same channel)
Admin->>P1: POST /v1/push/users
P1->>P1: Write to PostgreSQL
P1->>R: PUBLISH
R->>P1: Fan-out
R->>P2: Fan-out
P1->>C1: msg via WS
P2->>C2: msg via WS
```
**Key insight:** Redis PubSub decouples pods. Any pod can receive a write, all clients on all pods get notified.
### SSE vs WebSocket Routing
```mermaid
flowchart TD
Q{"Connection type?"}
Q -->|"Long-lived realtime
(dashboard, collaborative)"| WS["WebSocket
Endpoint: /v1/realtime
Sticky routing (IP hash) at LB
Heartbeat: 30s ping/pong"]
Q -->|"One-directional stream
(read-only feed)"| SSE["SSE
Endpoint: /v1/stream/:collection
Works through HTTP/2 multiplexing
Compatible with all HTTP proxies
Auto-reconnect on disconnect"]
```
### Provider WebSocket Support
| Feature | AWS | GCP | Hostinger | Hetzner | Heroku |
|---------|-----|-----|-----------|---------|--------|
| WebSocket at LB | ✅ ALB | ✅ | ✅ | ✅ | ✅ (60s timeout ⚠️) |
| Sticky sessions | ✅ | ✅ | ✅ | ✅ | ❌ (dyno routing) |
| Max WS connections | Unlimited | Unlimited | ~10k | ~10k | ~25k |
| WS timeout (max) | No limit | No limit | 60s ⚠️ | No limit | 55s ⚠️ |
> ⚠️ Heroku and some Hostinger plans have aggressive idle timeouts. Requires ping/pong every 50s.
---
## 9. Storage (File/Object)
### Storage Architecture
```mermaid
flowchart TD
API["Stackhouse Storage API
/v1/storage/*"] --> SS["StorageService (Rust)
Bucket management (metadata in PostgreSQL)
Object metadata (path, size, MIME, owner)
Access control (public vs private buckets)
Path validation (anti-traversal)"]
SS --> S3["AWS S3
(prod)"]
SS --> GCS["GCS Bucket
(prod)"]
SS --> MinIO["MinIO (self)
(Hetzner)"]
```
**Upload flow:** Client → Multipart `POST /v1/storage/object/bucket/path` → Validation (size, MIME, path) → Write to object storage backend → Update PostgreSQL metadata → Return `{ id, path, size, url }`
**Download flow:** Client → `GET /v1/storage/object/bucket/path` → Check auth (public bucket = no auth required) → Serve file bytes with correct Content-Type, OR generate pre-signed URL (redirect to S3/GCS directly)
**Image transforms (planned):** On-demand resize via `/object/bucket/img.jpg?w=200&h=200` → served from CDN after first transform → cached on disk (disk caching + CDN headers)
### Object Storage Comparison
| Feature | AWS S3 | GCS | Cloudflare R2 | MinIO (Hetzner) | Hostinger |
|---------|--------|-----|---------------|-----------------|-----------|
| S3-compatible API | ✅ | Partial | ✅ | ✅ | ❌ |
| Free egress | ❌ | ❌ | ✅ (zero egress) | ✅ (internal) | ❌ |
| Global CDN | CloudFront | Cloud CDN | ✅ included | ❌ | ❌ |
| Storage cost/GB | $0.023 | $0.020 | $0.015 | $0.004 | ~$0.01 |
| Egress cost/GB | $0.09 | $0.12 | $0 | $0 (internal) | ~$0.05 |
| Max object size | 5TB | 5TB | 5TB | Unlimited | ~5GB |
| Versioning | ✅ | ✅ | ✅ | ✅ | ❌ |
| Lifecycle policies | ✅ | ✅ | ✅ | ✅ | ❌ |
---
---
## Indexing
**URL:** https://www.stackhousedb.com/docs/core-features/indexing
**Description:** Secondary indexes and performance
# Indexing
## 📇 Secondary Indexes & Performance
### Creating Indexes
```bash
curl -X POST http://localhost:3000/v1/sql/query \
-H "Content-Type: application/json" \
-d '{"query": "CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users (email)"}'
```
### Index Types
### When to Create Indexes
- ✅ Frequently queried columns
- ✅ Join columns
- ✅ Filter/order by columns
- ❌ Low-cardinality columns (e.g., boolean)
- ❌ Columns updated frequently
### Performance Impact
| Approach | Complexity |
|---|---|
| Without index | O(n) full scan |
| With index | O(log n) index lookup + O(1) row fetch |
**Query speedup:** 10-100x for large datasets
---
**Next:** [Vector Search](/docs/advanced-features/vector-search)
---
## Querying
**URL:** https://www.stackhousedb.com/docs/core-features/querying
**Description:** Query patterns and best practices
# Querying
## 🔍 Query Patterns & Best Practices
Implemented by `query_handler` / `get_by_id_handler` in `stackhouse/src/api/handlers.rs`, routed at `stackhouse/src/api/routes.rs:17-23`.
### REST API Queries
```bash
# Get all documents (default limit: 100, hard cap: 1000)
GET /v1/query/:collection
# Get specific document
GET /v1/query/:collection/:id
# With pagination
GET /v1/query/:collection?limit=100&offset=0
# With ordering — param is `order_dir`, not `order`
GET /v1/query/:collection?order_by=created_at&order_dir=desc
```
Response shape: `{"success": true, "data": [...], "count": N, "collection": "..."}`.
### Filtering
Equality filters are applied **server-side** via query-string parameters — any param that isn't one of `limit`, `offset`, `order_by`, `order_dir` is treated as a `column = value` filter, AND-ed together, with the column name validated as a safe SQL identifier before use:
```bash
# WHERE active = 'true'
GET /v1/query/users?active=true
# WHERE active = 'true' AND role = 'admin'
GET /v1/query/users?active=true&role=admin
```
All filter values are compared as text equality — there is no operator syntax (`>`, `<`, `LIKE`, etc.) via this endpoint. For anything beyond equality, use the raw SQL endpoint below.
```javascript
// Client-side filtering is only needed for logic the query-string API
// doesn't support (e.g. non-equality comparisons):
const users = await fetch('/v1/query/users?active=true')
.then(r => r.json())
.then(data => data.data);
```
### Advanced Patterns
#### Raw SQL access
```bash
POST /v1/sql/query
{"query": "SELECT * FROM users WHERE age > 25"}
```
Gated behind `raw_sql_enabled` (off by default) and admin auth — see [API Reference](/docs/api-reference/api-reference) for current restrictions on this endpoint (destructive-statement filtering, etc).
### Performance Tips
---
**Next:** [Indexing](/docs/core-features/indexing)
---
## Schema Evolution
**URL:** https://www.stackhousedb.com/docs/core-features/schema-evolution
**Description:** Automatic schema evolution via the Schema-Later Guard, backed by PostgreSQL ALTER TABLE
# Schema Evolution
## 🧠 Automatic Schema Evolution
Stackhouse runs on PostgreSQL. Automatic schema evolution is implemented by the **Schema-Later Guard** (`stackhouse/src/security/guard.rs`), which caches known table schemas in a `DashMap`, diffs incoming JSON payload keys against them on every write, and issues `ALTER TABLE ... ADD COLUMN IF NOT EXISTS` for any new fields. Type inference itself lives in `stackhouse/src/inference.rs`.
This is a separate system from the versioned migration service (`stackhouse/src/db/schema_migrations.rs`), which tracks developer-authored `up_sql`/`down_sql` migrations with checksums and rollback — that system does not run automatically on writes.
### How It Works
```mermaid
flowchart TD
A["Input: JSON document"] --> B["Check DashMap cache for known table schema"]
B -->|cache miss| C["Query information_schema to verify live columns"]
C --> D["Diff payload keys against existing columns"]
D --> E["Validate new keys as SQL identifiers (reject reserved words)"]
E --> F["ALTER TABLE ... ADD COLUMN IF NOT EXISTS ..."]
F --> G["Insert data"]
```
A table is capped at `MAX_COLUMNS_PER_TABLE = 1000` columns to prevent "schema bloat" attacks from unbounded payload keys.
### Type Inference
Implemented by `infer_type()` in `stackhouse/src/inference.rs`:
| JSON Type | PostgreSQL Type |
|---|---|
| `string` | `TEXT` |
| `integer` | `BIGINT` |
| `float` | `DOUBLE PRECISION` |
| `boolean` | `BOOLEAN` |
| `array` | `JSONB` |
| `object` | `JSONB` |
| `null` | `NULL` (column skipped; all dynamic columns are nullable) |
When merging schemas across a batch of documents, conflicting types are promoted to a common type via `PgType::common_type()` — e.g. `BIGINT` + `DOUBLE PRECISION` promotes to `DOUBLE PRECISION`; anything mixed with an object/array promotes to `JSONB`; anything promotes to `TEXT` as a fallback.
### Example Evolution
```javascript
// Request 1: {name: "Alice", age: 25}
→ CREATE TABLE users (name TEXT, age BIGINT)
// Request 2: {name: "Bob", email: "bob@..."}
→ ALTER TABLE users ADD COLUMN IF NOT EXISTS email TEXT
// Request 3: {name: "Carol", tags: ["dev", "rust"]}
→ ALTER TABLE users ADD COLUMN IF NOT EXISTS tags JSONB
```
### Benefits
- ✅ Zero downtime
- ✅ No manual migrations for new fields on existing tables
- ✅ Handles any JSON structure
- ✅ Backward compatible (existing columns/rows are untouched)
---
**Next:** [Querying](/docs/core-features/querying)
---
## Storage Engine
**URL:** https://www.stackhousedb.com/docs/core-features/storage-engine
**Description:** Stackhouse stores data in PostgreSQL via sqlx — there is no custom WAL/MemTable/SSTable engine in this codebase.
# Storage Engine
## 💾 Storage Architecture
Stackhouse does not implement its own storage engine (no custom WAL/MemTable/SSTable stack exists in this codebase). It is a Rust/Axum service that stores all data in **PostgreSQL** via `sqlx` — durability, WAL, compaction, MVCC, and caching are all delegated to Postgres itself.
### Architecture Overview
```mermaid
flowchart LR
C["Client"] --> H["Axum handler"] --> G["Schema-Later Guard"] --> P["sqlx (PgPool)"] --> PG["PostgreSQL"]
```
### Components
#### 1. Stackhouse-Store (connection pool)
**Location:** `stackhouse/src/platform/db.rs` (`StackhouseStore`)
A thin wrapper around `sqlx::PgPool` (`PgPoolOptions`, default 20 max connections, 3s acquire timeout). Provides `execute`, `query`, `query_simple`, `insert_returning_id`, `execute_batch`, and simple `insert`/`scan`/`delete` helpers used throughout the API layer. Test isolation uses a fresh `CREATE SCHEMA IF NOT EXISTS stackhouse_test__` per test run, set via `search_path`, not an in-memory database.
#### 2. Schema-Later Guard (automatic schema evolution)
**Location:** `stackhouse/src/security/guard.rs`
Caches known table schemas in a `DashMap`, diffs incoming JSON payload keys against `information_schema` on cache miss, validates new keys as safe SQL identifiers (rejecting reserved keywords), and issues `ALTER TABLE ... ADD COLUMN IF NOT EXISTS` for new fields — capped at 1000 columns per table. See [Schema Evolution](/docs/core-features/schema-evolution) for the full type-inference mapping.
#### 3. Schema migrations (versioned, developer-authored)
**Location:** `stackhouse/src/db/schema_migrations.rs`
A separate system from the Schema-Later Guard above: tracks `up_sql`/`down_sql` migrations with SHA-256 checksums in a `stackhouse_schema_migrations` table, supports `migrate()`, `rollback_one()`, `rollback_to(version)`, and checksum verification. This does not run automatically on writes — it's for developer-driven schema changes.
#### 4. Object/blob storage
**Location:** `stackhouse/src/storage/` (`mod.rs`, `acl.rs`, `cdn.rs`, `lifecycle.rs`, `s3_compat.rs`, `tus.rs`, `versioning.rs`, `explorer.rs`, `scanning.rs`)
A separate subsystem for file storage (buckets, objects, S3-compatible API, resumable uploads via the `tus` protocol, lifecycle policies, ACLs, CDN integration, content scanning). Metadata is tracked in Postgres tables (`stackhouse_buckets`, `stackhouse_objects`) — the backing store is the same Postgres pool used everywhere else. See [Storage](/docs/security-and-ops/storage) for the full API.
### Performance
No throughput/latency numbers are published for this layer — actual performance is governed by the underlying PostgreSQL deployment (instance size, connection pool limits, indexes, network) rather than by any code in this repository.
---
**Next:** [Schema Evolution](/docs/core-features/schema-evolution)
---
## Benchmarks
**URL:** https://www.stackhousedb.com/docs/developer/benchmarks
**Description:** No benchmark suite ships in this repo today — here's how to add one
# Benchmarks
## 📈 Performance Benchmarks
Vector search is delegated to an external Qdrant instance (see [API Reference](/docs/api-reference/api-reference) and [Vector Search](/docs/advanced-features/vector-search)) — Stackhouse does not implement or tune its own HNSW index, so any future vector-search benchmarks here should measure the Qdrant deployment being used, not a component of this codebase.
### Adding Benchmarks
To add real, reproducible benchmarks:
```bash
# Add criterion as a dev-dependency in stackhouse/Cargo.toml, e.g.:
# [dev-dependencies]
# criterion = { version = "0.5", features = ["async_tokio"] }
# [[bench]]
# name = "storage"
# harness = false
cargo bench
```
### Running Custom Benchmarks
```rust
use criterion::{black_box, criterion_group, criterion_main, Criterion};
fn bench_insert(c: &mut Criterion) {
c.bench_function("insert_1k", |b| {
b.iter(|| {
// Insert 1000 items
black_box(db.insert_batch(data))
})
});
}
criterion_group!(benches);
criterion_main!(benches);
```
---
**Documentation Complete!** 🎉
---
## Contributing
**URL:** https://www.stackhousedb.com/docs/developer/contributing
**Description:** Contributing to Stackhouse
# Contributing
## 🤝 Contributing to Stackhouse
Thank you for your interest in contributing!
### Getting Started
```bash
# Fork the repository
# Click "Fork" on GitHub
# Clone your fork
git clone https://github.com/YOUR_USERNAME/stackhouse-stack.git
cd stackhouse-stack/stackhouse
# Add upstream remote
git remote add upstream https://github.com/ArjavDesa912/stackhouse.git
# Create feature branch
git checkout -b feature/your-feature-name
```
### Development Setup
```bash
# Install Rust toolchain
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh
# Build Stackhouse
cargo build --release
# Run tests
cargo test
# Run with logging
RUST_LOG=debug cargo run
```
### Code Style
```bash
# Format code
cargo fmt
# Run linter
cargo clippy -- -D warnings
# Run checks
cargo check --all-features
```
### Making Changes
1. **Create a branch** for each feature
2. **Write tests** for new functionality
3. **Update documentation** if needed
4. **Commit with clear messages**
### Pull Request Process
```bash
# Push to your fork
git push origin feature/your-feature-name
# Create Pull Request on GitHub
# Include:
# - Description of changes
# - Related issues
# - Testing done
```
### Contribution Areas
We welcome contributions in:
- 🐛 Bug fixes
- ✨ New features
- 📚 Documentation
- 🧪 Tests
- ⚡ Performance improvements
- 🌐 Internationalization
### Code Review Process
- All PRs require review
- At least one approval needed
- CI must pass
- Tests must pass
### Getting Help
- 💬 Discord: [discord.gg/stackhouse](https://discord.gg/stackhouse)
- 📧 Email: contributions@stackhouse.dev
- 📖 Docs: See [Documentation Index](/docs)
---
**Ready to contribute?** Start coding! 🚀
---
## Embedded Examples
**URL:** https://www.stackhousedb.com/docs/developer/embedded-examples
**Description:** Practical examples for Stackhouse's read-replica routing and failover system.
# Stackhouse Read Replicas — Usage Examples
This document provides practical examples for using Stackhouse's read-replica routing and failover system (`src/platform/replicas.rs`).
## What this module actually does
`ReplicaService` does **not** replicate data itself. It assumes you already have a Postgres primary and one or more Postgres read replicas set up via your own infrastructure (e.g. cloud-managed streaming replication). Stackhouse's job is to:
1. Keep a registry of known nodes (primary/replica/standby) per tenant, persisted in a `stackhouse_replica_nodes` table.
2. Health-check each node every 30 seconds — **this is a plain TCP connect to `host:port`**, not a Postgres protocol check or a replication-lag query.
3. Round-robin read traffic across healthy replicas via `route_read()`, falling back to the primary if no replica is healthy.
4. Support manual failover (`promote_to_primary`) that flips roles in the registry and records a `FailoverEvent` — it does not reconfigure the underlying Postgres servers for you.
`replication_lag_ms` on a `ReplicaNode` is set once at registration and is **not** automatically updated by the health checker — don't rely on it for real lag monitoring today.
## Library Usage
```rust
use std::sync::Arc;
use stackhouse::db::StackhouseStore;
use stackhouse::platform::replicas::{ReplicaService, NodeRole};
#[tokio::main]
async fn main() -> Result<(), Box> {
let store = Arc::new(StackhouseStore::in_memory().await?); // or your real StackhouseStore
let replicas = ReplicaService::new(store).await?;
let tenant_id = 1_i64;
// Register the primary
replicas.register_node(
tenant_id, "primary", "primary.db.internal", 5432, "postgres", "us-east-1",
NodeRole::Primary,
).await?;
// Register a read replica
replicas.register_node(
tenant_id, "replica-1", "replica1.db.internal", 5432, "postgres", "us-east-1",
NodeRole::Replica,
).await?;
// Route a read — returns a healthy replica, or the primary if none are healthy
let node = replicas.route_read(tenant_id).await?;
println!("Routing read to {}:{}", node.host, node.port);
// Inspect aggregate stats
let stats = replicas.get_stats(tenant_id).await;
println!("{} replicas, avg lag {}ms", stats.replica_count, stats.avg_replication_lag_ms);
Ok(())
}
```
## Mounting the REST API
If you want the HTTP surface, wire it up yourself (this is not done in `src/main.rs` today):
```rust
use stackhouse::platform::replicas::{ReplicaService, ReplicaState, create_replicas_router};
let replica_state = ReplicaState { replicas: Arc::new(replicas), auth: auth_state };
let replicas_router = create_replicas_router(replica_state);
let app = app.nest("/v1/replicas", replicas_router);
```
### Endpoints (once mounted)
| Method | Path | Description |
| --- | --- | --- |
| POST | `/v1/replicas/nodes` | Register a node (`name`, `host`, `port`, `database`, `region`, `role`) |
| GET | `/v1/replicas/nodes` | List nodes for the authenticated tenant |
| POST | `/v1/replicas/nodes/:id/promote` | Promote a node to primary (manual failover) |
| DELETE | `/v1/replicas/nodes/:id` | Remove a node from the registry |
| GET | `/v1/replicas/stats` | Aggregate replication stats for the tenant |
All routes require authentication (`extract_auth_user`); the tenant is taken from the authenticated user's ID.
**Register a node:**
```bash
curl -X POST http://localhost:8080/v1/replicas/nodes \
-H "Authorization: Bearer $TOKEN" \
-H "Content-Type: application/json" \
-d '{
"name": "replica-1",
"host": "replica1.db.internal",
"port": 5432,
"database": "postgres",
"region": "us-east-1",
"role": "replica"
}'
```
**Promote a replica to primary:**
```bash
curl -X POST http://localhost:8080/v1/replicas/nodes//promote \
-H "Authorization: Bearer $TOKEN"
```
**Get stats:**
```bash
curl http://localhost:8080/v1/replicas/stats -H "Authorization: Bearer $TOKEN"
```
Response:
```json
{
"success": true,
"data": {
"primary_id": "...",
"replica_count": 1,
"avg_replication_lag_ms": 0,
"max_replication_lag_ms": 0,
"total_reads_routed": 42,
"reads_to_primary": 5,
"reads_to_replicas": 37
}
}
```
## Summary
This page covers:
- ✅ What `ReplicaService` actually is: a read-replica registry + round-robin read router over externally-provisioned Postgres nodes
- ✅ Its real limitations: TCP-only health checks, no automatic lag measurement, manual (not automatic) failover
- ✅ That it is mounted by the default server binary under `/v1/platform/replicas`
- ✅ REST endpoint examples for the live router
For deeper background on Stackhouse's replication story, see [Replication](/docs/security-and-ops/replication).
---
## Testing
**URL:** https://www.stackhousedb.com/docs/developer/testing
**Description:** Testing guide
# Testing
## 🧪 Testing Guide
### Running Tests
```bash
# Run all tests
cargo test
# Run specific test
cargo test test_basic_operation
# Run with output
cargo test -- --nocapture
# Run tests in parallel
cargo test --release --test-threads=4
```
### Test Structure
Stackhouse is backed by Postgres (via `sqlx`/`StackhouseStore`), not a bespoke WAL/memtable/SSTable storage engine — there is no `stackhouse_core` module. Unit tests live inline as `#[cfg(test)] mod tests` blocks inside individual `src/` files (18 files currently do this); integration/contract tests live in `stackhouse/tests/`:
```
stackhouse/
├── src/
│ └── ... (individual modules with inline #[cfg(test)] mod tests blocks)
└── tests/
├── api_security_regression.rs
├── billing_integration.rs
├── brain_catalog_contract.rs
├── core_api_feature_coverage.rs
├── schema_inference_contract.rs
├── security_hardening.rs
└── source_security_scan.rs
```
### Writing Tests
```rust
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn test_basic_operation() {
// unit-test the function/struct in this module directly
}
}
```
### Integration Tests
Integration tests in `stackhouse/tests/` spin up an in-memory `StackhouseStore` and drive the real Axum router through `tower::ServiceExt::oneshot` (see `stackhouse/tests/core_api_feature_coverage.rs`):
```rust
use std::sync::Arc;
use axum::{body::Body, http::Request};
use tower::ServiceExt;
use stackhouse::{api::{create_router, AppState}, db::StackhouseStore};
#[tokio::test]
async fn test_api_endpoint() {
let store = StackhouseStore::in_memory().await.unwrap();
let app = create_router(AppState::new(Arc::new(store)));
let response = app
.oneshot(Request::builder()
.method("POST")
.uri("/v1/push/test")
.header("content-type", "application/json")
.body(Body::from(serde_json::json!({"name": "Test"}).to_string()))
.unwrap())
.await
.unwrap();
assert_eq!(response.status(), 200);
}
```
Tests that need a real database gracefully skip (print a message and return early) when `StackhouseStore::in_memory()` can't reach a test database, rather than failing.
### Test Coverage
```bash
# Install tarpaulin
cargo install cargo-tarpaulin
# Generate coverage report
cargo tarpaulin --out Html
# View report
open tarpaulin-report/index.html
```
### Benchmarks
See [Benchmarks](/docs/developer/benchmarks) for performance testing.
---
**Done!** 🎉
---
## Architecture
**URL:** https://www.stackhousedb.com/docs/getting-started/architecture
**Description:** Understanding the system architecture
# Architecture
## 🏗️ Stackhouse System Architecture
Understanding how Stackhouse works under the hood.
## Table of Contents
- [High-Level Architecture](#high-level-architecture)
- [Component Overview](#component-overview)
- [Data Flow](#data-flow)
- [Request Processing Flow](#request-processing-flow)
---
> **Correction:** earlier versions of this page described a custom in-process LSM-tree storage engine ("Stackhouse-Core": WAL/MemTable/SSTable/compaction files under `src/stackhouse_core/`). No such module exists in this codebase — it was aspirational/fictional documentation. Stackhouse stores all relational data in **PostgreSQL** via `sqlx` (`stackhouse/src/platform/db.rs`, `StackhouseStore`). The diagrams below have been corrected to reflect the actual implementation.
## High-Level Architecture
```mermaid
flowchart TD
subgraph Client["Client Layer"]
C["HTTP Clients · WebSocket · Web Dashboard"]
end
subgraph API["API Layer (Axum)"]
A["REST API · WebSocket · SSE · Middleware"]
end
subgraph Business["Business Logic Layer"]
B["Auth · Security (RLS) · Schema-Later Guard"]
end
subgraph Data["StackhouseStore (sqlx PgPool)"]
D["PostgreSQL"]
end
Client --> API --> Business --> Data
```
Adjacent services reached over their own APIs, not part of the write/read path above:
- Qdrant (vector search, HTTP)
- boa_engine JS runtime (REST router mounted under `/v1/functions` in `main.rs`)
- Object storage subsystem (buckets/objects in Postgres, S3-compatible API, CDN, tus resumable uploads)
---
## Component Overview
### 1. API Layer
**Location:** `stackhouse/src/api/` (`handlers.rs`, `routes.rs`, `admin.rs`, `dashboard.rs`, `graphql.rs`, `openapi.rs`, `mcp_server.rs`, `auto_rest.rs`, `versioned_api.rs`, `platform.rs`) — not a single `api.rs` file.
Handles all incoming HTTP/WebSocket requests.
```mermaid
flowchart LR
A["Request
Client"] --> B["Router
Axum"] --> C["Middleware
Auth"] --> D["Handlers
Logic"] --> E["Response
JSON"]
```
### 2. Data Storage
**Location:** `stackhouse/src/platform/db.rs` (`StackhouseStore`)
A `sqlx::PgPool`-backed wrapper providing `execute`, `query`, `query_simple`, `insert_returning_id`, and simple `insert`/`scan`/`delete` helpers. See [Storage Engine](/docs/core-features/storage-engine) for the full breakdown, including the separate Schema-Later Guard (auto schema evolution) and versioned migration service.
### 3. Data Flow
#### Write Path
```mermaid
flowchart TD
A["1. Client Request
POST /v1/push/users"] --> B["2. API validation / auth / RLS context injection"]
B --> C["3. Schema-Later Guard: diff payload keys vs. cached schema, ALTER TABLE ADD COLUMN for new fields"]
C --> D["4. INSERT via StackhouseStore (sqlx) → PostgreSQL"]
D --> E["5. Response to client"]
```
#### Read Path
```mermaid
flowchart TD
A["1. Client Request
GET /v1/query/users?..."] --> B["2. API validation / auth / RLS context injection"]
B --> C["3. Build SELECT with WHERE (equality filters from query params), ORDER BY, LIMIT/OFFSET"]
C --> D["4. Execute via StackhouseStore (sqlx) → PostgreSQL"]
D --> E["5. Return Result"]
```
---
## Request Processing Flow
### HTTP Request Flow
```mermaid
flowchart TD
A["Client Request"] --> B["Axum Router"]
B --> C["Middleware Stack
1. CORS · 2. Logging
3. Authentication (JWT)
4. Row-Level Security (policy check)"]
C --> D["Handler (api/handlers.rs)"]
D --> E["Processing
Schema-Later Guard (auto-evolve) · Type inference · Business logic"]
E --> F["StackhouseStore (platform/db.rs)"]
F --> G["PostgreSQL"]
G --> H["Response"]
```
---
## Component Communication
```mermaid
flowchart LR
api["api/handlers.rs"] <-->|JWT validation| auth["auth/*"]
api <-->|RLS + auto-evolve| guard["security/guard.rs"]
api <-->|database ops| db["platform/db.rs"]
api <-->|WebSocket (LISTEN/NOTIFY)| realtime["realtime/mod.rs"]
db <-->|versioned migrations| migrations["db/schema_migrations.rs"]
guard <-->|type inference| inference["inference.rs"]
vectors["storage/vectors.rs"] <-->|HTTP| qdrant["Qdrant"]
functions["compute/functions.rs"] <-->|JS execution — router mounted under /v1/functions| boa["boa_engine"]
```
---
## Concurrency Model
```mermaid
flowchart TD
subgraph Tokio["Tokio Runtime (Async, multi-threaded)"]
subgraph Workers["Worker Threads"]
direction LR
T1 & T2 & T3 & T4
end
Workers --> TS["Task Scheduling"]
end
```
Concurrency-relevant components:
- **Postgres connection pool:** `sqlx::PgPool` (default 20 max connections, 3s acquire timeout)
- **Schema cache:** `DashMap` (`security/guard.rs`)
- **Realtime fan-out:** `DashMap` of `tokio::sync::broadcast` channels, one per subscribed table
---
## Key Design Decisions
### 1. Why PostgreSQL, Not a Custom Engine
Stackhouse deliberately does not implement its own storage engine. Data durability, MVCC, indexing, and query execution are delegated entirely to PostgreSQL; Stackhouse's own code is the "schema-later" layer on top — automatic `ALTER TABLE` on new JSON fields, RLS policy management, and the REST/GraphQL/WebSocket surface — rather than a database kernel.
### 2. Async Architecture
**Why Tokio Async?**
- High concurrency without threads
- Efficient I/O operations
- Better resource utilization
- Scalable to thousands of connections
---
## Extension Points
Real, verified extension points in the current codebase:
| Extension point | Status |
|---|---|
| Enterprise Connectors | ~89 connectors under `stackhouse/src/connectors/` (Slack, Zendesk, Five9, NetSuite, Splunk, ...) — most make real outbound HTTP calls |
| Row-Level Security Policies | Defined per-table via the RLS API (`/v1/rls`), enforced by `security/guard.rs` on each request |
| JavaScript Functions | ⚠️ Implemented but **not currently reachable** — `compute/functions.rs` implements deploy/invoke via `boa_engine` and defines `create_functions_router()`, but that router is never nested into the app in `main.rs`. Wiring it up (a one-line `.nest(...)` in `main.rs`) is a prerequisite for this to work end-to-end. |
There is no `StorageEngine`/`AuthService` trait or `PolicyEngine` type in this codebase — those were aspirational claims in an earlier version of this page, not real extension mechanisms.
---
## Performance Characteristics
No per-layer latency numbers are published here — actual performance is governed by the underlying PostgreSQL deployment and, for vector search, by the external Qdrant deployment, not by fixed constants in Stackhouse's own code. Measure against your own deployment rather than relying on any previously quoted figures on this page.
---
## Next Steps
To dive deeper into specific components:
- [Storage Engine](/docs/core-features/storage-engine) - PostgreSQL-backed storage, schema evolution, object storage
- [Schema Evolution](/docs/core-features/schema-evolution) - Auto-schema magic
- [Vector Search](/docs/advanced-features/vector-search) - AI features (Qdrant-backed)
- [JavaScript Functions](/docs/advanced-features/functions) - Serverless compute
---
**Continue to [Quick Start](/docs/getting-started/quick-start) or back to [Index](/docs)** 🚀
---
## Introduction to Stackhouse
**URL:** https://www.stackhousedb.com/docs/getting-started/introduction
**Description:** What is Stackhouse and why should you use it?
# Introduction to Stackhouse
## 🎯 What is Stackhouse?
> Stackhouse is a next-generation database that evolves with your application, not against it. Schema-later, AI-native, and realtime.
**Stackhouse** is an open-source, AI-native database that combines the flexibility of NoSQL with the power of SQL, vector search, and serverless compute—all in one unified system.
### 📊 The Problem Stackhouse Solves
| Pain point | Why it hurts |
|---|---|
| **Migrations** | Schema locks, downtime required |
| **Vector Search** | Separate service, high latency/cost |
| **Serverless Compute** | Cloud functions only, vendor lock-in |
| **Realtime Updates** | Multiple tools, complex setup |
**Stackhouse eliminates all of this.**
### ✨ The Stackhouse Solution
```mermaid
flowchart TD
D["Data
✅ JSON · ✅ SQL · ✅ Auto-Relations"]
V["Vectors
✅ HNSW · ✅ Semantic · ✅ Fast"]
F["Functions
✅ Boa · ✅ Custom · ✅ Sandboxed"]
D & V & F --> API["Stackhouse Unified API"]
API --> R["Realtime + Secure + Fast"]
```
---
## 🌟 Core Philosophy
### 1️⃣ Schema-Later™
Don't plan your schema upfront. Let it evolve naturally.
**Visual Example:**
### 2️⃣ AI-Native
Vector search is a first-class, unified API — not a bolt-on you have to wire up yourself — even though under the hood it proxies to a dedicated Qdrant instance rather than an in-process index (see [Vector Search](/docs/advanced-features/vector-search)).
```mermaid
flowchart LR
subgraph Traditional["Traditional Approach — 3 separate services, high latency/cost"]
direction LR
T1["Database"] --> T2["Export Data"] --> T3["Pinecone /
Weaviate"] --> T4["Query"]
end
subgraph StackhouseDBFlow["Stackhouse Approach — one service, low latency/cost"]
direction LR
V1["Database
+Vectors"] --> V2["Stackhouse
Built-in"] --> V3["Results!"]
end
```
**Semantic Search Example:**
```mermaid
flowchart TD
Q["Query: "How do I reset my password?""] --> E["Embedding Model"]
E --> V["[0.23, -0.45, 0.67, ...]"]
V --> I["Stackhouse Vector API
HNSW Algorithm · O(log n) Search
Reached via Stackhouse's REST API, backed by an external Qdrant instance"]
I --> R["Top Results:
1. Reset Password Guide (96%)
2. Account Recovery (89%)
3. Login Issues (76%)"]
```
### 3️⃣ Realtime 2.0
Bi-directional WebSocket communication, not just server-sent events.
```mermaid
sequenceDiagram
participant Client
participant Stackhouse
Client->>Stackhouse: WebSocket Connection (GET /v1/realtime)
Client->>Stackhouse: {"type":"subscribe","table":"users","event":"*"}
Client->>Stackhouse: {"type":"subscribe","table":"docs","event":"INSERT"}
Stackhouse-->>Client: {"type":"INSERT","table":"users","record":{...}}
Stackhouse-->>Client: {"type":"INSERT","table":"docs","record":{...}}
```
Benefits:
- ✅ Bidirectional subscribe/unsubscribe control messages
- ✅ Multiple table subscriptions per connection
- ✅ Server-initiated push on INSERT/UPDATE/DELETE
Note: this channel is for table-change subscriptions only — there is no arbitrary SQL query-over-WebSocket capability; use the REST query/SQL endpoints for that.
---
## 🏗️ Architecture Overview
```mermaid
flowchart TD
App["Application Layer
REST API · WebSocket API · SSE · Web Dashboard"]
Sec["Security Layer
JWT Auth · Row-Level Security · API Keys"]
subgraph Core["Core Layer"]
direction LR
E1["Schema-Later Guard
Auto ALTER TABLE
Type inference"]
E2["Vector Search
Qdrant-backed HNSW/ANN
(external service)"]
E3["Boa Engine
JS/TS exec · Not yet wired to a route"]
end
Storage["Storage Layer
PostgreSQL (sqlx) · Object Storage · Replication"]
App --> Sec --> Core --> Storage
```
> All data lives in **PostgreSQL** — Stackhouse does not implement its own storage engine. See [Architecture](/docs/getting-started/architecture) and [Storage Engine](/docs/core-features/storage-engine) for the verified breakdown. The JavaScript function runtime (`compute/functions.rs`, powered by `boa_engine`) is implemented and its HTTP router is mounted under `/v1/functions` in `main.rs`.
---
## 🎯 Key Features Deep Dive
### 1. Automatic Schema Evolution
Initial state: empty database — no tables, no schema.
**Result:** zero downtime, zero manual migrations!
### 2. PostgreSQL-Backed Storage
Stackhouse does not implement its own storage engine (no custom WAL/MemTable/SSTable stack exists in this codebase). All data is stored in **PostgreSQL** via `sqlx` — durability, WAL, compaction, MVCC, and caching are all delegated to Postgres itself.
```mermaid
flowchart LR
C["Client"] --> H["Axum handler"] --> G["Schema-Later Guard"] --> P["sqlx (PgPool)"] --> PG["PostgreSQL"]
```
See [Storage Engine](/docs/core-features/storage-engine) for the full breakdown.
### 3. Vector Similarity Search
Distance metrics:
- **Cosine Similarity** (default) — semantic similarity
- **Euclidean Distance** — geometric distance
Use cases:
- ✅ Semantic search
- ✅ Recommendation systems
- ✅ Document similarity
- ✅ Image search (via vision embeddings)
- ✅ Duplicate detection
---
## 💪 Performance Characteristics
No throughput/latency numbers are published — actual performance is governed by the underlying PostgreSQL deployment (instance size, connection pool limits, indexes, network) and, for vector search, by the external Qdrant deployment, rather than by any fixed constant in Stackhouse's own code. There is no bundled benchmark suite in this repo today (see [Benchmarks](/docs/developer/benchmarks)); measure against your own deployment before relying on any comparative number.
---
## 🎓 When to Use Stackhouse?
### ✅ Perfect For:
1. **Rapid Prototyping**
- Changing requirements? No problem.
- Unknown data model? Start anyway.
- Quick iterations? Native workflow.
2. **AI/ML Applications**
- Semantic search
- RAG (Retrieval Augmented Generation)
- Recommendation engines
- Similarity matching
3. **Realtime Features**
- Live dashboards
- Collaborative apps
- Notifications
- Gaming leaderboards
4. **Serverless Compute**
- Custom business logic
- Data transformations
- Edge computing
- Cost reduction
### ⚠️ Consider Alternatives For:
1. **Legacy SQL Migrations**
- If you have strict migration requirements
- Consider: Postgres, MySQL
2. **Massive Analytics**
- Petabyte-scale data warehousing
- Consider: Snowflake, BigQuery
3. **Distributed Transactions**
- Multi-region ACID transactions
- Consider: CockroachDB, Spanner
---
## 🚀 What's Next?
Continue your journey:
- **[Quick Start Guide](/docs/getting-started/quick-start)** - Get Stackhouse running in 5 minutes
- **[Architecture Deep Dive](/docs/getting-started/architecture)** - Understand how it works
- **[API Reference](/docs/api-reference/api-reference)** - Explore the API
---
**Ready to start?** Continue to [Quick Start](/docs/getting-started/quick-start) 🚀
---
## Quick Start Guide
**URL:** https://www.stackhousedb.com/docs/getting-started/quick-start
**Description:** Get Stackhouse running in 5 minutes
# Quick Start Guide
## 🚀 Get Stackhouse Running in 5 Minutes
> From zero to Stackhouse in just 5 minutes!
## 📋 Prerequisites
Stackhouse is a Rust/Axum service backed by **PostgreSQL**, with **Qdrant** required only for vector-search features. Before you begin, ensure you have:
**Mandatory:**
- ☑ Rust toolchain (1.70+) — install from [rustup.rs](https://rustup.rs/)
- ☑ Git — install from [git-scm.com](https://git-scm.com/)
- ☑ A running PostgreSQL instance — Stackhouse stores all data here (see `docker-compose.yml`)
**Optional:**
- ☐ Docker + docker-compose (easiest way to get Postgres and Qdrant running together)
- ☐ Qdrant (only needed for `/v1/vectors/*` endpoints)
- ☐ curl or Postman (for API testing)
A JWT secret (`STACKHOUSE_JWT_SECRET` / `--jwt-secret`) is required to run the server — there is no default.
---
## 🎯 Installation Options
---
## 🏃 Quick Start
### Step 1: Start the Server
The CLI is subcommand-based (`stackhouse `) — there's no bare `stackhouse` that starts a server. To start the API server, use `stackhouse serve`:
```bash
# Start with default settings (port 3000, requires STACKHOUSE_URL and
# STACKHOUSE_JWT_SECRET to be set, e.g. via env vars or a .env file)
stackhouse serve
# Or customize
stackhouse serve \
--port 8080 \
--host 0.0.0.0 \
--jwt-secret some-long-dev-secret
```
Real flags on `stackhouse serve` (`stackhouse/src/cli/mod.rs`, `ServeArgs`): `--port` (default 3000), `--host` (default `0.0.0.0`), `--memory` (use an isolated test schema), `--jwt-secret`, `--storage-path`. There is no `--db` or `--log-level` flag — use `STACKHOUSE_URL` for the Postgres connection string and the standard `RUST_LOG` env var for log verbosity.
**On successful start, the server logs (approximately):**
```
✨ Stackhouse initialized successfully with PostgreSQL
⚡ Initializing Realtime Engine...
```
Available once running:
- API: `http://localhost:3000` (or your `--port`)
- Explorer dashboard: `http://localhost:3000/explore`
- WebSocket: `ws://localhost:3000/v1/realtime`
### Step 2: Verify Installation
```bash
curl http://localhost:3000/health
```
**Response:**
```json
{
"status": "healthy",
"database": "connected"
}
```
### Step 3: Open the Dashboard
Open browser: http://localhost:3000/explore
**You'll see the Stackhouse Explorer:**
💡 Tip: Use the API or dashboard to insert data.
---
## 🎨 Your First Queries
### Example 1: Auto-Schema Creation
```bash
# Insert your first document
curl -X POST http://localhost:3000/v1/push/users \
-H "Content-Type: application/json" \
-d '{
"name": "Alice Johnson",
"email": "alice@example.com",
"age": 28
}'
```
**What happens behind the scenes:**
1. Stackhouse receives the JSON.
2. Analyzes the structure: `name: String → TEXT`, `email: String → TEXT`, `age: Number → BIGINT`.
3. Creates the table (id/timestamps), then adds columns:
```sql
CREATE TABLE IF NOT EXISTS users (
id BIGSERIAL PRIMARY KEY,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE users ADD COLUMN IF NOT EXISTS name TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS email TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS age BIGINT;
```
4. Inserts the data.
5. Returns success.
**Response:**
```json
{
"success": true,
"data": {
"id": 1,
"name": "Alice Johnson",
"email": "alice@example.com",
"age": 28,
"created_at": "2025-01-03T12:00:00Z"
}
}
```
### Example 2: Schema Evolution
```bash
# Insert data with new fields
curl -X POST http://localhost:3000/v1/push/users \
-H "Content-Type: application/json" \
-d '{
"name": "Bob Smith",
"age": 35,
"city": "San Francisco",
"skills": ["Rust", "Python", "JavaScript"]
}'
```
Detected new fields: `city: String → TEXT` (added), `skills: Array → JSONB` (added).
Automatic migration:
```sql
ALTER TABLE users ADD COLUMN IF NOT EXISTS city TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS skills JSONB;
```
⚡ Zero downtime required.
### Example 3: Query Data
```bash
# Get all users
curl http://localhost:3000/v1/query/users
# Get specific user
curl http://localhost:3000/v1/query/users/1
```
**Response:**
```json
{
"success": true,
"data": [
{
"id": 1,
"name": "Alice Johnson",
"email": "alice@example.com",
"age": 28,
"created_at": "2025-01-03T12:00:00Z"
},
{
"id": 2,
"name": "Bob Smith",
"age": 35,
"city": "San Francisco",
"skills": ["Rust", "Python", "JavaScript"],
"created_at": "2025-01-03T12:01:00Z"
}
]
}
```
---
## 🧪 Advanced Quick Start
### Vector Search Setup
Requires a reachable Qdrant instance (`QDRANT_URL`, see `docker-compose.yml`) — Stackhouse proxies these requests to Qdrant, it doesn't index vectors itself.
```bash
# 1. Insert (upsert) a vector
curl -X POST http://localhost:3000/v1/vectors/documents/upsert \
-H "Content-Type: application/json" \
-d '{
"id": "doc1",
"embedding": [0.1, 0.2, 0.3, 0.4, 0.5],
"data": {
"title": "Introduction to Stackhouse",
"category": "database"
}
}'
# 2. Insert more vectors
curl -X POST http://localhost:3000/v1/vectors/documents/upsert \
-H "Content-Type: application/json" \
-d '{
"id": "doc2",
"embedding": [0.15, 0.25, 0.35, 0.45, 0.55],
"data": {
"title": "Advanced Stackhouse Features",
"category": "database"
}
}'
# 3. Search for similar vectors
curl -X POST http://localhost:3000/v1/vectors/documents/search \
-H "Content-Type: application/json" \
-d '{
"vector": [0.12, 0.22, 0.32, 0.42, 0.52],
"top_k": 5
}'
```
**Response:**
```json
{
"success": true,
"count": 2,
"collection": "documents",
"metric": "cosine",
"data": [
{
"id": "doc2",
"similarity": 0.98,
"data": {
"title": "Advanced Stackhouse Features",
"category": "database"
}
},
{
"id": "doc1",
"similarity": 0.95,
"data": {
"title": "Introduction to Stackhouse",
"category": "database"
}
}
]
}
```
### WebSocket Realtime Connection
```javascript
// Connect to Stackhouse WebSocket
const ws = new WebSocket('ws://localhost:3000/v1/realtime');
// Connection opened
ws.addEventListener('open', () => {
console.log('✅ Connected to Stackhouse');
// Subscribe to a table (real protocol: lowercase "subscribe", "table", "event")
ws.send(JSON.stringify({
type: 'subscribe',
table: 'users',
event: '*'
}));
});
// Listen for messages
ws.addEventListener('message', (event) => {
const msg = JSON.parse(event.data);
// Real event types are "INSERT" / "UPDATE" / "DELETE"
if (msg.type === 'INSERT') {
console.log('📨 New row:', msg.table, msg.record);
// Example output:
// 📨 New row: users {
// id: 3,
// name: "Charlie",
// ...
// }
}
});
// Now try inserting data from another terminal:
// curl -X POST http://localhost:3000/v1/push/users \
// -H "Content-Type: application/json" \
// -d '{"name": "Charlie", "age": 30}'
//
// You'll see it appear instantly in the WebSocket!
```
---
## 📊 Quick Reference Commands
Verified against `stackhouse/src/api/routes.rs` and `stackhouse/src/storage/vectors.rs`.
### Data Operations
```bash
# Insert data
POST /v1/push/:collection
POST /v1/push/:collection/batch
# Query data (equality filters via query params, limit/offset/order_dir)
GET /v1/query/:collection
GET /v1/query/:collection/:id
# Update data
POST /v1/update/:collection # bulk
# Delete data
POST /v1/delete/:collection # bulk
# Raw SQL (admin-gated, off by default)
POST /v1/sql/query
POST /v1/sql/execute
```
### Schema & Metadata
```bash
# List all tables
GET /v1/tables
# Get table stats / drop a table
GET /v1/tables/:collection
DELETE /v1/tables/:collection
```
There is currently **no REST endpoint for creating an index** — see [Indexing](/docs/core-features/indexing).
### Vector Search (proxied to Qdrant)
```bash
POST /v1/vectors/:collection/upsert
POST /v1/vectors/:collection/batch
POST /v1/vectors/:collection/search
GET /v1/vectors/:collection/info
```
There is no list-all-collections or delete-by-id vector endpoint.
### JavaScript Functions — implemented, NOT currently reachable
```bash
# These handlers and their router exist in compute/functions.rs, but the
# router is never mounted in main.rs, so none of these are live today:
POST /v1/functions/deploy
POST /v1/functions/invoke/:name
GET /v1/functions
```
See [JavaScript Functions](/docs/advanced-features/functions) for current status before relying on this.
### Realtime
```bash
# WebSocket connection (subscribe/unsubscribe protocol, see above)
WS /v1/realtime
# SSE stream per collection
GET /v1/stream/:collection
```
---
## 🎯 Common Use Cases
### Use Case 1: REST API Backend
```javascript
// server.js
const express = require('express');
const axios = require('axios');
const app = express();
app.use(express.json());
// Create user
app.post('/users', async (req, res) => {
const response = await axios.post(
'http://localhost:3000/v1/push/users',
req.body
);
res.json(response.data);
});
// Get user
app.get('/users/:id', async (req, res) => {
const response = await axios.get(
`http://localhost:3000/v1/query/users/${req.params.id}`
);
res.json(response.data);
});
app.listen(4000);
```
### Use Case 2: Python Client
```python
# client.py
STACKHOUSE_URL = "http://localhost:8080"
# Insert data
response = requests.post(
f"{STACKHOUSE_URL}/v1/push/products",
json={
"name": "Laptop",
"price": 999.99,
"in_stock": True
}
)
print(response.json())
# Query data
response = requests.get(f"{STACKHOUSE_URL}/v1/query/products")
print(response.json())
```
### Use Case 3: Realtime Dashboard
```html
Stackhouse Dashboard
Live Users
```
---
## 🔍 Troubleshooting
### Problem: Port already in use
```bash
# Use a different port
stackhouse serve --port 8080
# Or find and kill the process
# On Linux/Mac:
lsof -ti:3000 | xargs kill -9
# On Windows:
netstat -ano | findstr :3000
taskkill /PID /F
```
### Problem: Server won't start (missing config)
`stackhouse serve` requires a reachable `STACKHOUSE_URL` (Postgres connection string) and `STACKHOUSE_JWT_SECRET`. There is no local file-based storage mode — even `--memory` uses an isolated Postgres schema, not an embedded/on-disk file.
```bash
STACKHOUSE_URL=postgres://postgres:password@localhost:5432/stackhouse \
STACKHOUSE_JWT_SECRET=some-long-dev-secret \
stackhouse serve
```
### Problem: Can't connect
```bash
# Verify server is running
curl http://localhost:3000/health
# Check firewall settings
# Try connecting with telnet
telnet localhost 3000
```
Log level is currently fixed at `INFO` in `main.rs` (`FmtSubscriber::builder().with_max_level(Level::INFO)`) — there is no `--log-level` flag or `RUST_LOG`-driven verbosity today, despite what older docs may say.
---
## 📚 Next Steps
Now that you have Stackhouse running:
1. **Explore the Dashboard**
- Visit http://localhost:3000/explore
- Try the visual query builder
- Monitor real-time stats
2. **Build Something**
- [Architecture Guide](/docs/getting-started/architecture) - Understand the system
- [Schema Evolution](/docs/core-features/schema-evolution) - Master auto-schema
- [Vector Search](/docs/advanced-features/vector-search) - Add AI capabilities
3. **Go Production**
- [Deployment Guide](/docs/production/deployment) - Deploy to production
- [Performance Tuning](/docs/production/performance) - Optimize your setup
- [Monitoring](/docs/production/monitoring) - Set up observability
---
## 💡 Pro Tips
### Tip 1: Use Environment Variables
Real env vars (`stackhouse/src/cli/mod.rs` `ServeArgs`, `stackhouse/src/main.rs`): `STACKHOUSE_URL`, `STACKHOUSE_PORT`, `STACKHOUSE_HOST`, `STACKHOUSE_MEMORY`, `STACKHOUSE_JWT_SECRET`, `STACKHOUSE_STORAGE_PATH`, `STACKHOUSE_CORS_ALLOWED_ORIGINS`. There is no `STACKHOUSE_DB_PATH` or `STACKHOUSE_LOG_LEVEL`.
```bash
# .env file
STACKHOUSE_URL=postgres://postgres:password@localhost:5432/stackhouse
STACKHOUSE_PORT=3000
STACKHOUSE_JWT_SECRET=some-long-dev-secret
# Load and run
export $(cat .env | xargs)
stackhouse serve
```
### Tip 2: CORS Is Opt-In, Not On By Default
```bash
# There is no CORS-related CLI flag — set the allowlist via env var.
# An empty/unset allowlist means cross-origin requests are rejected.
STACKHOUSE_CORS_ALLOWED_ORIGINS="http://localhost:8080,https://example.com" \
stackhouse serve
```
### Tip 3: Use Batch Inserts
```bash
# Faster than individual inserts
curl -X POST http://localhost:3000/v1/push/users/batch \
-H "Content-Type: application/json" \
-d '[
{"name": "User 1", "age": 25},
{"name": "User 2", "age": 30},
{"name": "User 3", "age": 35}
]'
```
### Tip 4: Monitor Performance
```bash
# Check table stats
curl http://localhost:3000/v1/tables/users
# Response includes:
# - Row count
# - Size on disk
# - Indexes
# - Last update time
```
---
**Congratulations!** 🎉 You now have Stackhouse running and ready to use.
**Next:** Learn about the [Architecture](/docs/getting-started/architecture) to understand how everything works under the hood.
---
## Authentication
**URL:** https://www.stackhousedb.com/docs/security-and-ops/authentication
**Description:** JWT-based authentication
# Authentication
## 🔐 JWT-Based Authentication
### Sign Up
```bash
curl -X POST http://localhost:3000/v1/auth/signup \
-H "Content-Type: application/json" \
-d '{
"email": "user@example.com",
"password": "secure_password"
}'
```
**Response:**
```json
{
"success": true,
"data": {
"user": {"id": 1, "email": "user@example.com"},
"token": "eyJhbGc...",
"refresh_token": "eyJhbGc..."
}
}
```
### Login
```bash
curl -X POST http://localhost:3000/v1/auth/login \
-H "Content-Type: application/json" \
-d '{
"email": "user@example.com",
"password": "secure_password"
}'
```
### Using Tokens
```bash
# Include token in Authorization header
curl http://localhost:3000/v1/query/users \
-H "Authorization: Bearer "
```
### Token Lifecycle
```mermaid
flowchart TD
A["1. User logs in"] --> B["Access Token (1 hour)
+ Refresh Token (7 days)"]
B -->|"Access token expires"| C["Use refresh token"]
C -->|"Refresh token expires"| D["Re-login required"]
```
### Security Features
- ✅ Argon2id password hashing
- ✅ JWT token validation
- ✅ Automatic token expiration
- ✅ Refresh token rotation (each `/v1/auth/refresh` call deletes the old session and issues a new refresh token)
- ✅ Access-token blacklisting on logout (revoked JWT `jti`s are rejected even before natural expiry)
### Other Account Endpoints
```
GET /v1/auth/me # Current user profile
PUT /v1/auth/user # Update profile
POST /v1/auth/change-password # Change password
GET /v1/auth/sessions # List active sessions (refresh tokens)
DELETE /v1/auth/sessions/:id # Revoke a specific session
```
## 🔑 Additional Sign-In Methods
Beyond email/password, the following are implemented and mounted under `/v1/auth`:
**OAuth / social login** — Google, GitHub, Discord, and Apple:
```
GET /v1/auth/providers # List configured OAuth providers
GET /v1/auth/authorize/:provider # Start OAuth flow
GET /v1/auth/callback/:provider # OAuth callback
GET /v1/auth/accounts # List linked OAuth accounts (authenticated)
DELETE /v1/auth/accounts/:provider # Unlink an OAuth account
```
**Magic link (passwordless email):**
```
POST /v1/auth/magic-link # Request a magic link
GET /v1/auth/magic-link/verify # Verify magic link token
```
**Multi-factor authentication (TOTP + recovery codes):**
```
POST /v1/auth/mfa/enroll # Start MFA enrollment
POST /v1/auth/mfa/verify # Verify enrollment with a TOTP code
POST /v1/auth/mfa/challenge # Verify a TOTP code during login
POST /v1/auth/mfa/recovery # Use a recovery code
DELETE /v1/auth/mfa # Disable MFA
GET /v1/auth/mfa/status # Get MFA status
```
**Phone OTP:**
```
POST /v1/auth/phone/send # Send a one-time code via SMS
POST /v1/auth/phone/verify # Verify the code
```
**CAPTCHA:**
```
GET /v1/auth/captcha # Get captcha configuration
```
---
**Next:** [Row-Level Security](/docs/security-and-ops/row-level-security)
---
## Replication
**URL:** https://www.stackhousedb.com/docs/security-and-ops/replication
**Description:** Read-replica registry, routing, and failover — built on Postgres, not a custom WAL engine
# Replication
## Overview
Stackhouse relies on Postgres's own physical/streaming replication to keep replica nodes in sync; this layer tracks which nodes exist, routes reads across them, and orchestrates promoting a replica to primary on failover.
## Data Model
```rust
pub struct ReplicaNode {
pub id: String,
pub tenant_id: i64,
pub name: String,
pub host: String,
pub port: u16,
pub database: String,
pub region: String,
pub role: NodeRole, // Primary | Replica | Standby
pub status: NodeStatus, // Healthy | Degraded | Unhealthy | Offline | Promoting
pub replication_lag_ms: u64,
pub connections_active: u32,
pub connections_max: u32,
pub last_health_check: Option
## Related: Change Data Capture
Trigger + `NOTIFY`-based CDC (`stackhouse/src/platform/cdc.rs`) and PITR backup/restore (`stackhouse/src/storage/backups/pitr.rs`) are separate subsystems documented in their own sections — see [Backup & Recovery](/docs/production/backup-recovery).
---
**Next:** [Billing](/docs/stackhouse-billing/overview)
---
## Row-Level Security
**URL:** https://www.stackhousedb.com/docs/security-and-ops/row-level-security
**Description:** Fine-grained access control
# Row-Level Security
## 🔒 Fine-Grained Access Control with RLS
### What is RLS?
Row-Level Security (RLS) allows you to control which rows users can access based on policies.
RLS is implemented on top of PostgreSQL's native row security (`ALTER TABLE ... ENABLE/FORCE ROW LEVEL SECURITY` + `CREATE POLICY`), managed via a REST API rather than raw SQL. The current authenticated user's JWT claims are made available to policy expressions through `current_setting('request.jwt.claims', true)::json->>''` — there is no `auth.uid()`/`auth.role()` helper function.
### Creating Policies
```
POST /v1/rls/:table/enable # ALTER TABLE ... ENABLE/FORCE ROW LEVEL SECURITY
POST /v1/rls/:table/policies # Create a policy
GET /v1/rls/:table/policies # List policies on a table
DELETE /v1/rls/:table/policies/:name # Drop a policy
GET /v1/rls/:table/status # Whether RLS is enabled on a table
GET /v1/rls/audit # Audit log of RLS changes
POST /v1/rls/:table/disable # ALTER TABLE ... DISABLE ROW LEVEL SECURITY
```
```bash
# Example: Users can only see their own data
curl -X POST http://localhost:3000/v1/rls/documents/policies \
-H "Authorization: Bearer " \
-H "Content-Type: application/json" \
-d '{
"name": "user_isolation",
"operation": "SELECT",
"using_expr": "owner_id = (current_setting(\'request.jwt.claims\', true)::json->>\'user_id\')::bigint",
"permissive": true
}'
```
`operation` is one of `ALL` (default), `SELECT`, `INSERT`, `UPDATE`, `DELETE`. `using_expr` controls which existing rows are visible/affected; `check_expr` (optional) constrains rows being inserted/updated. Both expressions are validated against a SQL-injection allowlist (`SchemaGuard::validate_sql_expression`) before being interpolated into the generated `CREATE POLICY` statement. `permissive` (default `true`) selects `PERMISSIVE` vs `RESTRICTIVE` policy semantics, matching Postgres's own policy combination rules.
### Policy Evaluation
```mermaid
flowchart TD
A["Request arrives"] --> B["Extract user info from JWT"]
B --> C["Check applicable policies"]
C --> D["Filter rows based on policy rules"]
D --> E["Return only authorized rows"]
```
### Example Policies
```javascript
// Policy 1: Users see own data — POST /v1/rls/documents/policies
{
"name": "user_isolation",
"operation": "SELECT",
"using_expr": "owner_id = (current_setting('request.jwt.claims', true)::json->>'user_id')::bigint"
}
// Policy 2: Public documents visible to all — POST /v1/rls/documents/policies
{
"name": "public_docs",
"operation": "SELECT",
"using_expr": "is_public = true"
}
// Policy 3: Editors can modify — POST /v1/rls/documents/policies
{
"name": "editor_access",
"operation": "UPDATE",
"using_expr": "current_setting('request.jwt.claims', true)::json->>'role' IN ('editor', 'admin')"
}
```
### Best Practices
1. **Start restrictive** - Deny all, then allow specific
2. **Test policies** - Use test mode to verify
3. **Log denials** - Monitor unauthorized access attempts
4. **Keep it simple** - Complex policies are hard to debug
---
**Next:** [Storage](/docs/security-and-ops/storage)
---
## Stackhouse Security Documentation
**URL:** https://www.stackhousedb.com/docs/security-and-ops/security
**Description:** This document describes the current technical security controls on this branch and the deployment assumptions they rely on. It is not a full security program and it is not a compli
# Stackhouse Security Documentation
## Overview
This document describes the current technical security controls on this branch and the deployment assumptions they rely on. It is not a full security program and it is not a compliance statement.
## Implemented Controls
### Privileged access control
The current branch requires `service_admin` access on the covered admin surfaces, including branching, extensions, network, backup, log-drain, raw-SQL, and destructive admin routes. Raw SQL and destructive admin capabilities remain disabled by default unless an operator intentionally enables them.
### Audit evidence on covered admin surfaces
The readiness pass adds route-level admin audit entries for the privileged surfaces covered on this branch. These entries record actor, action, target or target type, outcome, and route metadata for the touched handlers.
### Team-scoped authorization
Membership and invitation routes now enforce team-scoped authorization so those actions stay within the owning team context.
### Protected authentication secrets
MFA TOTP secrets are encrypted before persistence. This protects the stored TOTP secret material only; it does not mean all product data is encrypted at rest.
### Protected backup artifacts
Backup artifacts are encrypted at rest before they are written to disk or storage.
### Safer runtime defaults
Cycle 1 also tightens default deployment posture:
- CORS uses an explicit allowlist.
- Helm/runtime defaults keep risky capabilities disabled unless they are deliberately enabled.
- The repo no longer treats raw SQL or destructive admin operations as safe-by-default capabilities.
## Safe Deployment Notes
- Configure an explicit CORS allowlist before exposing the service.
- Provide the encryption key material required by the MFA TOTP and backup workflows.
- Keep raw SQL and destructive admin capabilities disabled unless a trusted operator explicitly needs them.
- Review service-admin assignment carefully because it gates privileged operations.
- Treat the repo as a technical control baseline, not as evidence of SOC 2, ISO 27001, or FedRAMP readiness on its own.
- Use `docs/audit-readiness/` for the branch-specific evidence map, gaps, and readiness checklist.
## Not Implemented In Cycle 1
These items are intentionally deferred and should not be inferred from the current codebase or this document:
- Enforced MFA for every account.
- HTTP hardening headers.
- Payload size caps.
- Outbound request protections.
- Blanket OWASP Top 10 coverage.
- FIPS validation.
- Blanket encryption-at-rest claims for all product data.
- Organizational evidence and operating-effectiveness claims.
## Review Checklist
When auditing the Cycle 1 hardening work, verify:
- Service-admin authorization is present on the currently covered admin surfaces.
- Route-level admin audit entries are present on the covered privileged handlers.
- Raw SQL and destructive admin capabilities stay disabled by default.
- TOTP secrets are encrypted before storage.
- Backup artifacts are encrypted before storage.
- Membership and invitation routes are team-scoped.
- CORS is configured with an explicit allowlist.
- Remaining readiness gaps are tracked in `docs/audit-readiness/gap-register.md`.
---
## Storage
**URL:** https://www.stackhousedb.com/docs/security-and-ops/storage
**Description:** File storage and buckets
# Storage
## 📦 File Storage System
### Routes
```
POST /v1/storage/buckets # Create a bucket
GET /v1/storage/buckets # List buckets
GET /v1/storage/buckets/:name # Get bucket info
DELETE /v1/storage/buckets/:name # Delete a bucket
POST /v1/storage/object/:bucket/*path # Upload a file
GET /v1/storage/object/:bucket/*path # Download a file
DELETE /v1/storage/object/:bucket/*path # Delete a file
GET /v1/storage/list/:bucket # List objects in a bucket (?prefix=&limit=&offset=)
```
### Creating Buckets
The bucket name goes in the JSON body, not the URL path:
```bash
# Public bucket (files accessible via URL)
curl -X POST http://localhost:3000/v1/storage/buckets \
-H "Content-Type: application/json" \
-d '{"name": "public", "public": true}'
# Private bucket (requires auth)
curl -X POST http://localhost:3000/v1/storage/buckets \
-H "Content-Type: application/json" \
-d '{"name": "private", "public": false}'
```
### Uploading Files
```bash
curl -X POST http://localhost:3000/v1/storage/object/public/document.pdf \
-F "file=@document.pdf"
```
### Downloading Files
```bash
# Public file
curl http://localhost:3000/v1/storage/object/public/document.pdf -O
# Private file (requires auth)
curl http://localhost:3000/v1/storage/object/private/secret.pdf \
-H "Authorization: Bearer " -O
```
### Listing Files
```bash
curl http://localhost:3000/v1/storage/list/public
```
**Response:**
```json
{
"success": true,
"data": [
{
"id": 1,
"bucket_name": "public",
"path": "document.pdf",
"size": 1024000,
"mime_type": "application/pdf",
"created_at": "2026-08-18T12:00:00Z",
"updated_at": "2026-08-18T12:00:00Z",
"owner_id": null
}
]
}
```
### Deleting Files
```bash
curl -X DELETE http://localhost:3000/v1/storage/object/public/document.pdf
```
---
**Next:** [Replication](/docs/security-and-ops/replication)
---
## Data Model
**URL:** https://www.stackhousedb.com/docs/stackhouse-billing/data-model
**Description:** Stackhouse-Billing tables and how they relate to each other.
# Data Model
All Stackhouse-Billing tables are prefixed `billing_` and created by idempotent
migrations that run on boot when the module is enabled.
## Entity relationships
```mermaid
erDiagram
APPS ||--o{ PRODUCTS : owns
APPS ||--o{ ENTITLEMENTS : owns
APPS ||--o{ OFFERINGS : owns
APPS ||--o{ CUSTOMERS : owns
APPS ||--o{ AUDIENCES : owns
APPS ||--o{ EXPERIMENTS : owns
APPS ||--o{ WEBHOOK_ENDPOINTS : owns
ENTITLEMENTS ||--o{ ENTITLEMENT_PRODUCTS : "grants via"
PRODUCTS ||--o{ ENTITLEMENT_PRODUCTS : "grants via"
OFFERINGS ||--o{ PACKAGES : contains
OFFERINGS }o--o| AUDIENCES : targets
OFFERINGS ||--o| PAYWALLS : renders
PACKAGES }o--|| PRODUCTS : references
CUSTOMERS ||--o{ SUBSCRIPTIONS : has
CUSTOMERS ||--o{ TRANSACTIONS : has
CUSTOMERS ||--o{ RECEIPTS : has
CUSTOMERS ||--o{ EXPERIMENT_ASSIGNMENTS : assigned_to
CUSTOMERS ||--o{ EXPERIMENT_EVENTS : generates
SUBSCRIPTIONS }o--|| PRODUCTS : "purchased"
EXPERIMENTS ||--o{ EXPERIMENT_VARIANTS : has
EXPERIMENTS }o--o| AUDIENCES : targets
EXPERIMENT_VARIANTS }o--|| OFFERINGS : points_to
WEBHOOK_ENDPOINTS ||--o{ WEBHOOK_DELIVERIES : receives
```
## Tables
| Table | Purpose |
|---|---|
| `apps` | Top-level tenant boundary — one row per app using Stackhouse-Billing |
| `products` | Store-side SKUs (`store`, `store_product_id`, `product_type`) |
| `entitlements` | Named feature/access grants (e.g. `pro`) |
| `entitlement_products` | Many-to-many join: which products unlock which entitlements |
| `offerings` | Named groups of packages shown in a paywall (one marked `is_current` per app) |
| `packages` | Purchasable options within an offering, each referencing a product |
| `audiences` | Rule groups used by offerings and experiments |
| `experiments` | A/B tests with status, audience, and metric |
| `experiment_variants` | Weighted offering pointers belonging to an experiment |
| `experiment_assignments` | Sticky `(experiment, customer) → variant` mapping |
| `experiment_events` | Impressions and conversions per variant |
| `paywalls` | Per-offering live/draft visual configuration |
| `customers` | End users, identified by `app_user_id` |
| `subscriptions` | Active/expired subscription state per customer + product |
| `transactions` | Individual purchase/renewal/refund events |
| `receipts` | Raw receipt payloads submitted by clients, for audit/replay |
| `webhook_endpoints` | Outbound listener URLs registered by app owners |
| `webhook_deliveries` | Delivery attempts, status, and retry state |
See `src/billing/schema.rs` for the full DDL.
---
## Entitlements & Security
**URL:** https://www.stackhousedb.com/docs/stackhouse-billing/entitlements
**Description:** How entitlement resolution works, and security caveats to review before production.
# Entitlements & Security
## Entitlement resolver
`resolve_entitlements(app_id, customer_id, now)` returns an array of
`EntitlementInfo`:
```ts
interface EntitlementInfo {
identifier: string
is_active: boolean
will_renew: boolean
period_type: string
latest_purchase_date: string
expires_date: string
grace_period_expires_date: string | null
store: 'app_store' | 'play_store' | 'stripe'
product_identifier: string
}
```
An entitlement is active when any linked subscription's
`current_period_end > now` **or** its `grace_period_expires_at > now`:
```mermaid
flowchart TD
A["resolve_entitlements(app_id, customer_id, now)"] --> B["Load subscriptions
linked to entitlement's products"]
B --> C{"current_period_end > now?"}
C -->|Yes| Active["is_active = true"]
C -->|No| D{"grace_period_expires_at > now?"}
D -->|Yes| Active
D -->|No| Inactive["is_active = false"]
```
## Security caveats
---
## Experiments & Paywalls
**URL:** https://www.stackhousedb.com/docs/stackhouse-billing/experiments
**Description:** A/B testing, audience targeting, and visual paywall configuration in Stackhouse-Billing.
# Experiments & Paywalls
Stackhouse-Billing's growth suite adds four pieces on top of the core subscription model:
- **Audiences** — rule-based customer targeting
- **Experiments** — A/B tests whose variants point at existing offerings
- **Paywalls** — per-offering visual configuration with draft/publish
- **Remote-config resolution** — a customer endpoint that returns the right offering + paywall for that user
## Data model
The new tables are created alongside the existing `billing_*` tables by the same idempotent migration:
- `billing_audiences`
- `billing_experiments`
- `billing_experiment_variants`
- `billing_experiment_assignments`
- `billing_experiment_events`
- `billing_paywalls`
Offerings also gained an optional `audience_id` column, used to restrict an offering to a specific audience.
## Audience rules
An audience stores an array of `{ field, op, value }` rules. The matching engine evaluates rules against:
- customer `attributes` JSONB
- request context (`country`, `app_version`)
- derived flags (`is_existing_subscriber`)
Supported operators are `eq`, `neq`, `gt`, `gte`, `lt`, `lte`, `in`, and `exists`. All rules in an audience must match for the customer to be included.
## Bucketing
When a customer hits the per-user resolution endpoint:
1. Existing experiment assignments are read first; if found, that variant is sticky.
2. For each running experiment, the customer is checked against its optional audience.
3. A deterministic hash of `(experiment_id, customer_id)` maps to a weighted variant.
4. The assignment is persisted, so later traffic-weight changes do not re-bucket already-assigned customers.
The hashing function is a truncated SHA-256 digest; the same inputs always produce the same variant.
## Events and results
`POST /v1/billing/customers/:app_user_id/experiments/impression` and
`POST /v1/billing/customers/:app_user_id/experiments/conversion` append rows to
`billing_experiment_events`.
`GET /v1/billing/admin/experiments/:id/results` returns per-variant counts and a
z-score for treatment variants versus control. These numbers are estimates based on
distinct-customer counts and should not be treated as rigorous statistical inference
without further analysis.
## Endpoints
### Customer
| Method | Endpoint | Notes |
|---|---|---|
| GET | `/v1/billing/customers/:app_user_id/offerings/resolve?app_id=…` | Returns `{ offering, paywall, experiment }` |
| POST | `/v1/billing/customers/:app_user_id/experiments/impression` | Record an impression for the assigned variant |
| POST | `/v1/billing/customers/:app_user_id/experiments/conversion` | Record a conversion for the assigned variant |
### Admin
| Method | Endpoint | Notes |
|---|---|---|
| POST / GET | `/v1/billing/admin/audiences` | Upsert / list audiences |
| POST / GET | `/v1/billing/admin/experiments` | Upsert / list experiments |
| POST | `/v1/billing/admin/experiments/:id/status` | Set `draft`, `running`, `paused`, or `completed` |
| GET | `/v1/billing/admin/experiments/:id/results` | Variant statistics |
| POST | `/v1/billing/admin/offerings/:offering_id/audience` | Attach an audience to an offering |
| POST / GET | `/v1/billing/admin/paywalls` | Upsert / get paywall config |
| POST | `/v1/billing/admin/paywalls/:offering_id/publish` | Promote draft to live config |
## SDK usage
```ts
const stackhouse = createClient('http://localhost:8080');
const resolved = await stackhouse.billing.getResolvedOffering(
appId,
'user-42',
{ country: 'US', app_version: '1.2.0' },
);
// Paywall shown; later, when a purchase completes:
await stackhouse.billing.trackConversion(appId, 'user-42');
```
```tsx
function MyPaywall() {
const track = useExperimentConversion(appId, 'user-42');
return (
startPurchase(pkg, offering)}
/>
);
}
```
## Admin UI
The billing admin interface is embedded at `/admin/billing` in the main Stackhouse
Explore UI. New tabs cover **Audiences**, **Experiments**, and **Paywalls**.
---
## Frontend SDKs
**URL:** https://www.stackhousedb.com/docs/stackhouse-billing/frontend-sdks
**Description:** JS/TS SDK, React bindings, and the embedded admin dashboard.
# Frontend
Three companion frontend pieces ship alongside the server module.
## `@stackhouse/js` (JS/TS SDK)
Location: `stackhouse/js-sdks/stackhouse-js`
```ts
const stackhouse = createClient('http://localhost:3000');
await stackhouse.signIn(email, password);
// Customer
const info = await stackhouse.billing.getCustomerInfo(appId, 'user-42');
if (await stackhouse.billing.hasEntitlement(appId, 'user-42', 'pro')) { /* unlock */ }
await stackhouse.billing.submitAppleReceipt(appId, 'user-42', receiptDataB64);
// Per-user experiment/paywall resolution
const resolved = await stackhouse.billing.getResolvedOffering(appId, 'user-42', {
country: 'US',
app_version: '1.2.0',
});
await stackhouse.billing.trackImpression(appId, 'user-42');
await stackhouse.billing.trackConversion(appId, 'user-42');
// Admin (requires service-admin JWT)
await stackhouse.billing.admin.upsertProduct({
app_id: appId, store: 'app_store', store_product_id: 'pro.monthly',
});
// Growth suite admin
await stackhouse.billing.admin.upsertAudience({
app_id: appId,
identifier: 'us-users',
rules: [{ field: 'country', op: 'eq', value: 'US' }],
});
await stackhouse.billing.admin.upsertExperiment({
app_id: appId,
identifier: 'price-test',
metric: 'purchase',
variants: [
{ identifier: 'control', offering_id: 1, is_control: true, traffic_weight: 50 },
{ identifier: 'treatment', offering_id: 2, is_control: false, traffic_weight: 50 },
],
});
const paywall = await stackhouse.billing.admin.upsertPaywall({
offering_id: 1,
template: 'default',
draft_config: { sections: [{ type: 'text', title: 'Go Pro', body: 'Unlock everything.' }] },
});
```
## `@stackhouse/react` (React bindings)
Location: `stackhouse/js-sdks/stackhouse-react`
```tsx
```
```tsx
function MyPaywall() {
const track = useExperimentConversion(42, 'user-42');
return (
startPurchase(pkg, offering)}
onConversion={track}
/>
);
}
```
Exports from `@stackhouse/react` today: `BillingProvider`, `useOfferings`, `useEntitlements`, `useCustomerInfo`, `useHasEntitlement`, `useResolvedOffering`, `useExperimentConversion`, ``, ``.
## Admin dashboard
The billing admin UI is embedded in the main Stackhouse Explore app at `/admin/billing`.
It is built from `stackhouse/ui` and included in the Rust binary via `rust-embed` when
you build `stackhouse/ui` and then rebuild the Rust server.
```bash
cd stackhouse/ui
npm run build
cd ..
cargo build --release
```
The dashboard manages apps, secrets, products, entitlements, offerings, audiences,
experiments, paywalls, promo grants, and outbound webhook endpoints.
## Tests
Unit tests (no DB required):
```bash
cargo test --lib billing
```
Covers the entitlement resolver, audience matching, experiment bucketing, Stripe
signature round-trip, Apple JWS decoding, and webhook payload signing/filtering.
---
## Overview
**URL:** https://www.stackhousedb.com/docs/stackhouse-billing/overview
**Description:** A native, opt-in subscription backend for Stackhouse that mirrors RevenueCat's core server surface.
# Stackhouse-Billing (RevenueCat-style Subscriptions)
Stackhouse-Billing is a native, opt-in subscription backend for Stackhouse that mirrors
RevenueCat's core server surface: apps, products, entitlements, offerings,
customers, subscriptions, and lifecycle webhooks. It integrates Apple App
Store, Google Play, and Stripe receipts/events out of the box.
## Architecture
```mermaid
flowchart TD
subgraph Clients["Client Apps"]
iOS["iOS App"]
Android["Android App"]
Web["Web App"]
end
subgraph Stores["Purchase Sources"]
Apple["App Store"]
Google["Google Play"]
Stripe["Stripe"]
end
subgraph StackhouseBilling["Stackhouse-Billing Module — /v1/billing"]
API["REST API
Admin · Customer · Public"]
Resolver["Entitlement Resolver"]
Dispatcher["Outbound Webhook Dispatcher
background task"]
end
DB[("Postgres
billing_* tables")]
Backend["Your Backend
outbound webhook listener"]
Clients --> Apple & Google & Stripe
Clients -->|"submit receipt"| API
Apple & Google & Stripe -->|"store webhook"| API
API <--> DB
API --> Resolver
Resolver <--> DB
DB -.->|"subscription events"| Dispatcher
Dispatcher -->|"signed HTTP POST"| Backend
```
## Enabling
Set the environment variable `STACKHOUSE_ENABLE_BILLING=1` before starting
`stackhouse`. When enabled, the module:
- runs idempotent migrations on boot (tables prefixed `billing_`)
- mounts its router at **`/v1/billing`**
- spawns a background outbound-webhook dispatcher
Optional environment fallbacks (overridable per-app via the admin API):
| Variable | Purpose |
|---|---|
| `STACKHOUSE_BILLING_APPLE_SHARED_SECRET` | Apple `verifyReceipt` shared secret |
| `STACKHOUSE_BILLING_STRIPE_SIGNING_SECRET` | Stripe webhook signing secret |
| `STACKHOUSE_BILLING_GOOGLE_ACCESS_TOKEN` | Service-account OAuth token (androidpublisher scope) |
## What's in this section
- [Data Model](/docs/stackhouse-billing/data-model) — tables and entity relationships
- [REST API](/docs/stackhouse-billing/rest-api) — admin, customer, and public routes
- [Webhooks](/docs/stackhouse-billing/webhooks) — inbound store notifications and outbound delivery
- [Entitlements & Security](/docs/stackhouse-billing/entitlements) — resolution logic and security caveats
- [Experiments & Paywalls](/docs/stackhouse-billing/experiments) — A/B tests, audiences, and paywall editor
- [Frontend SDKs](/docs/stackhouse-billing/frontend-sdks) — JS/TS SDK, React bindings, and the embedded admin UI
---
**Next:** [Data Model](/docs/stackhouse-billing/data-model)
---
## REST API
**URL:** https://www.stackhousedb.com/docs/stackhouse-billing/rest-api
**Description:** Admin, customer, and public routes for Stackhouse-Billing.
# REST API
All JSON. Admin routes require a service-admin JWT; customer routes require
any authenticated user; inbound store-webhook routes are unauthenticated but
signature-verified.
## Admin
| Method | Endpoint | Notes |
|---|---|---|
| POST | `/v1/billing/admin/apps` | Create an app |
| GET | `/v1/billing/admin/apps` | List apps |
| POST | `/v1/billing/admin/apps/:app_id/secrets` | Per-app Apple/Google/Stripe/webhook secrets |
| POST | `/v1/billing/admin/products` | Upsert `{app_id, store, store_product_id, product_type, …}` |
| GET | `/v1/billing/admin/products?app_id=…` | List products |
| POST | `/v1/billing/admin/entitlements` | Upsert; accepts `product_ids: []` |
| GET | `/v1/billing/admin/entitlements?app_id=…` | List entitlements |
| POST | `/v1/billing/admin/offerings` | Upsert with nested packages; setting `is_current=true` clears the flag on the app's other offerings |
| POST | `/v1/billing/admin/offerings/:offering_id/audience` | Attach an audience to an offering |
| POST / GET | `/v1/billing/admin/audiences` | Upsert / list audiences |
| POST / GET | `/v1/billing/admin/experiments` | Upsert / list experiments |
| POST | `/v1/billing/admin/experiments/:id/status` | Set `draft`, `running`, `paused`, or `completed` |
| GET | `/v1/billing/admin/experiments/:id/results` | Variant impressions, conversions, and z-score |
| POST / GET | `/v1/billing/admin/paywalls` | Upsert / get paywall config |
| POST | `/v1/billing/admin/paywalls/:offering_id/publish` | Promote draft to live config |
| POST | `/v1/billing/admin/grant` | Manually grant an entitlement (promo / refund recovery): `{app_id, app_user_id, product_id, duration_days}` |
| POST | `/v1/billing/admin/webhook-endpoints` | Register an outbound listener |
## Customer
| Method | Endpoint | Notes |
|---|---|---|
| GET | `/v1/billing/customers/:app_user_id?app_id=…` | Fetch customer |
| GET | `/v1/billing/customers/:app_user_id/entitlements?app_id=…` | Resolved entitlements |
| POST | `/v1/billing/customers/:app_user_id/attributes` | Merge JSONB attributes |
| POST | `/v1/billing/customers/:app_user_id/alias` | Merge two customer identities |
| POST | `/v1/billing/customers/:app_user_id/receipts/apple` | `{app_id, receipt_data}` |
| POST | `/v1/billing/customers/:app_user_id/receipts/google` | `{app_id, package_name, subscription_id, purchase_token, access_token?}` |
| POST | `/v1/billing/customers/:app_user_id/receipts/stripe` | `{app_id, event}` |
| GET | `/v1/billing/customers/:app_user_id/offerings/resolve?app_id=…` | Resolve per-user offering, experiment, and paywall |
| POST | `/v1/billing/customers/:app_user_id/experiments/impression` | Record experiment impression |
| POST | `/v1/billing/customers/:app_user_id/experiments/conversion` | Record experiment conversion |
## Checkout & subscription management (JWT required)
Stripe Checkout flow, distinct from the store-receipt customer routes above — these act on the authenticated caller (`AuthedUser`), not an arbitrary `:app_user_id` path param.
| Method | Endpoint | Notes |
|---|---|---|
| POST | `/v1/billing/checkout` | `{app_id, price_id, app_user_id, customer_email?, success_url, cancel_url}` — creates a Stripe Checkout session (mode=subscription); requires `STRIPE_SECRET_KEY` or `STACKHOUSE_STRIPE_SECRET_KEY` set on the server |
| POST | `/v1/billing/cancel` | `{app_id, app_user_id}` — cancels the caller's active Stripe subscription at period end |
| GET | `/v1/billing/me?app_id=…` | Returns `{customer, subscriptions, entitlements}` for the authenticated user |
## Public
| Method | Endpoint | Notes |
|---|---|---|
| GET | `/v1/billing/offerings?app_id=…` | Fetch the current offering for a paywall (unchanged for backwards compatibility) |
| GET | `/v1/billing/plans` | List all subscription plans (no auth, no `app_id` filter) |
## Purchase flow
A typical client-initiated purchase, from tap to unlocked feature:
```mermaid
sequenceDiagram
participant C as Client App
participant S as App Store / Play / Stripe
participant V as Stackhouse-Billing API
participant R as Entitlement Resolver
participant D as Postgres
C->>S: Initiate purchase
S-->>C: Receipt / purchase token
C->>V: POST /customers/:app_user_id/receipts/*
V->>S: Verify receipt (server-to-server)
S-->>V: Verification result
V->>D: Upsert transaction + subscription
C->>V: GET /customers/:app_user_id/entitlements
V->>R: resolve_entitlements(app_id, customer_id, now)
R->>D: Read subscriptions
D-->>R: Subscription rows
R-->>V: EntitlementInfo[]
V-->>C: Entitlements (is_active, expires_date, …)
```
See [Entitlements & Security](/docs/stackhouse-billing/entitlements) for how
`is_active` is computed, [Experiments & Paywalls](/docs/stackhouse-billing/experiments) for A/B and paywall routes, and [Webhooks](/docs/stackhouse-billing/webhooks) for the inbound store-notification routes.
---
## Webhooks
**URL:** https://www.stackhousedb.com/docs/stackhouse-billing/webhooks
**Description:** Inbound store notifications and outbound webhook delivery.
# Webhooks
Stackhouse-Billing both receives webhooks from the stores and sends its own
outbound webhooks to your backend when subscription state changes.
## Inbound store webhooks
| Method | Endpoint | Notes |
|---|---|---|
| POST | `/v1/billing/webhooks/apple` | App Store Server Notifications V2 `signedPayload` |
| POST | `/v1/billing/webhooks/google` | Google Cloud Pub/Sub push envelope |
| POST | `/v1/billing/webhooks/stripe?app_id=…` | Stripe signed webhook (`Stripe-Signature` header) |
## Outbound webhook signature
Each delivery carries:
- `X-StackhouseBilling-Signature: t=,v1=`
- `X-StackhouseBilling-Event: `
Signature is computed over `.` using the endpoint secret.
## Event types
Subscription lifecycle events include `INITIAL_PURCHASE`, `RENEWAL`, `CANCELLATION`,
`UNCANCELLATION`, `PRODUCT_CHANGE`, `BILLING_ISSUE`, `EXPIRATION`, and `TEST`.
Experiment lifecycle events include `EXPERIMENT_IMPRESSION` and `EXPERIMENT_CONVERSION`,
fired when the customer resolution or tracking endpoints are called. Experiment events
are also stored independently in `billing_experiment_events` regardless of whether an
outbound webhook endpoint is configured.
## End-to-end delivery flow
```mermaid
sequenceDiagram
participant Store as Apple / Google / Stripe
participant V as Stackhouse-Billing
participant D as Postgres
participant Dispatcher as Webhook Dispatcher
participant Backend as Your Backend
Store->>V: POST /webhooks/{apple|google|stripe}
V->>V: Verify signature / JWS
V->>D: Update subscription + transaction
V-->>Store: 200 OK
D-->>Dispatcher: New billing event
Dispatcher->>Dispatcher: Sign payload (HMAC-SHA256)
Dispatcher->>Backend: POST with X-StackhouseBilling-Signature
alt 2xx response
Backend-->>Dispatcher: 200 OK
Dispatcher->>D: Mark delivery succeeded
else non-2xx or timeout
Backend-->>Dispatcher: error / timeout
Dispatcher->>D: Mark delivery failed, schedule retry
end
```
## Retry backoff
Failed deliveries are retried up to 5 times with exponential backoff:
```mermaid
flowchart LR
A1["Attempt 1"] -->|"60s"| A2["Attempt 2"]
A2 -->|"5m"| A3["Attempt 3"]
A3 -->|"30m"| A4["Attempt 4"]
A4 -->|"2h"| A5["Attempt 5"]
A5 -->|"12h"| A6["Final attempt"]
A6 -->|"still failing"| Dead["Marked failed
visible in webhook_deliveries"]
```
---
## Backup & Recovery
**URL:** https://www.stackhousedb.com/docs/production/backup-recovery
**Description:** Logical SQL dump backups via the /v1/admin/backups API, plus PITR's real limitations
# Backup & Recovery
## 💾 Backup and Disaster Recovery
Stackhouse's backup system (`stackhouse/src/storage/backups.rs`) produces **logical SQL dumps**, not filesystem/WAL snapshots — there's no embedded storage engine here to snapshot; the database is Postgres.
### Routes
Mounted under `/v1/admin`, require a service-admin JWT:
```
POST /v1/admin/backups # Create a backup: {"name": "..."}
GET /v1/admin/backups # List backups
POST /v1/admin/backups/:id/restore # Restore from a backup
DELETE /v1/admin/backups/:id # Delete a backup
```
```bash
curl -X POST http://localhost:3000/v1/admin/backups \
-H "Authorization: Bearer " \
-H "Content-Type: application/json" \
-d '{"name": "nightly"}'
```
### How a Backup Is Built
`BackupService::create_backup` walks `information_schema` for every user table (tables prefixed `stackhouse_` or `pg_` are skipped — **this means internal auth/session/billing tables are excluded from these backups**, only your own application tables are dumped) and writes a hand-generated `.sql` file (`CREATE TABLE IF NOT EXISTS ...` + row data, wrapped in `BEGIN;`/`COMMIT;`) to the server's local `backup_path`. Metadata (id, name, size, status) is tracked in the `stackhouse_backups` table. This is not a `pg_dump` wrapper — it's a custom logical-dump implementation, so exotic column types or constraints not reflected in `information_schema.columns` may not round-trip perfectly.
### Recovery Procedure
```bash
# List available backups
curl http://localhost:3000/v1/admin/backups -H "Authorization: Bearer "
# Restore a specific backup by id
curl -X POST http://localhost:3000/v1/admin/backups//restore \
-H "Authorization: Bearer "
```
Because the backup excludes `stackhouse_*` tables, a restore brings back your application data but not users/sessions/billing state — plan around that if you rely on this for full disaster recovery.
### Point-in-Time Recovery (PITR)
A separate `PitrService` (`stackhouse/src/storage/backups/pitr.rs`) implements point-in-time restore by creating a restore schema, finding the nearest base backup before the target time, cloning it, and replaying logical WAL entries from the `stackhouse_pitr_slot` up to the target timestamp.
```bash
POST /v1/admin/backups/pitr/restore
Authorization: Bearer
Content-Type: application/json
{
"target_time": "2026-08-18T12:00:00Z"
}
```
**Response (on success):**
```json
{
"success": true,
"data": ""
}
```
For self-hosted deployments that need native PostgreSQL PITR, you can also use PostgreSQL's own WAL archiving + `pg_basebackup`/`recovery_target_time` mechanism, or a managed Postgres provider's point-in-time restore feature (e.g. Cloud SQL, RDS) alongside this backup system.
---
**Next:** [API Reference](/docs/api-reference/api-reference)
---
## Deployment
**URL:** https://www.stackhousedb.com/docs/production/deployment
**Description:** Production deployment guide
# Deployment
## 🚀 Production Deployment Guide
> **Recommended path:** deploy on **Google Cloud Platform** — see [GCP Deployment](#-gcp-deployment-recommended) below. The cost model and pricing tiers this deployment target is designed around are documented in *`BUSINESS_PLAN_GCP.md`*. The generic Docker/systemd instructions further down remain valid for self-hosting on any VM or bare metal.
---
## ☁️ GCP Deployment (Recommended)
Two paths, matched to the tiers in `BUSINESS_PLAN_GCP.md`:
- **Cloud Run** — for Free/Starter/Pro tiers and most self-hosters. True scale-to-zero, no cluster to manage, cheapest option by a wide margin below sustained-load traffic.
- **GKE Autopilot** — for Team/Enterprise tiers, or once you need Qdrant/Redis/Postgres running as first-class stateful workloads alongside Stackhouse. Uses the existing Helm chart at `deploy/helm/stackhouse/`.
Both assume the `stackhouse/Dockerfile` in this repo, which already builds the UI and a slim Debian runtime image.
### Option A: Cloud Run (launch / low-cost path)
**1. One-time project setup**
```bash
export PROJECT_ID=your-gcp-project
export REGION=us-central1
gcloud config set project $PROJECT_ID
gcloud services enable \
run.googleapis.com \
sqladmin.googleapis.com \
redis.googleapis.com \
artifactregistry.googleapis.com \
secretmanager.googleapis.com \
vpcaccess.googleapis.com
gcloud artifacts repositories create stackhouse \
--repository-format=docker --location=$REGION
```
**2. Provision managed dependencies**
```bash
# Cloud SQL for PostgreSQL — start with the smallest tier, resize later
gcloud sql instances create stackhouse-pg \
--database-version=POSTGRES_16 \
--tier=db-f1-micro \
--region=$REGION \
--storage-auto-increase
gcloud sql databases create stackhouse --instance=stackhouse-pg
# Memorystore (Redis) — Basic tier is enough below the Team plan
gcloud redis instances create stackhouse-cache \
--size=1 --region=$REGION --tier=basic
# Serverless VPC connector so Cloud Run can reach Memorystore's private IP
gcloud compute networks vpc-access connectors create stackhouse-connector \
--region=$REGION --range=10.8.0.0/28
```
**3. Store secrets**
```bash
echo -n "$(openssl rand -hex 32)" | gcloud secrets create stackhouse-jwt-secret --data-file=-
echo -n "sk_live_..." | gcloud secrets create stackhouse-stripe-secret --data-file=-
echo -n "whsec_..." | gcloud secrets create stackhouse-stripe-webhook-secret --data-file=-
```
**4. Build and push**
```bash
cd stackhouse
gcloud builds submit --tag $REGION-docker.pkg.dev/$PROJECT_ID/stackhouse/stackhouse:latest
```
**5. Deploy**
```bash
gcloud run deploy stackhouse \
--image=$REGION-docker.pkg.dev/$PROJECT_ID/stackhouse/stackhouse:latest \
--region=$REGION \
--platform=managed \
--allow-unauthenticated \
--port=8080 \
--min-instances=0 \
--max-instances=10 \
--cpu=1 --memory=512Mi \
--vpc-connector=stackhouse-connector \
--add-cloudsql-instances=$PROJECT_ID:$REGION:stackhouse-pg \
--set-env-vars="STACKHOUSE_HOST=0.0.0.0,STACKHOUSE_PORT=8080,STACKHOUSE_ENABLE_BILLING=true" \
--set-env-vars="STACKHOUSE_URL=postgres://postgres:PASSWORD@/stackhouse?host=/cloudsql/$PROJECT_ID:$REGION:stackhouse-pg" \
--set-secrets="STACKHOUSE_JWT_SECRET=stackhouse-jwt-secret:latest,STRIPE_SECRET_KEY=stackhouse-stripe-secret:latest,STACKHOUSE_BILLING_STRIPE_SIGNING_SECRET=stackhouse-stripe-webhook-secret:latest"
```
`--min-instances=0` is what makes Free/Starter tenants near-free to host when idle — see `BUSINESS_PLAN_GCP.md` §5–6 for the cost model this relies on. Raise it to `1` for Pro-tier tenants that need to avoid cold starts.
**Cost note:** at list price this is $0.000024/vCPU-sec + $0.0000025/GiB-sec, with the first 180K vCPU-sec and 360K GiB-sec/month free — a low-traffic tenant costs cents per month. Re-check current rates at [cloud.google.com/run/pricing](https://cloud.google.com/run/pricing).
### Option B: GKE Autopilot (Team / Enterprise / high-sustained-load)
The Helm chart in `deploy/helm/stackhouse/` already ships Qdrant, PostgreSQL (Bitnami sub-chart), and Redis as dependencies — Autopilot just removes node management.
```bash
gcloud container clusters create-auto stackhouse-cluster --region=$REGION
gcloud container clusters get-credentials stackhouse-cluster --region=$REGION
# Create the secrets the chart expects (see deploy/helm/stackhouse/values.yaml)
kubectl create secret generic stackhouse-secrets \
--from-literal=database-url="postgres://stackhouse:PASSWORD@stackhouse-postgresql:5432/stackhouse" \
--from-literal=jwt-secret="$(openssl rand -hex 32)" \
--from-literal=redis-url="redis://:PASSWORD@stackhouse-redis-master:6379" \
--from-literal=qdrant-url="http://stackhouse-qdrant:6333" \
--from-literal=data-encryption-key="$(openssl rand -hex 32)"
helm dependency update deploy/helm/stackhouse
helm install stackhouse deploy/helm/stackhouse \
--set stackhouse.image.repository=$REGION-docker.pkg.dev/$PROJECT_ID/stackhouse/stackhouse \
--set stackhouse.image.tag=latest
```
Autopilot bills per-Pod CPU/memory request rather than per-node, so the chart's existing `resources.requests` in `values.yaml` directly determine cost — tune them down for smaller Team-tier tenants rather than over-provisioning by default.
### Choosing between them
| | Cloud Run | GKE Autopilot |
|---|---|---|
| Best for | Free, Starter, Pro, most self-hosters | Team, Enterprise, sustained high load |
| Scale-to-zero | Yes | No (Autopilot still bills scheduled Pod requests) |
| Stateful sidecars (Qdrant/Redis/Postgres in-cluster) | No — use managed Cloud SQL/Memorystore instead | Yes, via the Helm chart |
| Ops overhead | Near zero | Low (no node management, but still a cluster) |
---
### Docker Deployment (generic / any host)
Stackhouse is a Postgres-backed server (`StackhouseStore` connects via the `STACKHOUSE_URL` connection string), so a real deployment needs a Postgres instance alongside it — not just a local data volume.
**docker-compose.yml:**
```yaml
version: '3.8'
services:
postgres:
image: postgres:16
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
- POSTGRES_DB=stackhouse
volumes:
- stackhouse_pg:/var/lib/postgresql/data
stackhouse:
image: stackhouse:latest
ports:
- "3000:3000"
volumes:
- stackhouse_storage:/app/storage
depends_on:
- postgres
environment:
- RUST_LOG=info
- STACKHOUSE_URL=postgres://postgres:postgres@postgres:5432/stackhouse
- STACKHOUSE_JWT_SECRET=change-me
- STACKHOUSE_STORAGE_PATH=/app/storage
volumes:
stackhouse_pg:
stackhouse_storage:
```
```bash
docker-compose up -d
```
### Systemd Service
```ini
[Unit]
Description=Stackhouse Server
After=network.target
[Service]
Type=simple
User=stackhouse
WorkingDirectory=/opt/stackhouse
Environment=STACKHOUSE_URL=postgres://stackhouse:PASSWORD@localhost:5432/stackhouse
Environment=STACKHOUSE_JWT_SECRET=your-secret-key
Environment=STACKHOUSE_STORAGE_PATH=/var/lib/stackhouse/storage
ExecStart=/usr/local/bin/stackhouse serve \
--host 0.0.0.0 \
--port 3000
Restart=always
RestartSec=10
[Install]
WantedBy=multi-user.target
```
### Configuration
Stackhouse is configured entirely via CLI flags/environment variables — there is no `config.toml` file. The `serve` subcommand (`stackhouse serve`) accepts:
| Flag | Env var | Default | Purpose |
|---|---|---|---|
| `-u, --url` (global) | `STACKHOUSE_URL` | `postgres://postgres:postgres@localhost:5432/stackhouse` | Postgres connection string |
| `-p, --port` | `STACKHOUSE_PORT` | `3000` | Port to bind |
| `-h, --host`* | `STACKHOUSE_HOST` | `0.0.0.0` | Host to bind |
| `-m, --memory` | `STACKHOUSE_MEMORY` | off | Use an in-memory database (testing only) |
| `--jwt-secret` | `STACKHOUSE_JWT_SECRET` | none | Required for server mode |
| `--storage-path` | `STACKHOUSE_STORAGE_PATH` | none | Local path for file storage objects |
\* `-h` is normally reserved for `--help`, but `clap` here binds it to `--host`; use the long flag if that's ambiguous in your shell setup.
### Reverse Proxy (Nginx)
```nginx
location / {
proxy_pass http://localhost:3000;
proxy_http_version 1.1;
proxy_set_header Upgrade $http_upgrade;
proxy_set_header Connection "upgrade";
proxy_set_header Host $host;
}
```
### Scaling
---
**Next:** [Performance](/docs/production/performance)
---
## Monitoring
**URL:** https://www.stackhousedb.com/docs/production/monitoring
**Description:** Prometheus metrics, JSON summary endpoint, and structured logging
# Monitoring
## 📊 Monitoring & Observability
Metrics are implemented with the `prometheus` crate (`stackhouse/src/platform/metrics.rs`) and cover HTTP request counts/duration, DB query duration, auth events (signups/logins/OAuth/failed logins/token refreshes), storage operations, and realtime WebSocket connections/subscriptions.
### Metrics Endpoints
```
GET /metrics # Prometheus text-exposition format (mounted at root, not under /v1)
GET /v1/metrics/summary # Human-readable JSON summary
```
```bash
curl http://localhost:3000/metrics
```
**Response** (`text/plain; version=0.0.4`, standard Prometheus exposition format):
```
# HELP stackhouse_http_requests_total Total HTTP requests
# TYPE stackhouse_http_requests_total counter
stackhouse_http_requests_total{method="GET",path="/v1/query/users",status="200"} 42
...
```
```bash
curl http://localhost:3000/v1/metrics/summary
```
**Response:**
```json
{
"uptime_seconds": 3600,
"http": { "active_connections": 42 },
"auth": { "total_signups": 10, "total_logins": 120, "failed_logins": 3 },
"storage": { "total_uploads": 8, "total_downloads": 55 },
"realtime": { "active_connections": 4, "active_subscriptions": 12, "total_messages": 900 }
}
```
### Dashboard Integration
**Prometheus:**
```yaml
scrape_configs:
- job_name: 'stackhouse'
metrics_path: '/metrics'
static_configs:
- targets: ['localhost:3000']
```
No packaged Grafana dashboard ships with this repo today — build panels against the metric names above, or export `/metrics` into your existing Prometheus/Grafana stack.
### Alerts
Example AlertManager rules built on the exposed counters/histograms (`stackhouse_http_requests_total`, `stackhouse_http_request_duration_seconds`, `stackhouse_auth_failed_logins_total`, etc. — check the exact metric names via `curl /metrics` for your build):
```yaml
groups:
- name: stackhouse
rules:
- alert: HighAuthFailureRate
expr: rate(stackhouse_auth_failed_logins_total[5m]) > 1
for: 5m
```
### Logging
Logging uses `tracing` with a compact formatter (see `stackhouse/src/main.rs`), controlled by the standard `RUST_LOG` env var (e.g. `RUST_LOG=info`, `RUST_LOG=stackhouse=debug,tower_http=info`). There is no `[logging]` config file — set `RUST_LOG` in your process environment.
Structured log forwarding is also supported: setting `STACKHOUSE_LOG_DRAIN_URL` (and optionally `STACKHOUSE_LOG_DRAIN_KEY`) enables an outbound log-drain client (`stackhouse/src/platform/log_drain.rs`), plus a `/v1/admin` log-drain management router for configuring drains per tenant.
---
**Next:** [Backup & Recovery](/docs/production/backup-recovery)
---
## Performance
**URL:** https://www.stackhousedb.com/docs/production/performance
**Description:** Tuning connection pooling and Qdrant vector search — Stackhouse has no embedded storage engine to tune
# Performance
## ⚡ Performance Tuning & Optimization
Stackhouse is a thin Axum server over **PostgreSQL** (relational data) and **Qdrant** (vector search) — there is no embedded LSM storage engine in this build, so tuning is mostly about tuning those two systems plus connection pooling, not app-level compaction/cache knobs.
### Connection Pooling
Per-tenant pool settings are managed by `PoolingService` (`stackhouse/src/platform/pooling.rs`) and persisted in `stackhouse_pool_configs`:
```json
{
"pool_mode": "transaction",
"max_connections": 100,
"min_connections": 0,
"idle_timeout_secs": 600,
"max_lifetime_secs": 1800,
"connection_timeout_secs": 30,
"statement_timeout_secs": 30,
"query_wait_timeout_secs": 30
}
```
`max_connections` defaults to 100 per tenant; lower it for smaller Postgres instances (e.g. Cloud SQL `db-f1-micro`) to avoid exhausting the server's own `max_connections`.
### Vector Search
Vector collections are backed by **Qdrant** (`stackhouse/src/storage/vectors.rs` talks to the Qdrant HTTP API), which manages its own HNSW index (`m`, `ef_construct`, `ef_search`, etc.) server-side. Tune those via Qdrant's own collection config (see the [Qdrant HNSW config docs](https://qdrant.tech/documentation/concepts/indexing/#vector-index)) rather than through Stackhouse.
### Indexes
Use standard `CREATE INDEX` via the raw-SQL admin endpoint or a migration for hot columns on Postgres tables; RLS policies (see [Row-Level Security](/docs/security-and-ops/row-level-security)) that filter on a column benefit the most from an index on that column, since Postgres evaluates the `USING` expression per row.
### Optimization Tips
---
**Next:** [Monitoring](/docs/production/monitoring)
---
## Links
- [GitHub](https://github.com/ArjavDesa912/stackhouse)
- [Discord](https://discord.gg/stackhouse)
- [Support](mailto:support@stackhouse.dev)