Schema Conventions Specification

Version: 1.0 Status: Stable Audience: Database architects, schema designers, DBAs

---

1. Overview

FraiseQL is a runtime GraphQL framework for PostgreSQL. It relies on a small set of opinionated schema conventions so that, at application startup, it can build the GraphQL schema in memory and serve it over FastAPI. There is no build or code-generation step — the runtime reads your views and functions directly.

Core principle: The database schema is the source of truth. Your Python decorators declare which views to expose as GraphQL types and which functions back your mutations; the conventions below let FraiseQL map them efficiently.

These conventions are PostgreSQL-specific. FraiseQL v1 targets PostgreSQL only.

---

2. Naming Conventions

2.1 Table Naming

Pattern	Purpose	Example	Notes
tb_{entity}	Write table (normalized)	tb_user, tb_post, tb_order_item	Singular entity name
tb_{entity}_{relationship}	Junction/bridge tables	tb_user_role, tb_product_tag	For many-to-many

Rules:

• Must be lowercase
• Must use snake_case
• No abbreviations (use tb_customer, not tb_cust)
• Entity name should be singular
• A table exists only if there is a write model for the entity

Write tables are the normalized source of truth. They are never exposed directly to GraphQL — queries read from views, and mutations write through fn_ functions.

2.2 View Naming

Pattern	Purpose	Example
v_{entity}	Logical read view producing a data JSONB column	v_user, v_post
v_{entities}_by_{parent}	Pre-aggregated composition view	v_posts_by_user, v_order_items_by_order
tv_{entity}	Table-backed projection view (real table holding pre-composed JSONB)	tv_user_summary
mv_{entity}	Materialized view (cached)	mv_user_stats

Rules:

• Read views (v_, tv_) MUST produce a data JSONB column
• Pre-aggregated views group by the internal foreign key
• View names must be lowercase snake_case

When to use each pattern:

See the View Selection Guide for detailed decision trees, and the tv_ table pattern for table-backed projections:

• v_* is a logical view: the SQL runs on every query, composing the data JSONB on the fly. Best for simple to moderate reads.
• tv_* is a real table holding pre-composed JSONB, refreshed by functions or triggers. Best for heavy, deeply nested reads where re-composing JSONB on every query would be too slow.

Quick decision:

• Simple to moderate queries → v_* (logical view)
• Complex queries with 3+ JOINs or deep nesting → tv_* (table-backed projection)

2.3 Function Naming (Stored Procedures)

Pattern	Purpose	Example
fn_{action}_{entity}	Write function (mutation)	fn_create_user, fn_update_post, fn_delete_order
fn_{verb}_{entity}_{detail}	Complex operations	fn_archive_user_posts, fn_transfer_order_items

Rules:

• Must be lowercase snake_case
• Action verbs: create, update, delete, upsert, archive, etc.
• Function returns JSON with the result data (see section 5)
• Function is transaction-safe

All mutation business logic lives in these PostgreSQL functions. A @fraiseql.mutation resolver calls them via db.execute_function("fn_...", {...}).

2.4 Constraint Naming

Type	Pattern	Example
Primary Key	{table}_pkey	tb_user_pkey (PostgreSQL default)
Foreign Key	fk_{table}_{referenced_table}	fk_post_user
Unique	{table}_{columns}_key	tb_user_email_key
Check	ck_{table}_{condition}	ck_order_amount_positive
Index	idx_{table}_{columns}	idx_post_created_at

For a complete listing of identifier patterns, see Naming Patterns.

---

3. Column Conventions

3.1 The Trinity Identifier Pattern

Every entity carries three identifiers with distinct roles:

• pk_{entity} — internal BIGINT primary key. Used for fast joins. Never exposed in GraphQL.
• id — public UUID column. Stable external identity, exposed as the GraphQL id.
• identifier — optional human-readable TEXT UNIQUE slug. Exposed when present.

-- Write table (tb_*)
CREATE TABLE tb_user (
    pk_user BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    identifier TEXT NOT NULL UNIQUE
);

-- Related write table
CREATE TABLE tb_post (
    pk_post BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    fk_user BIGINT NOT NULL REFERENCES tb_user(pk_user)
);

Rules:

• Primary key: pk_{entity} (BIGINT, auto-generated)
• Foreign key: fk_{entity} (BIGINT, references pk_*)
• Public ID: id (UUID, exposed via GraphQL)
• Human-readable slug: identifier (TEXT, indexed)

Why this dual-key strategy?

