Decorators & Schema

All decorators are importable directly from 'pulsabase'. TypeScript experimentalDecorators and emitDecoratorMetadata must be enabled in tsconfig.json.

import { primary, text, integer, boolean, uuid, jsonb, timestamp,
         varchar, float, date, password, spatialGeometry, vector,
         column, foreignId, unique, relation, ModelSchema } from 'pulsabase';

---

Column Decorators

@primary

UUID primary key. Auto-generated on INSERT using gen_random_uuid().

@primary
id: string;

SQL: id UUID DEFAULT gen_random_uuid() PRIMARY KEY

---

@text(nullable?, defaultValue?)

Parameter	Default
nullable	true
defaultValue	undefined

@text(false)                 // NOT NULL
name: string;

@text(true, 'pending')       // nullable with DEFAULT
status: string;

---

@varchar(length, nullable?, defaultValue?)

@varchar(255, false)
slug: string;

---

@integer(nullable?, defaultValue?)

@integer(false)
age: number;

---

@float(nullable?, defaultValue?)

PostgreSQL DOUBLE PRECISION.

@float()
price: number;

---

@boolean(nullable?, defaultValue?)

@boolean(false, 'false')   // NOT NULL DEFAULT false
is_active: boolean;

---

@uuid(nullable?, defaultValue?)

For non-primary UUID columns (e.g., FK without the @foreignId constraint).

@uuid()
external_id: string;

---

@jsonb(nullable?, defaultValue?)

PostgreSQL JSONB — queryable JSON.

@jsonb()
metadata: Record<string, any>;

---

@timestamp(nullable?, defaultValue?)

@timestamp(false, 'NOW()')
published_at: string;

---

@date(nullable?, defaultValue?)

PostgreSQL DATE (date only, no time).

---

@password()

TEXT NOT NULL with automatic bcrypt hashing before storage. Value is never returned in queries.

@password()
password_hash: string;

Caution

Only use @password() for user credential tables. The column is write-only — the SDK strips it from query responses.

---

@spatialGeometry(geomType?, srid?)

PostgreSQL + PostGIS geometry column.

@spatialGeometry('Point', 4326)
location: any;

// Insert:
await pb.from(Place).insert({
  name: 'Eiffel Tower',
  location: 'SRID=4326;POINT(2.2945 48.8584)',
});

Default: geomType = 'Geometry', srid = 4326.

---

@vector(dimensions, nullable?)

PostgreSQL + pgvector column for AI embeddings.

@vector(1536)
embedding: number[];

// Insert:
await pb.from(Document).insert({ title: 'AI Guide', embedding: [0.1, 0.2, ...] });

---

@column(options)

Full control — raw column options.

@column({ type: 'decimal(10,2)', nullable: false, default: '0.00' })
amount: string;

ColumnOptions

Property	Type	Description
type	string	SQL type string
nullable	boolean	NOT NULL if false (default: true)
length	number	For varchar(n)
dimensions	number	For vector(n)
unique	boolean	Add UNIQUE constraint
default	string	SQL DEFAULT expression
primary	boolean	Mark as primary key
password	'bcrypt'	Enable bcrypt auto-hash

---

Modifier Decorators

@unique()

Add a UNIQUE constraint to any column. Can be combined with any column decorator.

@text(false)
@unique()
email: string;

---

Relation Decorator

@relation(options)

@relation({
  type: 'many-to-many',
  target: () => User,
  joinTable: 'room_members',
  foreignKey: 'room_id',
  inverseJoinColumn: 'user_id',
  pivotColumns: ['role'],
  pivotTimestamps: true,
})
members: User[];

RelationOptions

Property	Type	Description
type	'one-to-one' | 'one-to-many' | 'many-to-one' | 'many-to-many'	Relation type
target	() => ModelClass	Lazy reference to the related model (avoids circular imports)
foreignKey	string	Column on the pivot/join table pointing to the parent
joinColumn	string	Column on the parent table (defaults to 'id')
inverseJoinColumn	string	Column on the pivot pointing to the related record
joinTable	string	Pivot table name (M2M only)
pivotColumns	string[]	Extra columns to include from the pivot table
pivotTimestamps	boolean	Include created_at/updated_at from the pivot

