All Endpoints

Complete API endpoint reference

This document provides detailed documentation for all API endpoints in SelfHostedDB.

Health & System

GET /api/health

Health check endpoint. No authentication required.

Request:

GET /api/health

Response:

{
  "status": "ok",
  "timestamp": "2025-01-27T10:00:00.000Z"
}

Status Codes:

200 - Service is healthy

License & Trial

GET /api/license/status

Get current license status. No authentication required.

Request:

GET /api/license/status

Response:

{
  "valid": true,
  "status": "active",
  "type": "paid",
  "expiresAt": null,
  "trialExpiresAt": null,
  "gracePeriodEndsAt": null
}

Status Values:

valid: Boolean indicating if license is currently valid
status: "active", "trial", "expired", "grace_period", or "invalid"
type: "paid" or "trial"
expiresAt: Expiration date for paid licenses (null for lifetime licenses)
trialExpiresAt: Expiration date for trials
gracePeriodEndsAt: End date for offline grace period

Status Codes:

200 - Success

POST /api/license/validate

Validate a license key. No authentication required.

Request:

POST /api/license/validate
Content-Type: application/json
 
{
  "licenseKey": "your-license-key-here",
  "email": "purchaser@example.com"
}

Request Body:

licenseKey (required): License key to validate
email (optional): Email address to verify against license owner

Response:

{
  "valid": true,
  "message": "License key is valid",
  "type": "paid",
  "expiresAt": null
}

Status Codes:

200 - Success (check valid field in response)
400 - Invalid request (missing licenseKey)

Example:

curl -X POST http://localhost:3001/api/license/validate \
  -H "Content-Type: application/json" \
  -d '{"licenseKey": "abc123...", "email": "user@example.com"}'

POST /api/license/activate

Activate a license key on this machine. No authentication required.

Request:

POST /api/license/activate
Content-Type: application/json
 
{
  "licenseKey": "your-license-key-here",
  "email": "purchaser@example.com"
}

Request Body:

licenseKey (required): License key to activate
email (required): Email address associated with the purchase (must match license owner)

Response:

{
  "valid": true,
  "success": true,
  "message": "License activated successfully. You can deploy on unlimited platforms."
}

Status Codes:

200 - Success (check success field in response)
400 - Invalid request (missing licenseKey or email)
500 - Server error

Important Notes:

License is tied to the purchaser's email address (prevents license sharing)
Same owner can activate on unlimited machines/platforms
License is cached locally for offline use (7-day grace period for paid, 3-day for trial)

Example:

curl -X POST http://localhost:3001/api/license/activate \
  -H "Content-Type: application/json" \
  -d '{"licenseKey": "abc123...", "email": "user@example.com"}'

POST /api/trial/start

Start a 14-day free trial. No authentication required.

Request:

POST /api/trial/start
Content-Type: application/json
 
{
  "email": "your-email@example.com"
}

Request Body:

email (required): Email address for trial activation

Response:

{
  "success": true,
  "message": "Trial started successfully",
  "trialExpiresAt": "2025-02-10T10:00:00.000Z"
}

Status Codes:

200 - Success
400 - Invalid request (missing email) or trial already started
500 - Server error

Example:

curl -X POST http://localhost:3001/api/trial/start \
  -H "Content-Type: application/json" \
  -d '{"email": "user@example.com"}'

GET /api/trial/status

Get current trial status. No authentication required.

Request:

GET /api/trial/status

Response:

{
  "active": true,
  "trialExpiresAt": "2025-02-10T10:00:00.000Z",
  "daysRemaining": 7,
  "email": "user@example.com"
}

Response Fields:

active: Boolean indicating if trial is currently active
trialExpiresAt: Expiration date/time
daysRemaining: Number of days remaining in trial
email: Email address associated with trial

Status Codes:

200 - Success

Schemas (Projects)

GET /api/schemas

List all available schemas (projects).

Request:

GET /api/schemas
Authorization: Basic <credentials>

Response:

["schema1", "schema2", "public"]

Status Codes:

200 - Success
401 - Unauthorized
500 - Server error

POST /api/schema/create

Create a new schema (project) with a dedicated user.

Request:

POST /api/schema/create
Authorization: Basic <credentials>
Content-Type: application/json
 
{
  "schemaName": "my_project",
  "password": "secure-password-123"
}

Response:

{
  "success": true,
  "message": "Schema \"my_project\" created successfully with dedicated user",
  "username": "my_project_user"
}

Validation:

schemaName: Must start with letter, only letters/numbers/underscores
password: Minimum 8 characters, no SQL injection characters

Status Codes:

201 - Created
400 - Invalid input or schema already exists
401 - Unauthorized
500 - Server error

Example:

curl -X POST http://localhost:3001/api/schema/create \
  -u admin:secret \
  -H "Content-Type: application/json" \
  -d '{"schemaName": "my_project", "password": "secure123"}'

DELETE /api/schema/:schemaName

Delete a schema (project).

Request:

DELETE /api/schema/my_project?cascade=true
Authorization: Basic <credentials>

Query Parameters:

cascade (optional): If true, deletes schema and all objects

Response:

{
  "success": true,
  "message": "Schema \"my_project\" deleted successfully"
}

Status Codes:

200 - Success
400 - Cannot delete (contains objects or system schema)
401 - Unauthorized
500 - Server error

Example:

curl -X DELETE "http://localhost:3001/api/schema/my_project?cascade=true" \
  -u admin:secret

API Keys

GET /api/project/:schema/keys

List all API keys for a schema.

Request:

GET /api/project/my_schema/keys
Authorization: Basic <credentials>

Response:

[
  {
    "id": 1,
    "key_name": "My API Key",
    "key_type": "anon",
    "key_prefix": "a1b2c3d4",
    "created_at": "2025-01-27T10:00:00.000Z",
    "last_used_at": "2025-01-27T11:00:00.000Z",
    "is_active": true
  }
]

Note: Full API key is never returned (only on creation).

Status Codes:

200 - Success
400 - Invalid schema name
401 - Unauthorized
500 - Server error

POST /api/project/:schema/keys

Create a new API key for a schema.

Request:

POST /api/project/my_schema/keys
Authorization: Basic <credentials>
Content-Type: application/json
 
{
  "keyName": "My API Key",
  "keyType": "anon"
}

Request Body:

keyName (required): Name for the API key
keyType (optional): "anon" or "service_role" (default: "anon")

Response:

{
  "id": 1,
  "key": "a1b2c3d4e5f6...64-character-hex-string",
  "keyPrefix": "a1b2c3d4",
  "keyName": "My API Key",
  "keyType": "anon",
  "schemaName": "my_schema",
  "createdAt": "2025-01-27T10:00:00.000Z"
}

Important: The full key is only returned once. Store it securely!

Status Codes:

201 - Created
400 - Invalid input or key name already exists
401 - Unauthorized
500 - Server error

Example:

curl -X POST http://localhost:3001/api/project/my_schema/keys \
  -u admin:secret \
  -H "Content-Type: application/json" \
  -d '{"keyName": "Production Key", "keyType": "service_role"}'

DELETE /api/project/:schema/keys/:keyId

Delete an API key.

Request:

DELETE /api/project/my_schema/keys/1
Authorization: Basic <credentials>

Response:

{
  "success": true,
  "message": "API key \"My API Key\" deleted successfully"
}

Status Codes:

200 - Success
404 - Key not found
401 - Unauthorized
500 - Server error

Tables

GET /api/tables

List all tables in a schema.

Request:

GET /api/tables?schema=my_schema
Authorization: Basic <credentials>

Query Parameters:

schema (optional): Schema name (default: "public")

Response:

["users", "posts", "comments"]

Status Codes:

200 - Success
400 - Invalid schema name
401 - Unauthorized
500 - Server error

Example:

curl "http://localhost:3001/api/tables?schema=my_schema" \
  -u admin:secret

GET /api/table/:tableName

Get table data with pagination.

Request:

GET /api/table/users?schema=my_schema&page=1&limit=50
Authorization: Basic <credentials>

Query Parameters:

schema (optional): Schema name (default: "public")
page (optional): Page number (default: 1)
limit (optional): Rows per page (default: 50)

Response:

{
  "data": [
    {"id": 1, "name": "John", "email": "john@example.com"},
    {"id": 2, "name": "Jane", "email": "jane@example.com"}
  ],
  "columns": ["id", "name", "email"],
  "total": 100,
  "page": 1
}

Status Codes:

200 - Success
400 - Invalid table/schema name
401 - Unauthorized
500 - Server error

Example:

curl "http://localhost:3001/api/table/users?page=1&limit=10" \
  -u admin:secret

POST /api/table/create

Create a new table.

Request:

POST /api/table/create
Authorization: Basic <credentials>
Content-Type: application/json
 
{
  "tableName": "products",
  "schema": "my_schema",
  "columns": [
    {
      "name": "id",
      "type": "INTEGER",
      "primaryKey": true,
      "nullable": false
    },
    {
      "name": "name",
      "type": "VARCHAR",
      "length": 255,
      "nullable": false
    },
    {
      "name": "price",
      "type": "DECIMAL",
      "nullable": true,
      "defaultValue": "0.00"
    }
  ]
}

Request Body:

tableName (required): Table name
schema (optional): Schema name (default: "public")
columns (required): Array of column definitions