Key	Type	Purpose	Index
pk_user	BIGINT	Internal joins, max performance	PRIMARY KEY
id	UUID	External references	UNIQUE
identifier	TEXT	Human-readable URLs	UNIQUE

GraphQL exposes id: ID! and optionally identifier: String, never pk_*. Keep pk_* and fk_* out of the data JSONB entirely.

3.2 Filterable Foreign Keys in Views

-- View includes a UUID FK column for efficient filtering
CREATE VIEW v_post AS
SELECT
    id,
    (SELECT id FROM tb_user WHERE pk_user = fk_user) AS user_id,
    jsonb_build_object(
        'id', id,
        'title', title,
        'userId', (SELECT id FROM tb_user WHERE pk_user = fk_user),
        'createdAt', created_at
    ) AS data
FROM tb_post
WHERE deleted_at IS NULL;

-- Index the foreign key column for fast filtering
CREATE INDEX idx_v_post_user_id ON tb_post(fk_user);

Rules:

• Related views expose {parent}_id as a native column
• This column is UUID (matches the public id)
• Always indexed for fast filtering
• Appears in both the data JSONB and as a native column

3.3 Deep Path Filter Columns

For frequently-filtered nested paths, add denormalized columns:

CREATE VIEW v_order AS
SELECT
    o.id,

    -- Direct relationships
    user_id,

    -- Deep paths (denormalized for filtering)
    paths.items__product__category_id,
    paths.items__product__vendor_id,
    paths.items__product__tag_ids,

    jsonb_build_object(
        'id', o.id
        -- ... remaining fields ...
    ) AS data
FROM tb_order o
LEFT JOIN LATERAL (
    SELECT
        array_agg(DISTINCT c.id) AS items__product__category_id,
        array_agg(DISTINCT v.id) AS items__product__vendor_id,
        array_agg(DISTINCT t.id) AS items__product__tag_ids
    FROM tb_order_item oi
    JOIN tb_product p ON p.pk_product = oi.fk_product
    LEFT JOIN tb_category c ON c.pk_category = p.fk_category
    LEFT JOIN tb_vendor v ON v.pk_vendor = p.fk_vendor
    LEFT JOIN tb_product_tag pt ON pt.fk_product = p.pk_product
    LEFT JOIN tb_tag t ON t.pk_tag = pt.fk_tag
    WHERE oi.fk_order = o.pk_order
) paths ON true
WHERE o.deleted_at IS NULL;

-- Index each path column (GIN for array columns)
CREATE INDEX idx_tb_order_category
  ON tb_order_item(fk_product);

Rules:

• Column name = GraphQL filter path with __ separators
• Singular relationships: UUID type
• Array relationships: UUID[] type
• Must be indexed (B-tree for UUID, GIN for UUID[])
• FraiseQL reads these columns at startup and offers them as filter arguments

3.4 Audit Columns

All write tables SHOULD include audit columns:

CREATE TABLE tb_user (
    pk_user BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    email TEXT NOT NULL,

    -- Audit columns
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by BIGINT REFERENCES tb_user(pk_user),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by BIGINT REFERENCES tb_user(pk_user),
    deleted_at TIMESTAMPTZ,
    deleted_by BIGINT REFERENCES tb_user(pk_user),

    CHECK (updated_at >= created_at),
    CHECK (deleted_at >= created_at)
);

-- Trigger to maintain updated_at
CREATE TRIGGER trigger_tb_user_updated_at
BEFORE UPDATE ON tb_user
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

Rules:

• created_at: TIMESTAMPTZ, NOT NULL, default CURRENT_TIMESTAMP
• created_by: BIGINT FK to user (nullable if the system creates the row)
• updated_at: TIMESTAMPTZ, NOT NULL, maintained by trigger
• updated_by: BIGINT FK to user
• deleted_at: TIMESTAMPTZ, NULL if active (soft delete)
• deleted_by: BIGINT FK to user
• Add CHECK constraints to prevent invalid timestamps

Usage:

• Views filter on WHERE deleted_at IS NULL for soft delete
• Cache invalidation can use updated_at as a freshness signal

3.5 Projection Data Column

All read views MUST include a data JSONB column:

CREATE VIEW v_user AS
SELECT
    id,
    jsonb_build_object(
        'id', id,
        'identifier', identifier,
        'email', email,
        'name', name,
        'createdAt', created_at,
        'updatedAt', updated_at
    ) AS data
FROM tb_user
WHERE deleted_at IS NULL;