---

Foreign Key Decorator

@foreignId(options)

Declares a UUID column with a PostgreSQL FOREIGN KEY constraint.

@column({ type: 'uuid', nullable: false })
@foreignId({ references: 'id', on: 'users', onDelete: 'CASCADE' })
user_id: string;

Option	Type	Options
references	string	Column on the referenced table (usually 'id')
on	string	Referenced table name
onDelete	string	'CASCADE' | 'SET NULL' | 'RESTRICT' | 'NO ACTION'
onUpdate	string	Same options as onDelete

---

ModelSchema

The static schema block is the most critical part of any model — it defines the table, real-time behavior, indexes, validation, and Row-Level Security policies that gate every database operation.

See also

• Row-Level Security guide — deep dive into policy behavior and JWT integration
• Validation Rules reference — full rules.* and crossRules.* reference

Top-Level Properties

Property	Type	Required	Description
tableName	string		PostgreSQL table name
timestamps	boolean	—	Auto-manage created_at / updated_at with a DB trigger
publication	boolean	—	Include in PostgreSQL logical replication publication (enables real-time for this table)
access	string	—	Shorthand RLS preset — generates policies automatically (cannot be combined with policies)
indexes	IndexDefinition[]	—	Custom database indexes
validation	object	—	Per-field client-side validation rules (see Validation Rules)
crossValidation	CrossValidationRule[]	—	Cross-field validation rules (fire before the request is sent)
policies	PolicyDefinition[]	—	Explicit RLS policies — full control over who can do what

---

Indexes (IndexDefinition[])

Indexes are declared in the model and applied to the database by db:sync. They control query performance — always index columns used in frequent WHERE, JOIN, or ORDER BY clauses.

import { IndexDefinition } from 'pulsabase';

static schema: ModelSchema = {
  tableName: 'posts',
  indexes: [
    // ... examples below
  ],
};

IndexDefinition Properties

Property	Type	Default	Description
columns	string[]	—	Required. Columns to include in the index
unique	boolean	false	UNIQUE constraint — prevents duplicate values
name	string	auto	Custom index name. Auto-generated as idx_{table}_{cols} if omitted
type	string	'BTREE'	Index method — passed directly as USING {type} in DDL. Any type PostgreSQL supports works.
opclass	string	—	Operator class — appended to each column definition (e.g. vector_cosine_ops)
where	string	—	SQL condition for a partial index — only matching rows are indexed

Index Types & Examples

Standard BTREE (default)

The default PostgreSQL index. Optimal for equality checks, range queries, and sorting.

// Single column — most common case
{ columns: ['email'], unique: true }            // → idx_users_email_unique
{ columns: ['status'] }                         // → idx_posts_status
{ columns: ['user_id', 'created_at'] }          // Composite: filter by user, sort by date

Generated DDL: CREATE INDEX "idx_posts_status" ON "posts" ("status");

Partial Index (where)

Index only a subset of rows. Much smaller and faster when your queries always include a fixed filter.

// Only index active users — queries filtering `status = 'active'` get a tiny, fast index
{ columns: ['email'], where: "status = 'active'" }

// Only index unpublished posts — for admin queues
{ columns: ['created_at'], where: "published = false" }

// Only index soft-deleted records
{ columns: ['deleted_at'], where: 'deleted_at IS NOT NULL' }

Generated DDL: CREATE INDEX "idx_users_email" ON "users" ("email") WHERE status = 'active';

Unique Composite Index

Enforce uniqueness across multiple columns (e.g., one membership per user per room).

// A user can only be in a room once
{ columns: ['room_id', 'user_id'], unique: true }

// A user can only like a post once
{ columns: ['user_id', 'post_id'], unique: true }

GIN (Generalized Inverted Index)

Use for JSONB columns (querying nested fields) or full-text search (tsvector).

// Index a JSONB column for fast key/value lookups
{ columns: ['metadata'], type: 'GIN' }