Column Definition:

name (required): Column name
type (required): Data type (VARCHAR, INTEGER, TEXT, etc.)
length (optional): Length for VARCHAR
primaryKey (optional): Boolean
nullable (optional): Boolean (default: true)
defaultValue (optional): Default value

Response:

{
  "success": true,
  "message": "Table \"products\" created successfully"
}

Status Codes:

201 - Created
400 - Invalid input
401 - Unauthorized
500 - Server error

DELETE /api/table/:tableName

Delete a table.

Request:

DELETE /api/table/users?schema=my_schema&cascade=true
Authorization: Basic <credentials>

Query Parameters:

schema (optional): Schema name (default: "public")
cascade (optional): If true, deletes table and dependent objects

Response:

{
  "success": true,
  "message": "Table \"users\" deleted successfully"
}

Status Codes:

200 - Success
400 - Cannot delete (foreign key constraints)
401 - Unauthorized
500 - Server error

POST /api/table/:tableName/clone

Clone a table (structure and optionally data).

Request:

POST /api/table/users/clone
Authorization: Basic <credentials>
Content-Type: application/json
 
{
  "newTableName": "users_backup",
  "copyData": true,
  "schema": "my_schema"
}

Request Body:

newTableName (required): Name for new table
copyData (optional): Copy data (default: false)
schema (optional): Schema name (default: "public")

Response:

{
  "success": true,
  "message": "Table \"users_backup\" created successfully with data"
}

Status Codes:

201 - Created
400 - Invalid input or table already exists
404 - Source table not found
401 - Unauthorized
500 - Server error

GET /api/table/:tableName/export

Export table data as CSV or JSON.

Request:

GET /api/table/users/export?format=csv&limit=10000&schema=my_schema
Authorization: Basic <credentials>

Query Parameters:

format (optional): "csv" or "json" (default: "json")
limit (optional): Maximum rows to export (default: 10000, max: 10000)
schema (optional): Schema name (default: "public")

Response (JSON):

{
  "table": "users",
  "count": 100,
  "data": [...]
}

Response (CSV):

id,name,email
1,John,john@example.com
2,Jane,jane@example.com

Status Codes:

200 - Success
400 - Invalid parameters
404 - No data to export
401 - Unauthorized
500 - Server error

Table Columns

POST /api/table/:tableName/column

Add a column to an existing table.

Request:

POST /api/table/users/column
Authorization: Basic <credentials>
Content-Type: application/json
 
{
  "columnName": "phone",
  "dataType": "VARCHAR",
  "length": 20,
  "nullable": true,
  "defaultValue": null,
  "schema": "my_schema"
}

Request Body:

columnName (required): Column name
dataType (required): Data type
length (optional): Length for VARCHAR
nullable (optional): Boolean (default: true)
defaultValue (optional): Default value
schema (optional): Schema name (default: "public")

Response:

{
  "success": true,
  "message": "Column \"phone\" added to table \"users\" successfully"
}

Status Codes:

201 - Created
400 - Invalid input or column already exists
404 - Table not found
401 - Unauthorized
500 - Server error

Table Rows

POST /api/table/:tableName/row

Insert a new row into a table.

Request:

POST /api/table/users/row
Authorization: Basic <credentials>
Content-Type: application/json
 
{
  "data": {
    "name": "John Doe",
    "email": "john@example.com",
    "age": 30
  },
  "schema": "my_schema"
}

Request Body:

data (required): Object with column: value pairs
schema (optional): Schema name (default: "public")

Response:

{
  "success": true,
  "row": {
    "id": 1,
    "name": "John Doe",
    "email": "john@example.com",
    "age": 30
  },
  "message": "Row inserted successfully"
}

Status Codes:

201 - Created
400 - Invalid input
401 - Unauthorized
500 - Server error

PATCH /api/table/:tableName/:rowId

Update a single cell in a row.

Request:

PATCH /api/table/users/1
Authorization: Basic <credentials>
Content-Type: application/json
 
{
  "column": "email",
  "value": "newemail@example.com",
  "schema": "my_schema"
}

Request Body:

column (required): Column name to update
value (required): New value
schema (optional): Schema name (default: "public")

Response:

{
  "row": {
    "id": 1,
    "name": "John Doe",
    "email": "newemail@example.com"
  }
}

Status Codes:

200 - Success
400 - Invalid input
404 - Row not found
401 - Unauthorized
500 - Server error

DELETE /api/table/:tableName/row/:rowId

Delete a single row.

Request:

DELETE /api/table/users/row/1?schema=my_schema
Authorization: Basic <credentials>

Query Parameters:

schema (optional): Schema name (default: "public")

Response:

{
  "success": true,
  "message": "Row deleted successfully"
}

