SQL & Database

Every project gets a dedicated PostgreSQL 17 database. Run SQL over HTTP — parameterized queries, returned rows as JSON, no driver to install.

Pre-installed extensions

• pgvector — AI/embedding vectors with cosine + L2 + inner product distance
• uuid-ossp — uuid_generate_v4() for primary keys
• pgcrypto — hashing, encryption, secure random
• pg_stat_statements — query performance stats

Run a query

# Create a table
curl -X POST $API/{slug}/sql \
  -H "X-API-Key: $KEY" -H "Content-Type: application/json" \
  -d '{
    "query": "CREATE TABLE users (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), email TEXT UNIQUE, name TEXT, created_at TIMESTAMPTZ DEFAULT NOW())"
  }'

# Insert with parameterized values
curl -X POST $API/{slug}/sql \
  -H "X-API-Key: $KEY" \
  -d '{
    "query": "INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *",
    "params": ["alice@example.com", "Alice"]
  }'

# Select
curl -X POST $API/{slug}/sql \
  -H "X-API-Key: $KEY" \
  -d '{
    "query": "SELECT * FROM users WHERE email = $1",
    "params": ["alice@example.com"]
  }'

Best practices

• Use UUID primary keys — uuid-ossp is pre-installed
• Add created_at TIMESTAMPTZ DEFAULT NOW() to every table
• Use JSONB for flexible/nested data — fully indexable
• Create indexes on columns you filter/sort by
• Use RETURNING * on INSERT/UPDATE to get the row back in one call — also required for realtime event payloads

pgvector — AI embeddings

# Schema
CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  content TEXT,
  embedding vector(1536)        -- OpenAI ada-002 size; adjust to your model
);

# Index for fast similarity search
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

# Cosine-similarity query (lower distance = more similar)
SELECT content, 1 - (embedding <=> $1) AS similarity
FROM documents
ORDER BY embedding <=> $1
LIMIT 10;

Database usage

curl $API/{slug}/database/usage -H "X-API-Key: $KEY"

Endpoints