Relations (Eager Loading)

The Data Layer resolves FK-based relations automatically. Modules declare foreign keys in their SQL migrations and call include in the SDK — the Data Layer generates an indexed LEFT JOIN at the Go level. No N+1 queries.

Relations are read-only at the include API level. Creating related records requires an explicit transaction.

---

How Relations Work

1. Module defines FK in its SQL migration:

   CREATE TABLE contacts (
     id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
     company_id UUID REFERENCES companies(id),
     tenant_id  UUID NOT NULL
   );
   CREATE INDEX ON contacts(company_id);   -- required for JOIN eligibility

2. Module calls SDK with include:

   kernel.data().list('contacts', { include: ['company'] })

3. Go Data Layer:
   → Reads FK from information_schema (cached at startup)
   → Generates LEFT JOIN (contacts + companies)
   → Returns nested object: { id, name, company: { id, name } }

The Data Layer discovers FK relationships automatically by reading information_schema.KEY_COLUMN_USAGE. The module does not declare relation metadata anywhere else — only the FK constraint in the migration is enough.

---

retrieve with include

GET https://api.septemcore.com/v1/data/crm/contacts/01j9pa5mz700000000000000?select=name,company(name,industry)
Authorization: Bearer <access_token>

Response 200 OK:

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

SDK:

const contact = await kernel.data().retrieve('contacts', id, {
  include: ['company'],
});
// contact.company.name → 'Acme Corp'

---

list with include

GET https://api.septemcore.com/v1/data/crm/contacts?select=name,company(name),tags(label)&status=eq.active
Authorization: Bearer <access_token>

Response 200 OK:

{
  "data": [
    {
      "id": "01j9pa5mz700000000000000",
      "name": "Alice Chen",
      "company": {
        "name": "Acme Corp"
      },
      "tags": [
        { "label": "enterprise" },
        { "label": "vip" }
      ]
    }
  ],
  "meta": { "cursor": "01j9pa5mz700000000000000", "hasMore": false }
}

SDK:

const { data: contacts } = await kernel.data().list('contacts', {
  include: ['company', 'tags'],
  filters: { status: 'eq.active' },
  order: 'created_at.desc',
  limit: 20,
});

---

Depth Rules

Depth	Example	Allowed
1	contacts → company	✅
2	contacts → company → industry	✅
3	contacts → company → industry → sector	❌ — 400 Bad Request

Exceeding depth 2 returns 400 Bad Request:

{
  "type": "https://api.septemcore.com/problems/validation-error",
  "status": 400,
  "detail": "include depth exceeds maximum of 2 levels",
  "code": "INCLUDE_DEPTH_EXCEEDED"
}

---

Cross-Module Restriction

A module can only include tables that belong to the same module. Joining tables from another module is not permitted — each module's data is isolated at the schema layer.

// ✅ Allowed — contacts and companies both belong to module 'crm'
await kernel.data().list('contacts', { include: ['company'] });

// ❌ Forbidden — 'orders' belongs to module 'commerce', not 'crm'
await kernel.data().list('contacts', { include: ['orders'] });
// → 403 Forbidden: cross-module include not permitted

For cross-module data composition, modules communicate via the Event Bus or expose a dedicated API endpoint.

---

include is Read-only

include works only for retrieve and list (read operations). It is not supported for create:

// ❌ This is not supported:
await kernel.data().create('contacts', {
  name: 'Alice',
  company: { name: 'Acme' },  // ignored / rejected
});

To create related records atomically, use an explicit transaction:

await kernel.data().transaction(async (tx) => {
  const company = await tx.create('companies', { name: 'Acme Corp' });
  await tx.create('contacts', {
    name: 'Alice Chen',
    company_id: company.id,
  });
  // If either create fails → full rollback
});

The transaction uses a single PostgreSQL transaction. On error, both inserts are rolled back automatically.

---

Performance Guarantees

Rule	Reason
JOIN only on indexed FK	Only columns with an index are eligible for include. Requesting an unindexed FK returns 400 Bad Request.
No include → no JOIN	Without include, the query is a plain SELECT with no joins.
LEFT JOIN semantics	Records with no matching relation row are still returned; the relation field is null.
Batch loading for list	The Data Layer batches FK resolution across all rows in a single query — no N+1 per row.

---

Error Reference

Scenario	HTTP	type URI suffix
Include depth > 2	400	validation-error (code: INCLUDE_DEPTH_EXCEEDED)
Cross-module include	403	forbidden
Unknown relation name	400	validation-error (code: UNKNOWN_RELATION)
Unindexed FK	400	validation-error (code: UNINDEXED_FK)
include on create	400	validation-error (code: INCLUDE_ON_WRITE_NOT_SUPPORTED)