Status Codes:

200 - Success
404 - Row not found
401 - Unauthorized
500 - Server error

POST /api/table/:tableName/rows/delete

Bulk delete multiple rows.

Request:

POST /api/table/users/rows/delete
Authorization: Basic <credentials>
Content-Type: application/json
 
{
  "rowIds": [1, 2, 3, 4, 5],
  "schema": "my_schema"
}

Request Body:

rowIds (required): Array of row IDs to delete
schema (optional): Schema name (default: "public")

Response:

{
  "success": true,
  "deletedCount": 5,
  "message": "5 row(s) deleted successfully"
}

Status Codes:

200 - Success
400 - Invalid input
401 - Unauthorized
500 - Server error

Queries

POST /api/query

Execute a custom SQL query.

Request:

POST /api/query
Authorization: Basic <credentials>
Content-Type: application/json
 
{
  "sql": "SELECT * FROM users WHERE age > 25 LIMIT 10;",
  "schema": "my_schema"
}

Request Body:

sql (required): SQL query to execute
schema (optional): Schema name (default: "public")

Response:

{
  "rows": [
    {"id": 1, "name": "John", "age": 30},
    {"id": 2, "name": "Jane", "age": 28}
  ],
  "fields": [
    {"name": "id", "dataTypeID": 23},
    {"name": "name", "dataTypeID": 1043},
    {"name": "age", "dataTypeID": 23}
  ],
  "rowCount": 2,
  "duration": 15
}

Query Timeout: 30 seconds

Status Codes:

200 - Success
400 - Invalid SQL query
401 - Unauthorized
500 - Query execution failed

Example:

curl -X POST http://localhost:3001/api/query \
  -u admin:secret \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT COUNT(*) FROM users;", "schema": "my_schema"}'

Schema Information

GET /api/schema

Get complete schema information including tables, columns, and foreign keys.

Request:

GET /api/schema?schema=my_schema
Authorization: Basic <credentials>

Query Parameters:

schema (optional): Schema name (default: "public")

Response:

{
  "schema": [
    {
      "table": "users",
      "columns": [
        {
          "name": "id",
          "type": "integer",
          "maxLength": null,
          "nullable": false,
          "defaultValue": "nextval('users_id_seq'::regclass)",
          "primaryKey": true
        },
        {
          "name": "name",
          "type": "character varying",
          "maxLength": 255,
          "nullable": true,
          "defaultValue": null,
          "primaryKey": false
        }
      ],
      "foreignKeys": [
        {
          "column": "role_id",
          "referencedTable": "roles",
          "referencedColumn": "id",
          "constraintName": "users_role_id_fkey"
        }
      ]
    }
  ]
}

Status Codes:

200 - Success
400 - Invalid schema name
401 - Unauthorized
500 - Server error

License System Notes

License Model

SelfHostedDB uses an email-based, owner-centric license model:

License Owner: One person (identified by email - the purchaser)
Deployments: Unlimited (same owner can deploy on AWS, GCP, Docker, DigitalOcean, etc.)
Deployment Users: Unlimited team members per deployment (via Basic Auth or API Keys)
Usage: Unlimited databases/projects within PostgreSQL
Restrictions: License key tied to purchaser's email (prevents license sharing)

Offline Grace Period

The application supports offline operation with grace periods:

Paid Licenses: 7-day grace period (works offline for 7 days)
Trials: 3-day grace period (works offline for 3 days)
After grace period expires, license validation is required

License Validation Flow

Application checks local license cache on startup
If valid and within grace period, access is granted
If expired or invalid, attempts to validate with license server
If license server is unreachable, uses grace period
After grace period, access is blocked until license is validated

Authentication Security

All Endpoints

Health & System

GET /api/health

License & Trial

GET /api/license/status

POST /api/license/validate

POST /api/license/activate

POST /api/trial/start

GET /api/trial/status

Schemas (Projects)

GET /api/schemas

POST /api/schema/create

DELETE /api/schema/:schemaName

API Keys

GET /api/project/:schema/keys

POST /api/project/:schema/keys

DELETE /api/project/:schema/keys/:keyId

Tables

GET /api/tables

GET /api/table/:tableName

POST /api/table/create

DELETE /api/table/:tableName

POST /api/table/:tableName/clone

GET /api/table/:tableName/export

Table Columns

POST /api/table/:tableName/column

Table Rows

POST /api/table/:tableName/row

PATCH /api/table/:tableName/:rowId

DELETE /api/table/:tableName/row/:rowId

POST /api/table/:tableName/rows/delete

Queries

POST /api/query

Schema Information

GET /api/schema

Related Documentation

License System Notes

License Model

Offline Grace Period

License Validation Flow