Constructive Data Modeling

Constructive Data Modeling

Tables, fields, relations, constraints, and indexes — the full schema lifecycle via the type-safe SDK. Everything compiles to PostgreSQL DDL through Constructive's metaschema layer.

When to Apply

Use this skill when:

• Creating tables, fields, relations, constraints, or indexes via the SDK
• Provisioning databases with module selection
• Defining enum types
• Configuring field validation (regexp, min, max)
• Setting api_required on nullable FK columns
• Understanding the composition: table → fields → constraints → indexes → relations → security

The Composition Flow

1. Provision database  → db.databaseProvisionModule.create({ modules: ['all'] })
2. Create table        → db.secureTableProvision.create({ tableName, nodeType, ... })
3. Add fields          → db.field.create({ tableId, name, type, ... })
4. Add constraints     → db.checkConstraint.create / db.foreignKeyConstraint.create
5. Add indexes         → db.index.create({ tableId, fieldIds, ... })
6. Add relations       → db.relationProvision.create({ fromTableId, toTableId, ... })
7. Apply security      → see constructive-security skill

Database Provisioning

const result = await db.databaseProvisionModule.create({
  data: {
    databaseName: 'my-app',
    ownerId: userId,
    subdomain: 'my-app',
    domain: 'localhost',
    modules: ['all'],
    bootstrapUser: true,
  },
  select: { id: true, databaseId: true, status: true },
}).execute();

See provisioning.md for the full provisioning flow.

Tables

Create tables via secureTableProvision (recommended) or db.table.create:

await db.secureTableProvision.create({
  data: {
    databaseId,
    tableName: 'projects',
    nodeType: 'DataEntityMembership',
    useRls: true,
    grantRoles: ['authenticated'],
    grantPrivileges: [['select', '*'], ['insert', '*'], ['update', '*'], ['delete', '*']] as unknown as Record<string, unknown>,
    policyType: 'AuthzEntityMembership',
    policyPermissive: true,
    policyData: { entity_field: 'entity_id', membership_type: 2 },
  },
  select: { id: true, tableId: true, outFields: true },
}).execute();

For full table management operations, see the generated orm-* skills in constructive-db.

Fields

await db.field.create({
  data: {
    databaseId,
    tableId,
    name: 'status',
    type: { name: 'project_status' },  // enum type
    defaultValue: { value: 'draft' },
    isRequired: true,
  },
  select: { id: true },
}).execute();

Field types include: text, integer, bigint, boolean, uuid, jsonb, timestamptz, date, numeric, citext, ltree, vector(N), and custom enums.

See field-types.md for the complete type reference.

Enum Types

await db.enum.create({
  data: {
    databaseId,
    schemaName: 'app_public',
    name: 'project_status',
    values: ['draft', 'active', 'archived'],
  },
  select: { id: true, name: true, values: true },
}).execute();

Relations

Four relation types via db.relationProvision.create:

await db.relationProvision.create({
  data: {
    databaseId,
    fromTableId: projectsTableId,
    toTableId: organizationsTableId,
    fromFieldName: 'organization_id',
    apiRequired: true,
    cascadeDelete: 'no_action',
  },
  select: { id: true },
}).execute();

Constraints

Check constraints and foreign keys via db.checkConstraint.create and db.foreignKeyConstraint.create.

Indexes

await db.index.create({
  data: {
    databaseId,
    tableId,
    fieldIds: [fieldId],
    isUnique: true,
    accessMethod: 'btree',  // or 'gin', 'gist', 'hash'
  },
  select: { id: true },
}).execute();

api_required (Required API Fields)

For nullable FK columns that should be required at the GraphQL API level:

await db.field.update({
  where: { id: fieldId },
  data: { apiRequired: true },
  select: { id: true },
}).execute();

References

Cross-References

• Security (RLS, grants, policies): `constructive-security`
• Blueprint definitions: `constructive-blueprints`
• Generated ORM API: `constructive-orm`
• Code generation pipeline: `constructive-codegen`

FieldType & FieldDefault Reference

Structured JSONB representations of PostgreSQL types and default value expressions. Stored in metaschema_public.field.type and metaschema_public.field.default_value.

TypeScript interfaces: FieldType, FieldDefault from @constructive-io/node-type-registry.

---

FieldType