Rules:

• The column must be named data (configurable via jsonb_column, default "data")
• Type: JSONB
• Contains the fully-formed projection in GraphQL field case
• Place it at the END of the SELECT (last column) by convention
• Never null (use {} for empty objects, [] for empty arrays)
• Never put pk_* or fk_* inside data

Field naming in JSONB:

• Convert snake_case to camelCase
• created_at → createdAt
• user_id → userId

At startup, FraiseQL maps each @fraiseql.type field to a key in this data JSONB. The hot path reads data and shapes it to the requested GraphQL fields (the optional fraiseql_rs Rust extension accelerates this transformation).

3.6 Reserved Column Names

These columns have a special meaning to FraiseQL and SHOULD NOT be repurposed:

pk_*         — Primary key (internal only, never exposed)
fk_*         — Foreign key (internal only, never exposed)
id           — Public identifier (UUID)
identifier   — Human-readable slug
*_id         — Filterable FK in views (UUID or UUID[])
*__*         — Deep path columns (path separators)
data         — Projection JSONB
created_at   — Audit column
created_by   — Audit column
updated_at   — Audit column
updated_by   — Audit column
deleted_at   — Audit column (soft delete)
deleted_by   — Audit column

---

4. View Patterns

4.1 Base Entity View

CREATE VIEW v_user AS
SELECT
    id,                                   -- Public ID
    jsonb_build_object(                   -- Projection
        'id', id,
        'identifier', identifier,
        'email', email,
        'name', name,
        'createdAt', created_at
    ) AS data
FROM tb_user
WHERE deleted_at IS NULL;                 -- Soft delete

Must include:

• id for public identity (and WHERE id = $1 lookups)
• data JSONB with camelCase fields
• A soft-delete filter

4.2 Related Entity View with Foreign Key

CREATE VIEW v_post AS
SELECT
    id,
    (SELECT id FROM tb_user WHERE pk_user = fk_user) AS user_id,
    jsonb_build_object(
        'id', id,
        'identifier', identifier,
        'title', title,
        'userId', (SELECT id FROM tb_user WHERE pk_user = fk_user),
        'createdAt', created_at
    ) AS data
FROM tb_post
WHERE deleted_at IS NULL;

-- Index the underlying FK column for filtering
CREATE INDEX idx_tb_post_fk_user ON tb_post(fk_user);

Must include:

• A native {parent}_id (UUID) column for filtering
• An index on the underlying FK column
• The FK value (as UUID) in the data JSONB for clients

4.3 Pre-aggregated Composition View

-- Group related entities by the parent key
CREATE VIEW v_posts_by_user AS
SELECT
    p.fk_user,
    jsonb_agg(v.data ORDER BY p.created_at DESC) AS posts
FROM v_post v
JOIN tb_post p ON p.id = (v.data->>'id')::uuid
GROUP BY p.fk_user;

-- Composition view joins base + pre-aggregated
CREATE VIEW v_user_with_posts AS
SELECT
    u.id,
    (u.data || jsonb_build_object(
        'posts', COALESCE(p.posts, '[]'::jsonb)
    )) AS data
FROM v_user u
JOIN tb_user tu ON tu.id = u.id
LEFT JOIN v_posts_by_user p ON p.fk_user = tu.pk_user;

Rules:

• Pre-aggregated _by_ views are NOT exposed directly to GraphQL
• They are used only for efficient composition
• Join on internal fk_* / pk_* columns
• Compose using the JSONB merge operator (||)

4.4 Fact and Dimension Tables (Analytical Modeling)

Fact and dimension tables are a legitimate data-modeling pattern. You build the tables and an ETL/refresh process; FraiseQL queries them through views like any other read source. When a GraphQL query selects aggregate fields, FraiseQL derives GROUP BY and aggregate SQL at runtime against your views (supported aggregates: COUNT, SUM, AVG, MIN, MAX, STDDEV, VARIANCE).

Fact tables hold transactional or event data:

• Measures: SQL columns (numeric types, for fast aggregation)
• Dimensions: a JSONB dimensions column for flexible grouping
• Denormalized filters: indexed SQL columns (e.g. customer_id, occurred_at)

Dimension tables hold reference data used at ETL time to denormalize into fact tables.

Example:

-- Fact table: raw sales transactions (finest granularity)
CREATE TABLE tb_sales_fact (
    pk_sales_fact BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    -- Measures (SQL columns for fast aggregation)
    revenue DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL,
    -- Dimensions (JSONB for flexible GROUP BY)
    dimensions JSONB NOT NULL,
    -- Denormalized filters (indexed for fast WHERE)
    customer_id UUID NOT NULL,
    occurred_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_sales_customer ON tb_sales_fact(customer_id);
CREATE INDEX idx_sales_occurred ON tb_sales_fact(occurred_at);
CREATE INDEX idx_sales_dimensions_gin ON tb_sales_fact USING GIN(dimensions);

-- Pre-aggregated fact table: daily granularity
CREATE TABLE tb_sales_fact_daily (
    pk_sales_fact_daily BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    day DATE NOT NULL UNIQUE,
    revenue DECIMAL(10,2) NOT NULL,      -- SUM(revenue)
    quantity INT NOT NULL,               -- SUM(quantity)
    transaction_count INT NOT NULL,      -- COUNT(*)
    dimensions JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_sales_daily_dimensions_gin ON tb_sales_fact_daily USING GIN(dimensions);

-- Dimension table: product catalog (for ETL denormalization)
CREATE TABLE tb_product_dim (
    pk_product_dim BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- An ETL process denormalizes product attributes into tb_sales_fact.dimensions,
-- e.g. dimensions->>'product_name', dimensions->>'product_category'.

Window functions (ROW_NUMBER, RANK, LAG, LEAD, OVER (PARTITION BY ...)) and richer analytics are standard PostgreSQL that you embed in your v_/tv_ view SQL — FraiseQL serves the resulting view, no special API required.

---

5. Stored Procedures (Mutations)

5.1 Mutation Response Contract

A @fraiseql.mutation resolver calls a fn_ function via db.execute_function(...). The function performs validation and the write, then returns a JSON response. A practical response shape:

{
  "status": "success|error|noop",
  "message": "Human-readable message",
  "entity_id": "uuid-string",
  "entity_type": "User",
  "entity": {
    "id": "uuid",
    "identifier": "slug",
    "name": "...",
    "email": "..."
  },
  "updated_fields": ["field1", "field2"],
  "metadata": {
    "trigger": "api_create|api_update|api_delete",
    "reason": "entity_created|entity_updated|conflict|not_found"
  }
}

Response fields:

Field	Type	Description
status	TEXT	success, error, or noop
message	TEXT	Human-readable message for the client
entity_id	TEXT	UUID of the affected entity
entity_type	TEXT	Entity type name (e.g. "User")
entity	JSONB	The mutated entity (full view projection)
updated_fields	ARRAY	Fields that were actually changed
metadata	JSONB	Operation metadata

Status values:

Status	Meaning
success	Mutation completed successfully
error	Mutation failed (validation, constraint, etc.)
noop	No changes applied (idempotent, already exists, etc.)

The Python resolver inspects status and returns a @fraiseql.success or @fraiseql.error type accordingly.

5.2 Basic Create Function

CREATE FUNCTION fn_create_user(
    tenant_id UUID,
    user_id UUID,
    payload JSONB
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_new_id UUID := gen_random_uuid();
    v_entity JSONB;
BEGIN
    INSERT INTO tb_user (id, email, name, created_by)
    VALUES (
        v_new_id,
        payload->>'email',
        payload->>'name',
        (SELECT pk_user FROM tb_user WHERE id = user_id)
    );

    -- Fetch the full projection from the read view
    SELECT data INTO v_entity FROM v_user WHERE id = v_new_id;

    RETURN jsonb_build_object(
        'status', 'success',
        'message', 'User created successfully',
        'entity_id', v_new_id::text,
        'entity_type', 'User',
        'entity', v_entity,
        'updated_fields', ARRAY['email', 'name'],
        'metadata', jsonb_build_object(
            'trigger', 'api_create',
            'reason', 'entity_created'
        )
    );
EXCEPTION WHEN OTHERS THEN
    RETURN jsonb_build_object(
        'status', 'error',
        'message', SQLERRM,
        'entity_id', NULL,
        'entity_type', 'User',
        'entity', NULL,
        'updated_fields', ARRAY[]::TEXT[],
        'metadata', jsonb_build_object(
            'trigger', 'api_create',
            'reason', 'error'
        )
    );
END;
$$;

Rules:

• Function name: fn_{action}_{entity}
• Parameters: auth context (tenant_id, user_id) plus a payload JSONB
• Return type: JSONB (the response contract above)
• Handle errors and return an error status rather than propagating raw exceptions
• The entity field is the full projection fetched from the read view

5.3 Update Function with Audit

CREATE FUNCTION fn_update_user(
    tenant_id UUID,
    user_id UUID,
    payload JSONB
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_id UUID := (payload->>'id')::UUID;
    v_entity JSONB;
    v_before JSONB;
    v_updated_fields TEXT[];
BEGIN
    -- Fetch the BEFORE snapshot
    SELECT data INTO v_before FROM v_user WHERE id = v_id;
    IF v_before IS NULL THEN
        RETURN jsonb_build_object(
            'status', 'noop',
            'message', 'User not found',
            'entity_id', v_id::text,
            'entity_type', 'User',
            'entity', NULL,
            'updated_fields', ARRAY[]::TEXT[],
            'metadata', jsonb_build_object('trigger', 'api_update', 'reason', 'not_found')
        );
    END IF;

    -- Update only the fields present in the payload
    UPDATE tb_user
    SET
        name = COALESCE(payload->>'name', name),
        updated_by = (SELECT pk_user FROM tb_user WHERE id = user_id),
        updated_at = NOW()
    WHERE id = v_id;

    -- Track which fields were actually supplied
    v_updated_fields := ARRAY(
        SELECT key FROM jsonb_each_text(payload) WHERE key != 'id'
    );

    -- Fetch the AFTER snapshot
    SELECT data INTO v_entity FROM v_user WHERE id = v_id;

    RETURN jsonb_build_object(
        'status', 'success',
        'message', 'User updated successfully',
        'entity_id', v_id::text,
        'entity_type', 'User',
        'entity', v_entity,
        'updated_fields', v_updated_fields,
        'metadata', jsonb_build_object('trigger', 'api_update', 'reason', 'entity_updated')
    );
EXCEPTION WHEN OTHERS THEN
    RETURN jsonb_build_object(
        'status', 'error',
        'message', SQLERRM,
        'entity_id', v_id::text,
        'entity_type', 'User',
        'entity', NULL,
        'updated_fields', ARRAY[]::TEXT[],
        'metadata', jsonb_build_object('trigger', 'api_update', 'reason', 'error')
    );
END;
$$;

Rules:

• Extract the entity ID from the payload
• Fetch a BEFORE snapshot for comparison
• Update only the fields present in the payload
• Track which fields actually changed
• Fetch an AFTER snapshot for the response
• Always return the standard response shape

5.4 Delete Function (Soft Delete)

CREATE FUNCTION fn_delete_user(
    tenant_id UUID,
    user_id UUID,
    payload JSONB
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_id UUID := (payload->>'id')::UUID;
BEGIN
    UPDATE tb_user
    SET
        deleted_at = NOW(),
        deleted_by = (SELECT pk_user FROM tb_user WHERE id = user_id)
    WHERE id = v_id AND deleted_at IS NULL;

    IF NOT FOUND THEN
        RETURN jsonb_build_object(
            'status', 'noop',
            'message', 'User already deleted or not found',
            'entity_id', v_id::text,
            'entity_type', 'User',
            'entity', NULL,
            'updated_fields', ARRAY[]::TEXT[],
            'metadata', jsonb_build_object('trigger', 'api_delete', 'reason', 'not_found')
        );
    END IF;

    RETURN jsonb_build_object(
        'status', 'success',
        'message', 'User deleted successfully',
        'entity_id', v_id::text,
        'entity_type', 'User',
        'entity', NULL,
        'updated_fields', ARRAY['deleted_at', 'deleted_by'],
        'metadata', jsonb_build_object('trigger', 'api_delete', 'reason', 'entity_deleted')
    );
EXCEPTION WHEN OTHERS THEN
    RETURN jsonb_build_object(
        'status', 'error',
        'message', SQLERRM,
        'entity_id', v_id::text,
        'entity_type', 'User',
        'entity', NULL,
        'updated_fields', ARRAY[]::TEXT[],
        'metadata', jsonb_build_object('trigger', 'api_delete', 'reason', 'error')
    );
END;
$$;

Rules:

• Soft delete: set deleted_at and deleted_by
• Never hard-delete from tb_*
• Deleted entities carry no entity data in the response
• Read views filter WHERE deleted_at IS NULL, so the row disappears from queries

---

6. Many-to-Many Relationships

6.1 Junction Table

CREATE TABLE tb_user_role (
    pk_user_role BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    fk_user BIGINT NOT NULL REFERENCES tb_user(pk_user) ON DELETE CASCADE,
    fk_role BIGINT NOT NULL REFERENCES tb_role(pk_role),
    assigned_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(fk_user, fk_role)
);

6.2 View for Array Aggregation

-- Aggregate role IDs per user
CREATE VIEW v_user_role_ids_by_user AS
SELECT
    ur.fk_user,
    array_agg(r.id) AS role_ids
FROM tb_user_role ur
JOIN tb_role r ON r.pk_role = ur.fk_role
GROUP BY ur.fk_user;

-- Include the role IDs in the user view
CREATE VIEW v_user AS
SELECT
    u.id,
    COALESCE(ur.role_ids, ARRAY[]::UUID[]) AS role_ids,
    jsonb_build_object(
        'id', u.id,
        'identifier', u.identifier,
        'email', u.email,
        'roleIds', COALESCE(ur.role_ids, ARRAY[]::UUID[])
    ) AS data
FROM tb_user u
LEFT JOIN v_user_role_ids_by_user ur ON ur.fk_user = u.pk_user
WHERE u.deleted_at IS NULL;

Rules:

• The junction table uses internal keys (fk_*)
• The view aggregates to a UUID[] array of public IDs
• Include the array both as a native column AND inside the data JSONB
• Index the array column with GIN for filtering

---

7. Indexing Strategy

7.1 Essential Indexes

-- On the write table
CREATE INDEX idx_tb_user_id ON tb_user(id);
CREATE INDEX idx_tb_user_identifier ON tb_user(identifier);
CREATE INDEX idx_tb_user_created_at ON tb_user(created_at);
CREATE UNIQUE INDEX idx_tb_user_email ON tb_user(email) WHERE deleted_at IS NULL;

-- On related tables
CREATE INDEX idx_tb_post_fk_user ON tb_post(fk_user);
CREATE INDEX idx_tb_post_id ON tb_post(id);
CREATE INDEX idx_tb_post_created_at ON tb_post(created_at);

-- On array/JSONB columns (GIN)
CREATE INDEX idx_tb_user_role_user ON tb_user_role(fk_user);

Rules:

• B-tree on: public id, identifier, audit columns, FK columns
• UNIQUE on: id and identifier
• GIN on: array columns, deep path columns, and data JSONB (optional)
• Index what will actually be filtered

---

8. View Materialization Strategy

8.1 When to Materialize

Use materialized views for:

• Complex aggregations (slow joins)
• Deep denormalization needed for filtering
• Rarely-changing data (stats, summaries)
• High-cardinality pre-aggregations

CREATE MATERIALIZED VIEW mv_user_stats AS
SELECT
    u.pk_user AS fk_user,
    COUNT(DISTINCT p.pk_post) AS post_count,
    MAX(p.created_at) AS latest_post_at
FROM tb_user u
LEFT JOIN tb_post p ON p.fk_user = u.pk_user
GROUP BY u.pk_user;

CREATE INDEX idx_mv_user_stats_fk_user ON mv_user_stats(fk_user);

-- Refresh manually or on a schedule
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_user_stats;

Rules:

• Name: mv_{entity}
• Index on grouping columns
• Document the refresh strategy
• Use CONCURRENTLY to avoid locks

For heavy nested reads, prefer a tv_* table-backed projection refreshed by triggers — see the tv_ table pattern.

---

9. Validation Checklist

When creating a schema for FraiseQL:

• All write tables prefixed tb_*
• All read views prefixed v_* (or tv_* for table-backed projections)
• All mutation functions prefixed fn_*
• Primary keys: pk_{entity} (BIGINT, hidden)
• Foreign keys: fk_{entity} (BIGINT, hidden)
• Public IDs: id (UUID)
• Slugs: identifier (TEXT, indexed) where applicable
• All read views have a data JSONB column
• Related entity views expose {parent}_id (UUID)
• Deep path columns follow the entity__path__field pattern
• Tables have audit columns
• Views filter on deleted_at IS NULL
• Pre-aggregated views named v_{entities}_by_{parent}
• Mutation functions return the standard JSON response
• Relevant columns are indexed
• JSONB fields use camelCase
• No pk_* / fk_* inside data
• No nullable JSONB (use {} or [])

---

See Also

• Naming Patterns — full identifier reference
• Scalars — built-in GraphQL scalar types
• View Selection Guide — v_ vs tv_
• tv_ Table Pattern — table-backed projections
• Database-Centric Architecture — the CQRS model

---

End of Schema Conventions Specification