RafikiDB REST API#

You are working with the RafikiDB REST API. Base URL http://localhost:8080, all routes under /api/v1. OpenAPI spec at /openapi.yaml, Swagger UI at /docs, health check GET /healthz.

Auth#

Three credential paths:

• Project-user auth: use the project auth endpoints, NOT /auth/login.
• The API key must belong to the project in the URL (else 403).
• SSE endpoints authenticate via ?token= / ?api_key= (EventSource cannot send headers).

Response envelope#

Success (200/201):

{ "success": true, "message": "Request was successful", "data": ... }

Error:

{ "success": false, "message": "...", "errors": ["..."], "details": ... }

Error sentinels map to statuses: unauthorized 401, forbidden 403, not found 404, conflict 409, invalid input 400, quota exceeded 429, else 500. Friendly messages replace bare sentinels ("unauthorized" becomes "Invalid or missing credentials").

Data CRUD: /api/v1/data/:table (DataAuth)#

• GET /data/:table list. Query params:
  • select=col1,col2 projection (unknown columns ignored)
  • order=created_at.desc,updated_at.asc
  • limit (default 50, max 200), offset
  • cursor=<id> keyset pagination over (created_at, id), valid with created_at ordering; offsets are ignored when cursor is set
  • count=exact returns {"count": n} instead of rows
  • Filters use dot syntax on any column param: eq., neq., gt., gte., lt., lte., like., ilike., is.null, is.not.null, in.(a,b)
• GET /data/:table/:id single row.
• POST /data/:table insert (body = raw JSON object), 201.
• PATCH /data/:table/:id partial update, returns updated row.
• DELETE /data/:table/:id soft delete, 204.
• The users table is virtual (maps to project_users) and rejects writes with 400; use project auth endpoints instead.
• RLS policies are enforced per table. Unknown fields rejected.

Project auth: /api/v1/projects/:projectID/auth#

• POST /auth/signup {email, password, full_name, phone, metadata?} -> 201 {user, tokens:{access_token, access_token_expires_at, token_type}}
• POST /auth/signin {email, password} -> {user, tokens}
• POST /auth/otp/request {phone} -> {phone, expires_at, otp_code?} (otp_code only in non-prod)
• POST /auth/otp/verify {phone, code (4-8 digits), full_name, metadata?} -> {user, tokens}
• POST /auth/reset-password {email}; POST /auth/reset-password/confirm {email, code, new_password}
• GET /auth/users?search=&page=&limit=, GET/PATCH/DELETE /auth/users/:userID (JWT)
• GET/PUT /auth/settings {email_enabled, phone_enabled, allow_signup, otp_length, otp_expiry_minutes, jwt_expiry_seconds}

Query engine: /api/v1/projects/:projectID/query (EitherAuth)#

POST /query/run:

{
  "table_slug": "orders",
  "config": {
    "columns": ["id", "total"],
    "filters": [{"column": "status", "operator": "eq", "value": "paid", "values": [], "logic": "AND|OR"}],
    "sort": [{"column": "created_at", "direction": "desc"}],
    "limit": 50, "offset": 0, "distinct": false,
    "joins": [{"type": "LEFT JOIN", "table": "customers", "on": null, "from_column": "customer_id", "to_column": "id"}],
    "aggregates": [{"fn": "COUNT", "column": "id", "alias": "total"}],
    "group_by": ["status"]
  }
}

Response:

{
  "success": true, "message": "Request was successful",
  "data": { "columns": ["id", "status"], "rows": [{"id": "...", "status": "paid"}], "total": 42, "sql": "SELECT ..." }
}

Also: POST /query/preview -> {sql, rest_url, curl}; POST /query/exec {sql} (SELECT/WITH only, max 8000 chars, runs in project schema); GET|POST /query/saved, DELETE /query/saved/:queryID.

Realtime#

• POST /realtime/connect?table=<t> (EitherAuth) -> {url, token, channel, expires_at}. Channel project:{id}:{table}, token 24h with subs claim.
• GET /realtime/events SSE: auth via ?token= then ?api_key=; filters ?table=, ?events=INSERT,UPDATE,DELETE. Frames: id:, event: INSERT|UPDATE|DELETE, data: = {id, type, table, project_id, record, old_record?, created_at} (old_record on UPDATE only). Heartbeat every 25s.
• GET/PUT /realtime/settings, GET/POST /realtime/channels, POST /realtime/token (JWT, 10-min TTL).

Storage: /api/v1/projects/:projectID/storage#

POST /storage/buckets {name, slug, is_public, file_size_limit?, allowed_mime_types?}; GET /storage/buckets, GET/DELETE /storage/buckets/:bucketID; GET /storage/buckets/:bucketID/objects?prefix=; POST /storage/buckets/:bucketID/folders {name}; DELETE /storage/objects/:objectID; POST /storage/signed-upload-url {bucket_id, object_name, content_length?} -> {url, storage_path?}; POST /storage/signed-download-url {object_id}; GET /storage/public/:bucketID/:objectID (no auth, 302).

Env vars: /api/v1/projects/:projectID/env#

GET /env; POST /env {key, value, environment: production|preview|development}; PATCH /env/:varID {value?, environment?}; DELETE /env/:varID; POST /env/bulk {environment, vars: {KEY: "value"}}.