interface FieldType {
  name: string;               // SQL type name (required)
  schema?: string;            // schema qualifier
  args?: (string | number | boolean)[];  // type arguments
  array_dimensions?: number;  // 1 = text[], 2 = text[][]
  range?: string[];           // interval field range
}

Examples

---

FieldDefault

type FieldDefaultArg = string | number | boolean | null | FieldDefault;

interface FieldDefault {
  value?: string | number | boolean | null | unknown[] | Record<string, unknown>;
  function?: string;
  schema?: string;
  args?: FieldDefaultArg[];
  cast?: FieldType;         // reuses FieldType shape
  operator?: string;
  left?: FieldDefault;
  right?: FieldDefault;
  sql_keyword?: string;
}

Literal values

Cast expressions

Function calls

Function calls with arguments

Operator expressions

SQL keywords

---

Blueprint usage

{
  "fields": [
    { "name": "title", "type": { "name": "text" }, "is_required": true },
    { "name": "status", "type": { "name": "text" }, "default": { "value": "draft" } },
    { "name": "metadata", "type": { "name": "jsonb" }, "default": { "value": {}, "cast": { "name": "jsonb" } } },
    { "name": "tags", "type": { "name": "citext", "array_dimensions": 1 }, "default": { "value": [], "cast": { "name": "citext", "array_dimensions": 1 } } },
    { "name": "expires_at", "type": { "name": "timestamptz" }, "default": { "operator": "+", "left": { "function": "now" }, "right": { "value": "5 minutes", "cast": { "name": "interval" } } } }
  ]
}

SDK usage

await db.field.create({
  data: {
    databaseId,
    tableId,
    name: 'tags',
    type: { name: 'citext', array_dimensions: 1 },
    defaultValue: { value: [], cast: { name: 'citext', array_dimensions: 1 } },
    isRequired: true,
  },
  select: { id: true, name: true, type: true },
}).execute();

Validation

The validate_field_type and validate_field_default triggers on metaschema_public.field reject non-object inputs. Passing "type": "text" (a JSON string) returns:

FieldType must be an object, got string

Always use the object format: "type": { "name": "text" }.

Database Provisioning (End-to-End)

Client Setup

import { createClient as createAuthClient }   from '@constructive-db/sdk/auth';
import { createClient as createPublicClient } from '@constructive-db/sdk/public';

const authDb   = createAuthClient({ endpoint: 'http://auth.localhost:3000/graphql' });
const publicDb = createPublicClient({ endpoint: 'http://api.localhost:3000/graphql' });

Step 1: Sign Up + Sign In

await authDb.mutation.signUp({ input: { email, password } }, { select: { ok: true, errors: true } }).execute();

const signIn = await authDb.mutation.signIn(
  { input: { email, password } },
  { select: { result: { select: { accessToken: true, userId: true } } } }
).execute();
const { accessToken, userId } = signIn.signIn.result;

Step 2: Provision Database

Always use modules: ['all'] and bootstrapUser: true:

publicDb.setHeaders({ Authorization: `Bearer ${accessToken}` });

const result = await publicDb.databaseProvisionModule.create({
  data: {
    databaseName: dbName,
    ownerId: userId,
    subdomain: dbName,
    domain: 'localhost',
    modules: ['all'],
    bootstrapUser: true,
  },
  select: { id: true, databaseId: true, databaseName: true, status: true }
}).execute();

const dbId = result.createDatabaseProvisionModule?.databaseProvisionModule?.databaseId;

Step 3: Apply Workarounds

See workarounds/fix-membership-defaults and workarounds/auto-verify-email.

Step 4: Per-DB Sign In

const dbAuth = createAuthClient({
  endpoint: `http://auth-${dbName}.localhost:3000/graphql`
});
const dbSignIn = await dbAuth.mutation.signIn(
  { input: { email, password } },
  { select: { result: { select: { accessToken: true, userId: true } } } }
).execute();
const dbAccessToken = dbSignIn.signIn.result.accessToken;

Step 5: Use Per-DB App API

import { createClient } from './generated/<db-name>/sdk/orm';

const db = createClient({
  endpoint: `http://app-public-${dbName}.localhost:3000/graphql`,
  headers: { Authorization: `Bearer ${dbAccessToken}` },
});

await db.notes.create({ data: { content: 'Hello' }, select: { id: true } }).execute();

Module Reference