SWAIN - User Guide

🚀 What You Can Build

📋 Prerequisites

• Database Access: You have DB credentials (host, port, DB type, DB name, username, password) for one or more stages (Development / Staging / Production)
• Database Permissions: You are authorized to read/write the target database
• SMTP Configuration: If using email features: SMTP host, port, username/password, From address & From name, Base URL (e.g., your website/app origin), and Password Reset Path (must include a %s placeholder for the token)
• OAuth Setup: If using social login: Google & GitHub OAuth client ID/secret and approved redirect URLs

⚡ Workflow at a Glance

1️⃣ Step 1 — Create a Project

Project Creation Interface

• Go to Projects → New Project
• Give it a name and (optionally) a description
• Think of a project as a workspace that can contain multiple connections (e.g., dev/staging/prod)

2️⃣ Step 2 — Create a Connection

Database Connection Configuration

Navigate to Project → Connections → New Connection and fill the form:

Required Fields

• Stage: Development / Staging / Production
• DB Type: e.g., PostgreSQL, MySQL, etc.
• Host & Port: Your database server hostname and port number
• Database Name: The specific database to connect to
• Username & Password: Database credentials for access

Tips

Click Create. The connection appears in your project.

3️⃣ Step 3 — Sync the Schema

Schema Synchronization Process

Schema Synchronization Process

• Click Sync Schema on your connection
• We scan and import tables, columns, types, and constraints (PKs, FKs, uniques, nullability)
• You'll see a visual summary of your schema

Why this matters

4️⃣ Step 4 — Authentication Settings

Open Authentication → General Settings. There are three cards.

4.1 General Security

Security Settings Panel

Toggle and configure:

• Enable Authentication: Turns on JWT-based auth for protected routes
• Enable Row-Level Security (RLS): Scope records by tenant (see 4.2)
• JWT Secret: A long, random string used to sign tokens. Change only with caution
• Access Token Expiry: e.g., 1h
• Refresh Token Expiry: e.g., 7h
• Password Token Expiry: e.g., 1h (for "forgot password" links)
• Public Routes (Exclude from Auth): Comma-select API paths that do not require auth (e.g., /auth/login, /products if public)

4.2 Tenant Scoping (Row-Level Security)

Multi-Tenant Configuration

Use this when multiple users belong to an account/tenant, or when each user must only see their own records.

• Choose Tenant Model: Pick the table representing tenants (e.g., accounts for org-scoped apps or users for simple single-user tenancy)
• Default Tenant Field Name: Choose the column name to add for scoping (e.g., account_id or user_id)
• Exclude Tables: Select tables not to receive the tenant column (e.g., products, countries, other global catalogs)

What happens

4.3 Email (SMTP) Settings

SMTP Configuration Form

Used for password reset emails.

