Migrations & autoApi

SQL migrations via pressly/goose. Module migrations/ folder, goose up on install/update, error handling. autoApi manifest flag → auto CRUD. Post- migration permission reconciliation. Schema introspection listModels() / describeModel().

Every module manages its own schema via versioned SQL migrations. The Module Registry runs pressly/goose automatically during install and update — modules never run migrations themselves. Declaring autoApi: true on a model in the manifest is all that is needed to get five auto-generated CRUD endpoints and three RBAC permissions.

Migration File Layout

Each module scaffold contains a migrations/ directory with SQL files in goose format:

my-module/
├── module.manifest.json
├── migrations/
│   ├── 00001_create_contacts.sql
│   ├── 00002_add_status_index.sql
│   └── 00003_add_tags_table.sql
└── ...

Every .sql file uses the goose directives:

-- migrations/00001_create_contacts.sql
-- +goose Up
CREATE TABLE module_crm.contacts (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name          TEXT NOT NULL,
    email         TEXT,
    status        TEXT NOT NULL DEFAULT 'active',
    metadata      JSONB,
    tenant_id     UUID NOT NULL,        -- mandatory for every module table
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at    TIMESTAMPTZ,
    idempotency_key UUID UNIQUE
);

CREATE INDEX ON module_crm.contacts(tenant_id);
CREATE INDEX ON module_crm.contacts(status);

-- +goose Down
DROP TABLE IF EXISTS module_crm.contacts;

Table namespace: Every module's tables live in a PostgreSQL schema named module_{moduleSlug}. This guarantees that two different modules can each have a contacts table without conflict (module_crm.contacts vs module_hr.contacts). An advisory lock on the schema name prevents concurrent installation of two modules with the same slug.

Migration Lifecycle

Event	What happens
Install	Module Registry validates manifest → runs `goose up` from `migrations/` → RLS policy auto-applied to each new table → permissions registered if `autoApi: true`
Update	New module version may contain new migration files → Registry runs `goose up` (incremental only) → post-migration permission reconciliation
Deactivate	Tables remain. Data preserved. Module stops loading.
Delete	Soft delete. Data remains with a deleted marker. Full schema purge requires explicit `kernel-cli purge-module --module=crm` by Platform Owner.

Migration Failure Handling

Automatic rollback is not performed — rolling back SQL migrations can destroy tenant data irreversibly.

Situation	Behaviour
Migration N of M fails (SQL error)	goose marks migration as `pending`. Module enters `ERROR` status. All successful migrations before N remain. No automatic rollback.
Module in ERROR status	Module does not load in UI Shell. Module Registry returns `{ "status": "error", "migrationError": "..." }`. Admin sees the error in Admin → Modules → Status.
Fix path	Developer fixes SQL → publishes hotfix → `PATCH /api/v1/modules/:id` with new version → Registry runs pending migrations. Or: Admin → Modules → Retry Migration.
Manual intervention	Platform Owner via CLI: `kernel-cli migration force-version --module=crm --version=3` — clears `dirty` flag without re-running the failed migration.

autoApi — Auto-generated CRUD

Marking a model autoApi: true in module.manifest.json instructs the Data Layer to generate 5 REST endpoints when the migration completes:

{
  "name": "@acme/crm",
  "version": "1.2.0",
  "models": [
    {
      "name": "contacts",
      "table": "module_crm.contacts",
      "autoApi": true
    },
    {
      "name": "companies",
      "table": "module_crm.companies",
      "autoApi": true
    },
    {
      "name": "internal_config",
      "table": "module_crm.internal_config",
      "autoApi": false     ← not exposed — internal table
    }
  ]
}

Models without autoApi: true are never exposed via the REST API. This is an explicit opt-in — internal tables, audit tables, and configuration tables should not be autoApi: true.

Post-migration Permission Reconciliation

Every time a module update completes, the Data Layer runs a reconciliation pass to keep RBAC permissions in sync with the actual schema:

1.  goose migrations complete successfully
2.  Data Layer scans information_schema.tables
    → finds all tables in module_crm.* WHERE table_comment = 'autoApi'
3.  Compares with registered permissions for this module in IAM
4.  New tables (autoApi: true) → register crm.{model}.read/write/delete
5.  Removed tables (was autoApi, now gone) → archive crm.{model}.* in IAM
    (archived permissions: hidden from role builder, return false on
     hasPermission() even if a role still holds them)
6.  On partial migration failure:
    → reconciliation NOT run
    → retried at next healthcheck / service restart
    → env: DATA_PERMISSION_RECONCILIATION_ON_MIGRATE=true (default: true)

This prevents orphaned permissions — role builder never shows permissions for tables that no longer exist.

Schema Introspection

The Data Layer reads information_schema at service start and caches the result. The cache is refreshed after any module install/update.

List all models for the current module

GET https://api.septemcore.com/v1/data/crm/models
Authorization: Bearer <access_token>

{
  "data": [
    {
      "name": "contacts",
      "table": "module_crm.contacts",
      "autoApi": true,
      "columns": 10,
      "relations": ["companies", "tags"]
    },
    {
      "name": "companies",
      "table": "module_crm.companies",
      "autoApi": true,
      "columns": 6,
      "relations": []
    }
  ]
}

SDK:

const models = await kernel.data().listModels();
// [{ name: 'contacts', autoApi: true, ... }, ...]

Describe a specific model

GET https://api.septemcore.com/v1/data/crm/models/contacts
Authorization: Bearer <access_token>

{
  "name": "contacts",
  "table": "module_crm.contacts",
  "autoApi": true,
  "columns": [
    { "name": "id",         "type": "uuid",        "nullable": false, "pk": true },
    { "name": "name",       "type": "text",        "nullable": false },
    { "name": "email",      "type": "text",        "nullable": true  },
    { "name": "status",     "type": "text",        "nullable": false, "default": "active" },
    { "name": "metadata",   "type": "jsonb",       "nullable": true  },
    { "name": "tenant_id",  "type": "uuid",        "nullable": false },
    { "name": "created_at", "type": "timestamptz", "nullable": false },
    { "name": "updated_at", "type": "timestamptz", "nullable": false },
    { "name": "deleted_at", "type": "timestamptz", "nullable": true  }
  ],
  "indexes": [
    { "name": "contacts_tenant_id_idx", "columns": ["tenant_id"] },
    { "name": "contacts_status_idx",    "columns": ["status"] }
  ],
  "relations": [
    { "name": "company",  "fk": "company_id",  "references": "module_crm.companies" }
  ]
}

SDK:

const schema = await kernel.data().describeModel('contacts');
// schema.columns, schema.relations, schema.indexes

Required Permissions

Action	Permission
List models	`{module}.*.read` (any read permission for the module)
Describe model	`{module}.*.read`
Trigger migration retry	Admin UI only (`modules.install` system permission)
Force version via CLI	Platform Owner only

Migrations & autoApi

On this page