// Full-text: index a tsvector generated column
{ columns: ['search_vector'], type: 'GIN' }

GIST (Generalized Search Tree)

Use for geometry columns (PostGIS spatial queries) or range types.

// Spatial index — required for ST_DWithin, ST_Intersects, etc. to be fast
{ columns: ['location'], type: 'GIST' }

// Multiple geometry columns
{ columns: ['pickup_location'], type: 'GIST' }
{ columns: ['dropoff_location'], type: 'GIST' }

Without a GIST index, spatial queries perform a full table scan. Always add one to geometry columns.

HNSW (Hierarchical Navigable Small World)

The recommended index for vector columns with pgvector. Enables fast approximate nearest-neighbor (ANN) search.

// L2 distance (Euclidean) — for OpenAI text-embedding-ada-002 (1536 dims)
{
  columns: ['embedding'],
  type: 'HNSW',
  opclass: 'vector_l2_ops',
}

// Cosine distance — for normalized embeddings (recommended for semantic search)
{
  columns: ['embedding'],
  type: 'HNSW',
  opclass: 'vector_cosine_ops',
}

// Inner product — for dot-product similarity
{
  columns: ['embedding'],
  type: 'HNSW',
  opclass: 'vector_ip_ops',
}

Operator class	Distance metric	SDK operator
vector_l2_ops	Euclidean (L2)	<->
vector_cosine_ops	Cosine distance	<=>
vector_ip_ops	Negative inner product	<#>

IVFFlat (alternative to HNSW for large datasets)

{
  columns: ['embedding'],
  type: 'IVFFlat',
  opclass: 'vector_cosine_ops',
}

Use HNSW for most cases. IVFFlat requires a training phase (VACUUM ANALYZE) after initial data load.

BRIN (Block Range Index)

Extremely compact index for very large, append-only tables (logs, events, time series). Only useful when rows are inserted in physical order (e.g., by created_at).

// Great for event/audit log tables with millions of rows
{ columns: ['created_at'], type: 'BRIN' }

---

Row-Level Security (policies: PolicyDefinition[])

This is the security boundary

RLS policies are enforced at the PostgreSQL level — they cannot be bypassed by the SDK or any direct query. Every SELECT, INSERT, UPDATE, and DELETE is filtered by these policies based on the user’s JWT claims.

See also

Row-Level Security guide — full explanation of auth modes (NONE, IDP, JWKS), JWT claim mapping, and advanced policy patterns.

static schema: ModelSchema = {
  tableName: 'posts',
  policies: [
    // ... examples below
  ],
};

PolicyDefinition Properties

Property	Type	Description
action	('SELECT' | 'INSERT' | 'UPDATE' | 'DELETE')[]	Required. SQL operations this policy applies to
public	boolean	Allow access with no JWT (anonymous/public access)
role	string | string[]	Required IDP role(s). Multiple roles = OR (any one is sufficient)
permission	string | string[]	Required IDP permission(s). Multiple = OR
owner	string	Column that must equal jwt.sub (current user’s ID)
memberOf	string	FK column — user must be a member of the group via a pivot table
pivotWhere	Record<string, any>	Extra condition on the pivot row (e.g., { role: 'admin' })

Logic rules:

• Between different fields: AND — all specified conditions must be satisfied
• Within role / permission arrays: OR — any one value satisfies the condition

Policy Patterns

Public read — no login required

{ action: ['SELECT'], public: true }

Generated SQL: USING (true) — no restriction.

Authenticated — any signed-in user

{ action: ['SELECT', 'INSERT', 'UPDATE', 'DELETE'], role: 'authenticated' }

Generated SQL:

USING (
  current_setting('request.jwt.claims', true)::json->>'roles' LIKE '%"authenticated"%'
)

Owner-only — user can only touch their own rows

{ action: ['SELECT', 'UPDATE', 'DELETE'], owner: 'user_id' }

Generated SQL:

USING (
  "user_id"::text = current_setting('request.jwt.claims', true)::json->>'sub'
)

The owner value is the column name on the table that stores the user’s ID. It is compared against the sub claim from the JWT.

Insert with automatic owner binding