Secrets: /api/v1/projects/:projectID/secrets#

GET /secrets (values masked); POST /secrets {key, value, description?}; GET /secrets/:secretID/value (revealed {id, key, value}); PATCH /secrets/:secretID; DELETE /secrets/:secretID. Also GET /api/v1/secrets/resolve/:key (APIKeyAuth only, key uppercased).

Webhooks: /api/v1/projects/:projectID/webhooks#

GET /webhooks; POST /webhooks {name, url, events: [string], secret?}; PATCH /webhooks/:webhookID {name?, url?, events?, secret?, enabled?}; DELETE /webhooks/:webhookID; GET /webhooks/:webhookID/deliveries; POST /webhooks/:webhookID/send {event, payload}; POST /webhooks/:webhookID/deliveries/:deliveryID/retry.

Functions: /api/v1/projects/:projectID/functions#

GET /functions; POST /functions {name, runtime?: javascript|typescript, code?}; GET/PATCH/DELETE /functions/:fnID; POST /functions/:fnID/deploy; POST /functions/:fnID/invoke {method?, path?, headers?, body?} -> {status, headers, body, duration_ms}.

Payments: /api/v1/projects/:projectID/payments#

• GET /payments/settings; PUT /payments/settings {mpesa_environment: sandbox|live, mpesa_shortcode, mpesa_consumer_key, mpesa_consumer_secret, mpesa_passkey, mpesa_callback_url, snippe_api_key, snippe_webhook_secret, snippe_mode: live|sandbox, snippe_callback_url, is_active}
• POST /payments/mpesa/stk-push {phone, amount, description} -> {transaction_id, checkout_request_id, response_code, ...}
• POST /payments/snippe/initiate {amount, phone, first_name, last_name, email?, description?, order_id?} -> {transaction_id, reference, status, expires_at}
• POST /payments/snippe/session {amount, description?, redirect_url?, customer_name?, customer_phone?, order_id?, expires_in? (60-86400)} -> includes checkout_url, payment_link_url
• GET /payments/snippe/status/:reference; GET /payments/transactions?status=&page=&limit=; GET /payments/transactions/:transactionID
• Provider callbacks (no auth): POST /api/v1/webhooks/mpesa/:projectID/callback, POST /api/v1/webhooks/snippe/:projectID/callback.

Schema: /api/v1/projects/:projectID (JWT)#

GET|POST /tables (POST {name, description?, columns?: [{name, data_type, char_length?, required, unique, indexed, description?, default_value?}]}; data_type one of: string text integer smallint bigint serial decimal float boolean date timestamp time interval uuid json jsonb enum email phone money file char bytea inet); GET/PATCH/DELETE /tables/:tableID; POST /tables/:tableID/columns; PATCH/DELETE /tables/:tableID/columns/:columnID; GET /schema/preview/:tableID -> {sql, json_schema, endpoints}; GET /schema/diagram; GET|POST /schema/relationships, PATCH/DELETE /schema/relationships/:relationshipID; POST /schema/exec-ddl; views/functions/triggers under /schema/*.

Records (RLS-aware): /api/v1/projects/:projectID/tables/:tableSlug/records (APIKeyAuth)#

POST {data: {...}}; GET ?limit=20&offset=0 + non-reserved params become exact-match filters; GET/PATCH/DELETE /:recordID (PATCH {data: {...}}).

RLS: /api/v1/projects/:projectID/tables/:tableID/rls (JWT)#

GET|POST {action: select|insert|update|delete, subject: public|authenticated|owner|api_key|role, effect: allow|deny, condition?, role_ids?}; PATCH/DELETE /:policyID.

RBAC#

/api/v1/rbac (JWT): POST /roles, POST /permissions, POST /roles/:roleID/permissions, POST /users/:userID/roles. Project roles: /api/v1/projects/:projectID/roles and /:projectID/permissions (JWT).

Other platform routes (JWT)#

• GET /api/v1/audit-logs?limit= (default 50); GET /api/v1/projects/:projectID/request-logs?limit= (max 1000); GET .../usage?limit= (1-60); GET /api/v1/plans; GET|PATCH .../billing.
• Cron: /api/v1/projects/:projectID/cron {name, schedule, target_type: http|function, target_url?, function_slug?, payload?}, POST /:jobID/run.
• Domains: /api/v1/projects/:projectID/domains (verify flow). Integrations: /integrations {provider, name, config?, enabled?}, POST /:integrationID/test.
• GraphQL: POST /api/v1/projects/:projectID/graphql {query, variables?} (raw GraphQL result, not envelope).
• Notifications: GET /api/v1/me/notifications?token=<jwt> SSE.
• Project deletion: DELETE /api/v1/projects/:projectID returns 202, background deletion, completion via GET /api/v1/events SSE.

Gotchas#

• /rest/v1/{table} URLs from query/preview are preview artifacts; real CRUD is /api/v1/data/....
• Filter operator is.null / is.not.null use nested dot (not.null); in.(a,b) values comma-split.
• Data list returns a bare array in data (no pagination wrapper).
• Deletes on data/records are soft deletes; realtime DELETE events carry the pre-delete row in record.
• Limits: data list limit 50/200; records 20; audit-logs 50; request-logs 200/1000; usage 12/60; auth users 20; payments transactions 20.