• SMTP Host & Port: Your email provider's SMTP server details
• SMTP Username & Password: Credentials for email sending
• From Address: e.g., no-reply@yourdomain.com
• From Name: e.g., Your App
• Base URL: The origin where users complete reset (e.g., https://siteclear.mobix-sites.co.uk)
• Password Reset Path: Must include %s placeholder for the token—e.g., /reset-password?token=%s

How it works

5️⃣ Step 5 — User Mapping (9 Cards)

Map your own tables/columns to what our auth and RBAC layers expect. You can name the tables anything; mapping tells the platform how to use them.

8.1 User (required for authentication)

User Table Mapping

• Required columns: id, email, firstname, password, isactive
• Map each to the corresponding columns in your user table

8.2 Password Reset Token (required for reset password flow)

Password Reset Token Mapping

• Required columns: id, userid (FK to User), token, expiresAt, usedAt, createdAt

8.3 Session (optional; for DB-backed sessions)

Session Management Mapping

• Columns: id, userId, refreshToken, expiresAt, lastUsed

8.4 API Keys (recommended for SDK/server-to-server)

API Key Management Mapping

• Columns: id, userId (or accountId), key, createdAt, revokedAt (nullable)

8.5 Social Profile (optional; for social login)

Social Profile Mapping

• Columns: id, userId, provider (google/github), providerUserId, email, createdAt

8.6 Role

Role Definition Mapping

• Columns: id, name, description

8.7 Permission

Permission Definition Mapping

• Columns: id, name, description

8.8 Role Permission (join table)

Role-Permission Association

• Columns: id, roleId, permissionId

8.9 User Role (assign roles to users)

Role-Permission Association

• Columns: id, userId, roleId

6️⃣ Step 6 — Social Authentication (Google/GitHub)

• Open Authentication → Social
• Enter your Google and/or GitHub OAuth Client ID/Secret
• Configure redirect/callback URLs in your provider dashboards to match the URLs shown in the platform
• Once enabled and mapped (see 8.5), users can sign in/up via social providers

GitHub OAuth Setup & Google OAuth Setup

Google OAuth Console

GitHub OAuth Application

7️⃣ Step 7 — Authorization (Roles & Permissions)

Turn on RBAC to restrict which users can access which actions/routes.

• Go to Authorization in the project
• Create Roles (e.g., admin, manager, member)
• Create Permissions (e.g., orders.read, orders.create, orders.update, orders.delete)
• Link them via Role Permission
• Assign roles to users via User Role

Creating Roles & Defining Permissions

Role Management Interface

Permission Assignment Panel

At runtime

8️⃣ Step 8 — Generate, Deploy & Use the API

Once mapping & settings are complete:

1

Generate API

Create build and deploy your API

Create build

Deploy build

After your API is deployed, check the connection settings you will see your end point url

What You Get

• Base URL for your project/stage
• Auth endpoints (e.g., /auth/register, /auth/login, /auth/refresh, /auth/reset-password)
• CRUD endpoints for your synced tables

Need SDKs fast? Jump to the Swain CLI section for installation and interactive generation guidance.

Typical Flows

• Register/Login → Get access & refresh tokens
• Use Access Token in Authorization: Bearer <token> to call protected routes until it expires
• Refresh using the refresh token to get a new access token
• Password Reset: /auth/request-reset → user receives email link → /auth/confirm-reset consumes the token

📚 API Documentation

🛠️ Swain CLI SDK Generation

swain_cli is our zero-setup command-line wrapper around OpenAPI Generator. Use it when you want fresh SDKs without leaving your terminal or installing Java yourself.

Quick Start

Interactive Mode Walkthrough

$ swain_cli interactive
[swain_cli] interactive SDK generation wizard
[swain_cli] press Ctrl+C at any time to cancel
[swain_cli] no authentication token configured.
? Sign in before continuing?  Yes
? Username or email: founder@acme.co
? Password: ********
[swain_cli] stored access token (foun...co) in system keyring

? Select a project > Customer API (#123)
? Select a connection > #981 - prod-db (postgres, schema=public)
? Output directory (sdks): ./sdks
? Target languages (comma separated, e.g. python,typescript): python,typescript

[swain_cli] configuration preview
[swain_cli]   swain base: https://api.swain.technology
[swain_cli]   project: Customer API (#123)
[swain_cli]   connection: #981 (prod-db)
[swain_cli]   dynamic swagger: https://api.swain.technology/api/dynamic_swagger?project=123&connection=981
[swain_cli]   output: ./sdks
[swain_cli]   languages: python, typescript
[swain_cli]   extra generator args: ['--global-property=apiDocs=false,apiTests=false,modelDocs=false,modelTests=false']
[swain_cli]   engine: embedded
[swain_cli]   skip validate: False
[swain_cli]   verbose: False
[swain_cli]   java options: -Xms2g -Xmx10g -XX:+UseG1GC
[swain_cli] equivalent command: swain_cli gen --swain-project-id 123 --swain-connection-id 981 -o ./sdks -l python -l typescript --generator-arg --global-property=apiDocs=false,apiTests=false,modelDocs=false,modelTests=false --java-opt -Xms2g --java-opt -Xmx10g --java-opt -XX:+UseG1GC
? Run generation now?  Yes

[swain_cli] Fetching OpenAPI schema…
[swain_cli] Preparing embedded OpenAPI Generator (7.6.0)
[OpenAPI Generator] generating python client... done
[OpenAPI Generator] generating typescript client... done
[swain_cli] SDKs written to ./sdks/python and ./sdks/typescript

The wizard echoes the exact swain_cli gen command before running it. Prefer scripting? Use the same flags directly:

swain_cli gen --swain-project-id <project_id> --swain-connection-id <connection_id> \
  -l python -l typescript -o ./sdks \
  -p packageName=swain_api -p packageVersion=1.0.0

What Gets Generated

The command above writes one subfolder per generator (for example ./sdks/python and ./sdks/typescript). Each directory is ready to publish or vendor directly into your app:

./sdks/
  python/
    setup.py
    README.md
    swain_api/
      __init__.py
      api/<ModelName>_api.py        # per-model API classes (list/create/get/update/delete)
      model/<ModelName>.py          # dataclasses for your tables
      configuration.py              # auth, base URL, timeouts
      rest.py                       # HTTP transport helpers
  typescript/
    package.json
    tsconfig.json
    src/
      apis/<ModelName>Api.ts        # strongly-typed API clients per model
      models/<ModelName>.ts         # generated TypeScript interfaces
      runtime.ts                    # shared fetch/config plumbing

Docs and test stubs stay disabled because swain_cli forwards --global-property=apiDocs=false,apiTests=false,modelDocs=false,modelTests=false by default.

Model APIs & Operations

The dynamic swagger builder expands every database model into a dedicated tag and gives each path a predictable operationId. When the SDKs are generated, those IDs become method names:

• list<Model>: GET /<model> — paginated query returning <Model>FilterResponse with data, total, page, page_size, and total_pages.
• create<Model>: POST /<model> — insert a record and return the created payload.
• getById<Model>: GET /<model>/{id} — fetch a single row by primary key.
• updateById<Model>: PUT /<model>/{id} — update and return the row.
• deleteById<Model>: DELETE /<model>/{id} — delete and return the deleted record (when the backend exposes it).
• filter<Model>/filterQuery<Model>: POST /<model>/filter and /filter/query — submit JSON filter expressions and receive the typed filter response.
• count<Model>: GET /<model>/count — lightweight count queries without fetching data.

Bulk helpers such as bulkCreate<Model> or bulkUpdate<Model> appear when the path ends in /bulk, and the generator removes the placeholder {model} parameter so each API class feels first-party.

Using the Generated SDKs

pip install ./sdks/python

from swain_api import Configuration, ApiClient
from swain_api.api import UsersApi

config = Configuration()
config.host = "https://your-api.example.com"
config.username = "user@example.com"
config.password = "super-secret"  # OAuth2 password flow

with ApiClient(config) as api_client:
    users_api = UsersApi(api_client)
    page = users_api.list_users(page=1, page_size=25)
    created = users_api.create_user({"email": "new@acme.co"})

npm install ./sdks/typescript --legacy-peer-deps

import { Configuration, UsersApi } from "./sdks/typescript";

const config = new Configuration({
  basePath: "https://your-api.example.com",
  baseOptions: { auth: { username: "user@example.com", password: "super-secret" } },
});

const usersApi = new UsersApi(config);
const { data } = await usersApi.listUsers({ page: 1, pageSize: 25 });

Advanced Filtering & Querying

Use the JSON filter system documented in filtering_process.md to build rich queries. The payload below combines field expressions, logical groups, and relationship filters:

{
  "expressions": [
    { "field": "status", "operator": "eq", "value": "active" },
    {
      "operator": "AND",
      "expressions": [
        { "field": "createdAt", "operator": "gte", "value": "2024-01-01T00:00:00Z" },
        { "field": "role", "operator": "in", "value": ["admin", "manager"] }
      ]
    },
    {
      "relationship": "Orders",
      "scope": "filterParent",
      "include": false,
      "expressions": [
        { "field": "total", "operator": "gt", "value": 500 }
      ]
    }
  ],
  "sort": [
    { "field": "createdAt", "direction": "desc" }
  ]
}

filter_payload = {
    "expressions": [
        {"field": "status", "operator": "eq", "value": "active"},
        {
            "operator": "AND",
            "expressions": [
                {"field": "createdAt", "operator": "gte", "value": "2024-01-01T00:00:00Z"},
                {"field": "role", "operator": "in", "value": ["admin", "manager"]}
            ]
        }
    ],
    "sort": [{"field": "createdAt", "direction": "desc"}]
}
page = users_api.filter_users(filter_payload=filter_payload, page=1, page_size=50)
for user in page.data:
    print(user["email"], user.get("createdAt"))

const filterPayload = {
  expressions: [
    { field: "status", operator: "eq", value: "active" },
    {
      operator: "AND",
      expressions: [
        { field: "createdAt", operator: "gte", value: "2024-01-01T00:00:00Z" },
        { field: "role", operator: "in", value: ["admin", "manager"] }
      ]
    }
  ],
  sort: [{ field: "createdAt", direction: "desc" }]
};

const { data: usersPage } = await usersApi.filterUsers(filterPayload, 1, 50);
usersPage.data.forEach((user) => {
  console.log(user.email, user.createdAt);
});

Need raw SQL-style expressions or column projections? Call filterQuery<Model> (maps to POST /{model}/filter/query) with the same payload to retrieve the generated SQL preview alongside the data.

Counts & Aggregations

POST /{model}/count returns a lightweight {"count": N} response that reuses the same filter grammar. Aggregations can be attached to filter payloads to compute grouped metrics:

{
  "expressions": [
    {
      "relationship": "Orders",
      "include": true,
      "expressions": [
        { "field": "status", "operator": "eq", "value": "completed" }
      ],
      "aggregations": {
        "functions": [{ "type": "SUM", "field": "total", "alias": "order_value" }],
        "group_by": ["user_id"]
      }
    }
  ],
  "distinct": true
}

count_payload = {
    "expressions": [{"field": "status", "operator": "eq", "value": "active"}]
}
count_response = users_api.count_users(filter_payload=count_payload)
print(f"Active users: {count_response.count}")

agg_payload = {
    "expressions": [{
        "relationship": "Orders",
        "include": True,
        "aggregations": {
            "functions": [{"type": "SUM", "field": "total", "alias": "order_value"}],
            "group_by": ["user_id"]
        }
    }]
}
report = users_api.filter_users(filter_payload=agg_payload, page=1, page_size=1)
print(report.data[0]["aggregations"]["order_value"])

const { data: count } = await usersApi.countUsers({
  expressions: [{ field: "status", operator: "eq", value: "active" }],
});
console.log(`Active users: ${count.count}`);

const aggPayload = {
  expressions: [
    {
      relationship: "Orders",
      include: true,
      aggregations: {
        functions: [{ type: "SUM", field: "total", alias: "order_value" }],
        group_by: ["user_id"],
      },
    },
  ],
};
const { data: summary } = await usersApi.filterUsers(aggPayload);
summary.data.forEach((row) => {
  console.log(row.user_id, row.aggregations?.order_value);
});

Bulk Operations & Mass Updates

CrudSQL exposes bulk create/update endpoints (/{model}/bulk) plus filter-driven mass update/delete operations (PUT/DELETE /{model}/filter). Responses follow the BulkCreateResult shape documented in controller_bulk.go with succeeded and failed arrays.

bulk_body = [
    {"email": "new-1@example.com", "password": "Secret123!"},
    {"email": "new-2@example.com", "password": "Secret123!"}
]
bulk_result = users_api.bulk_create_users(bulk_body)
for success in bulk_result.succeeded:
    print("created", success.id)
for failure in bulk_result.failed:
    print("failed", failure.error.code, failure.error.message)

update_payload = {
    "expressions": [{"field": "status", "operator": "eq", "value": "trial"}],
    "updateData": {"status": "expired"}
}
users_api.update_by_filter_users(update_filter_payload=update_payload)

users_api.delete_by_filter_users(filter_payload={
    "expressions": [{"field": "status", "operator": "eq", "value": "churned"}]
})

const bulkBody = [
  { email: "new-3@example.com", password: "Secret123!" },
  { email: "new-4@example.com", password: "Secret123!" },
];
const { data: bulk } = await usersApi.bulkCreateUsers(bulkBody);
bulk.succeeded.forEach((row) => console.log("created", row.id));
bulk.failed.forEach((row) => console.warn(row.error?.code, row.error?.message));

await usersApi.updateByFilterUsers({
  expressions: [{ field: "status", operator: "eq", value: "trial" }],
  updateData: { status: "expired" },
});

await usersApi.deleteByFilterUsers({
  expressions: [{ field: "status", operator: "eq", value: "churned" }],
});

Bulk endpoints enforce the same RLS and validation rules as single-record operations. For large updates, pair a count<Model> call with your filter first to preview the scope before mutating data.

Because the swagger is generated dynamically from your synced schema, any new table or column you map is reflected the next time you run swain_cli gen. Regenerate whenever you deploy a new build to keep SDKs in lockstep with your API.

🔐 Authentication Endpoints Usage

Once your API is deployed, you'll have access to several authentication endpoints. Here's how to use them and what's required for each to work properly.

🔑 User Registration

{
  "email": "user@example.com",
  "password": "securePassword123",
  "is_active": true,
  "additional_data": {
    "first_name": "John",
    "last_name": "Doe"
    // Any other data you want to map
  }
}

🚪 User Login

{
  "email": "user@example.com",
  "password": "securePassword123"
}

🔄 Password Reset Request

{
  "email": "user@example.com"
}

✅ Password Reset Confirmation

{
  "token": "abc123def456ghi789",
  "new_password": "newSecurePassword123"
}

📧 SMTP Configuration Examples

For password reset emails to work, you need to configure SMTP settings. Here are examples for popular email providers:

🧪 Testing Your Authentication Flow

Testing & Verification Checklist

Use this comprehensive checklist to verify that all components of your API are working correctly:

• Connection: Test DB connectivity from Connections page
• Schema: Confirm all tables, PK/FK, and types appear after Sync
• Auth: Toggle Enable Authentication and set token expiries
• RLS: Set tenant model & default tenant field; exclude public tables
• Email: Send a test reset email (SMTP settings + reset path with %s)
• User Mapping: Ensure all 9 cards are mapped
• Social: Verify OAuth callback works (login with Google/GitHub)
• Authorization: Create roles/permissions and verify restricted endpoints
• Public Routes: Confirm excluded paths are accessible without JWT
• SDK/API Keys: Create API key, call a test endpoint via SDK

❓ Troubleshooting & FAQs

🔒 Security Best Practices

• JWT Security: Use a strong JWT secret (32+ random bytes). Rotate carefully
• Token Management: Keep access tokens short-lived (e.g., 15–60 minutes). Use refresh tokens for longevity
• Password Security: Store passwords hashed using a modern algorithm (e.g., bcrypt, argon2). Never store plain text
• Route Protection: Limit the number of public routes. Prefer auth unless a route is intentionally public
• Database Access: Restrict database access by network policy and least-privilege credentials
• SMTP Security: Use app passwords or provider-scoped credentials when possible
• Audit Trails: Log admin actions and use audit trails where needed

📖 Glossary