Query Language

The Data Layer query language is inspired by PostgREST — the industry standard for auto-generated REST APIs over relational databases. Filters are expressed as query parameters in the format ?{field}={operator}.{value}. All values are parameterised through sqlc before reaching PostgreSQL — SQL injection is structurally impossible.

Unknown operators or field names return 400 Bad Request (RFC 9457 type: ".../problems/validation-error") before any DB query is executed.

---

Filter Operators

Operator	Example	SQL equivalent
eq	?status=eq.active	WHERE status = 'active'
neq	?status=neq.deleted	WHERE status != 'deleted'
gt	?price=gt.100	WHERE price > 100
gte	?price=gte.100	WHERE price >= 100
lt	?price=lt.50	WHERE price < 50
lte	?price=lte.50	WHERE price <= 50
ilike	?name=ilike.alice*	WHERE name ILIKE 'alice%'
in	?status=in.(active,pending)	WHERE status IN ('active', 'pending')
is	?deleted_at=is.null	WHERE deleted_at IS NULL

ilike pattern matching

The * character is treated as the SQL % wildcard. The query is always case-insensitive (ILIKE). A case-sensitive like operator is not exposed — ilike covers all practical use cases without risking encoding or collation surprises.

?name=ilike.iphone*      →  WHERE name ILIKE 'iphone%'
?name=ilike.*pro*        →  WHERE name ILIKE '%pro%'
?name=ilike.*book        →  WHERE name ILIKE '%book'

in value list

Values in the in operator are comma-separated, enclosed in parentheses:

GET https://api.septemcore.com/v1/data/crm/contacts?status=in.(active,pending,trial)

WHERE status IN ('active', 'pending', 'trial')

Null checks

GET https://api.septemcore.com/v1/data/crm/contacts?deleted_at=is.null
GET https://api.septemcore.com/v1/data/crm/contacts?company_id=is.not_null

---

Multiple Filters (AND)

Multiple filter parameters are combined with implicit AND:

GET https://api.septemcore.com/v1/data/crm/contacts?status=eq.active&price=gt.100&name=ilike.alice*

WHERE status = 'active'
  AND price > 100
  AND name ILIKE 'alice%'

Limit: Maximum 10 filter parameters per request. Exceeding this limit returns 400 Bad Request with code: FILTER_LIMIT_EXCEEDED. This protects against cost-of-query attacks via complex WHERE clauses.

---

Logical OR

The or parameter accepts a comma-separated list of conditions in parentheses:

GET https://api.septemcore.com/v1/data/crm/contacts?or=(status.eq.active,status.eq.trial)

WHERE (status = 'active' OR status = 'trial')

OR conditions can be combined with AND filters:

GET https://api.septemcore.com/v1/data/crm/contacts?category=eq.hardware&or=(price.lt.50,status.eq.sale)

WHERE category = 'hardware'
  AND (price < 50 OR status = 'sale')

---

Ordering

The order parameter accepts one or more {field}.{direction} pairs, comma-separated:

GET https://api.septemcore.com/v1/data/crm/contacts?order=created_at.desc,name.asc

ORDER BY created_at DESC, name ASC

Direction	Value
Descending	.desc
Ascending	.asc

Only indexed columns are allowed in order to prevent full table scans. Ordering by an unindexed column returns 400 Bad Request with code: UNINDEXED_ORDER_FIELD.

---

Field Selection

The select parameter limits which fields are returned in the response. Use it to reduce payload size when only a subset of columns is needed:

GET https://api.septemcore.com/v1/data/crm/contacts?select=name,email,status

Response includes only the selected fields plus id (always present):

{
  "data": [
    {
      "id": "01j9pa5mz700000000000000",
      "name": "Alice Chen",
      "email": "[email protected]",
      "status": "active"
    }
  ],
  "meta": { "cursor": "01j9pa5mz700000000000000", "hasMore": false }
}

Requesting a non-existent field returns 400 Bad Request with code: UNKNOWN_FIELD.

---

Eager Loading (Relations)

The select parameter uses a nested parentheses syntax to include FK-related records. A module's contacts table with a company_id FK to companies:

GET https://api.septemcore.com/v1/data/crm/contacts?select=name,company(name,industry)

{
  "data": [
    {
      "id": "01j9pa5mz700000000000000",
      "name": "Alice Chen",
      "company": {
        "name": "Acme Corp",
        "industry": "Technology"
      }
    }
  ]
}

The Data Layer resolves the FK and generates a LEFT JOIN using the indexed foreign key. Maximum nesting depth is 2 levels:

contacts → company             ✅  depth 1
contacts → company → industry  ✅  depth 2
contacts → company → industry → sector  ❌  depth 3 — rejected

Cross-module relations (joining tables from a different module) are not permitted. Only tables belonging to the same module can be included.

---

SQL Injection Prevention

All filter values, order fields, field names, and relation identifiers are validated against the schema before query construction:

// internal/query/parser.go (simplified)
func (p *Parser) ParseFilter(field, operator, value string) (Filter, error) {
    // Step 1: validate field exists in schema (whitelist)
    if !p.schema.HasColumn(field) {
        return Filter{}, ErrUnknownField
    }
    // Step 2: validate operator is in allowed set
    if !allowedOperators[operator] {
        return Filter{}, ErrUnknownOperator
    }
    // Step 3: value is passed as a sqlc parameter — never interpolated
    return Filter{Column: field, Op: operator, Value: value}, nil
}

The query is then assembled through sqlc-generated code where all user-supplied values are bound as $N parameters. String interpolation into SQL is structurally impossible — the query template and parameter list are always separate.

---

Complete Example

List active contacts, sort by creation date descending, return only name and email, 20 per page:

GET https://api.septemcore.com/v1/data/crm/contacts
    ?status=eq.active
    &order=created_at.desc
    &select=name,email
    &limit=20
Authorization: Bearer <access_token>

List contacts from either "active" or "trial" status, in the "hardware" category, sorted by price:

GET https://api.septemcore.com/v1/data/crm/contacts
    ?category=eq.hardware
    &or=(status.eq.active,status.eq.trial)
    &order=price.asc
    &limit=50
Authorization: Bearer <access_token>

---

SDK

import { kernel } from '@platform/sdk-core';

const data = kernel.data();

// Filters
const { data: contacts } = await data.list('contacts', {
  filters: {
    status: 'eq.active',
    price: 'gt.100',
  },
  order: 'created_at.desc',
  limit: 20,
});

// Logical OR
const { data: results } = await data.list('contacts', {
  or: ['status.eq.active', 'status.eq.trial'],
  limit: 50,
});

// Field selection
const { data: slim } = await data.list('contacts', {
  select: ['name', 'email', 'status'],
  limit: 100,
});

// Eager loading FK relation
const { data: rich } = await data.list('contacts', {
  select: ['name', { company: ['name', 'industry'] }],
});

---

Limits

Parameter	Limit	Override
Max filters per request	10	Not overridable
Max limit per page	100	Not overridable
Default limit	20	Module can set lower default
Max relation depth (select)	2	Not overridable

All limit violations return 400 Bad Request (RFC 9457) before any database query is issued.