{ action: ['INSERT'], owner: 'user_id' }

Generated SQL:

WITH CHECK (
  "user_id"::text = current_setting('request.jwt.claims', true)::json->>'sub'
)

Prevents any user from inserting rows on behalf of someone else.

Role-based access

// Single role
{ action: ['DELETE'], role: 'admin' }

// Any of multiple roles (OR)
{ action: ['UPDATE'], role: ['admin', 'moderator'] }

Generated SQL (single role):

USING (
  current_setting('request.jwt.claims', true)::json->>'roles' LIKE '%"admin"%'
)

Generated SQL (multiple roles — OR):

USING (
  current_setting('request.jwt.claims', true)::json->>'roles' LIKE '%"admin"%'
  OR current_setting('request.jwt.claims', true)::json->>'roles' LIKE '%"moderator"%'
)

Permission-based access

// Single permission
{ action: ['INSERT', 'UPDATE'], permission: 'content.write' }

// Any of multiple permissions (OR — one is sufficient)
{ action: ['DELETE'], permission: ['content.delete', 'content.admin'] }

Generated SQL (single):

USING (
  current_setting('request.jwt.claims', true)::json->>'permissions' LIKE '%"content.write"%'
)

Generated SQL (multiple — OR):

USING (
  current_setting('request.jwt.claims', true)::json->>'permissions' LIKE '%"content.delete"%'
  OR current_setting('request.jwt.claims', true)::json->>'permissions' LIKE '%"content.admin"%'
)

Admin OR owner (combining role and owner)

// Admin can manage all; owners can manage only their own
{ action: ['UPDATE', 'DELETE'], role: 'admin' },
{ action: ['UPDATE', 'DELETE'], owner: 'user_id' },

Two separate policy entries are OR-ed by PostgreSQL — a row passes if any policy matches.

Group membership via pivot table

// Only room members can read messages
{ action: ['SELECT'], memberOf: 'room_id' }

Generated SQL:

USING (
  EXISTS (
    SELECT 1 FROM "messages" AS _membership_check
    WHERE _membership_check."room_id" = "room_id"
    AND _membership_check."user_id" = (current_setting('request.jwt.claims', true)::json->>'sub')::uuid
  )
)

Group membership with pivot condition

// Only room admins can delete messages
{ action: ['DELETE'], memberOf: 'room_id', pivotWhere: { role: 'admin' } }

Generated SQL:

USING (
  EXISTS (
    SELECT 1 FROM "messages" AS _membership_check
    WHERE _membership_check."room_id" = "room_id"
    AND _membership_check."user_id" = (current_setting('request.jwt.claims', true)::json->>'sub')::uuid
    AND _membership_check."role" = 'admin'
  )
)

Complete Real-World Example

// A social media post — public read, owner write, admin override
static schema: ModelSchema = {
  tableName: 'posts',
  timestamps: true,
  publication: true,        // Enable real-time
  indexes: [
    { columns: ['user_id'] },
    { columns: ['status', 'created_at'] },
    // Full-text search index on title + body
    { columns: ['search_vector'], type: 'GIN' },
  ],
  policies: [
    // Anyone (even logged out) can read published posts
    { action: ['SELECT'], public: true },

    // Only authenticated users can create posts
    { action: ['INSERT'], role: 'authenticated', owner: 'user_id' },

    // Only the author can edit or delete their own posts
    { action: ['UPDATE', 'DELETE'], owner: 'user_id' },

    // Admins can do anything
    { action: ['SELECT', 'INSERT', 'UPDATE', 'DELETE'], role: 'admin' },
  ],
};

access Presets (shorthand — cannot combine with policies)

When using access, the CLI generates a standard set of policies automatically. Use policies directly when you need custom logic.

Value	Equivalent Policies Generated
'public'	SELECT public: true | INSERT/UPDATE/DELETE role: ‘authenticated’
'private'	All actions: owner: ‘user_id’ (requires a user_id column)
'authenticated'	All actions: role: ‘authenticated’
'members-read'	SELECT: memberOf FK column
'members-manage'	All actions: memberOf FK column