FraiseQL Naming Patterns Reference

Status: ✅ Specification
Audience: Architects, database designers, schema developers
Reading Time: 10-15 minutes

---

Overview

FraiseQL enforces a consistent naming convention across your database schema to make relationships between GraphQL types, write-side tables, and read-side views immediately obvious. This guide documents all FraiseQL naming patterns and when to use each.

Key Principle: Naming is semantic. The prefix tells you exactly what the table/view does and how it fits into FraiseQL's architecture.

---

Pattern Index

Pattern	Purpose	Prefix	Example	Storage
ID	GraphQL entity identifier	id	id: UUID	Not stored (computed)
PK/FK	Internal database keys	pk_, fk_	pk_user, fk_order	Primary/foreign keys
Write Tables	Normalized write-side data	tb_	tb_user, tb_order	PostgreSQL TABLE
Read Views	Denormalized read-side (logical)	v_	v_user, v_order_items	PostgreSQL VIEW
Materialized Views	Read-side (table-backed)	tv_	tv_user_summary	PostgreSQL TABLE
Analytics Tables	Fact tables with dimensions	tf_	tf_events, tf_sales	PostgreSQL TABLE

---

1. ID: UUID v4 (GraphQL Primary Identifier)

Purpose: Public-facing entity identifier exposed in the GraphQL schema

Format:

id: UUID v4

Characteristics:

• ✅ Externally visible (appears in GraphQL queries)
• ✅ Globally unique across all records
• ✅ Non-sequential (prevents guessing)
• ✅ URL-safe
• ❌ NOT stored directly in database (computed by FraiseQL)

Example - GraphQL Schema:

type User {
  id: ID!                    # UUID v4, exposed to client
  email: String!
  createdAt: DateTime!
}

query {
  user(id: "550e8400-e29b-41d4-a716-446655440000") {
    email
  }
}

Example - Python Type:

@fraiseql.type
class User:
    id: UUID                 # Maps to GraphQL ID scalar
    email: str
    created_at: datetime

Example - Database Query:

-- FraiseQL computes ID from pk_user in v_user
SELECT 
    encode(digest(pk_user::text, 'sha256'), 'hex')::uuid AS id,
    email,
    created_at
FROM tb_user

When to use:

• ✅ Always for public API identifiers
• ✅ When you need URL-safe, non-sequential IDs
• ✅ For federation (IDs must be consistent)
• ❌ Don't expose pk_user to clients

---

2. PK_ / FK_: Internal Database Keys

Purpose: Internal BIGINT primary and foreign keys used by database

Format:

pk_{entity}  → Primary key
fk_{entity}  → Foreign key

Characteristics:

• ✅ Sequential for performance
• ✅ Small (BIGINT = 64-bit)
• ✅ Fast joins
• ✅ Never exposed to GraphQL
• ❌ Internal only

Example - Write Table:

CREATE TABLE tb_user (
    pk_user BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    id UUID NOT NULL UNIQUE,  -- Unique constraint for UUID
    email VARCHAR NOT NULL,
    created_at TIMESTAMP
);

CREATE TABLE tb_order (
    pk_order BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    id UUID NOT NULL UNIQUE,
    fk_user BIGINT NOT NULL REFERENCES tb_user(pk_user),
    total DECIMAL
);

Example - Join Pattern:

-- Fast join using BIGINT foreign keys
SELECT 
    u.email,
    o.total
FROM tb_user u
INNER JOIN tb_order o ON o.fk_user = u.pk_user
WHERE u.pk_user = 42

When to use:

• ✅ Always for internal database keys
• ✅ For all joins (BIGINT faster than UUID)
• ✅ In write-side normalized tables
• ❌ Never expose in GraphQL schema

---

3. TB_{Entity}: Write-Side Normalized Tables

Purpose: Normalized write-side storage for Create/Update/Delete operations

Format:

tb_{entity}          → Singular entity name
tb_{entity}_relationship  → Many-to-many tables

Characteristics:

• ✅ Normalized (3NF)
• ✅ Handles Create/Update/Delete
• ✅ Used for mutations
• ✅ Multiple related entities
• ❌ Not for reading (use views instead)

Example - E-commerce Schema:

-- Write tables (normalized)
CREATE TABLE tb_customer (
    pk_customer BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    id UUID NOT NULL UNIQUE,
    email VARCHAR NOT NULL UNIQUE,
    name VARCHAR NOT NULL
);

CREATE TABLE tb_product (
    pk_product BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    id UUID NOT NULL UNIQUE,
    sku VARCHAR NOT NULL,
    price DECIMAL
);

CREATE TABLE tb_order (
    pk_order BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    id UUID NOT NULL UNIQUE,
    fk_customer BIGINT NOT NULL REFERENCES tb_customer(pk_customer),
    order_date TIMESTAMP
);

-- Many-to-many junction table
CREATE TABLE tb_order_item (
    pk_order_item BIGINT PRIMARY KEY,
    fk_order BIGINT NOT NULL REFERENCES tb_order(pk_order),
    fk_product BIGINT NOT NULL REFERENCES tb_product(pk_product),
    quantity INT NOT NULL
);

When to use:

• ✅ For all mutable data (mutations)
• ✅ When inserting/updating/deleting records
• ✅ For referential integrity (foreign keys)
• ✅ When normalizing many-to-many relationships

When NOT to use:

• ❌ For read queries (use v_* or tv_* views)
• ❌ For analytics (use tf_* tables)
• ❌ If you need complex JOINs (denormalize to view)

---

4. V_{Entity}: Read-Side Denormalized Views (Logical)

Purpose: Logical (not table-backed) denormalized views for read queries

Format:

v_{entity}           → Base projection
v_{entities}_by_{parent}  → Composed/aggregated projections

Characteristics:

• ✅ Denormalized (faster reads, fewer JOINs)
• ✅ Logical view (not stored)
• ✅ Recomputed on every query
• ✅ Always consistent with write table
• ❌ Can be slow for complex queries

Example - Simple View:

-- v_user: Denormalized read-side view
CREATE VIEW v_user AS
SELECT 
    encode(digest(u.pk_user::text, 'sha256'), 'hex')::uuid AS id,
    u.email,
    u.name,
    u.created_at,
    COUNT(o.pk_order) AS order_count,
    COALESCE(SUM(o.total), 0) AS lifetime_value
FROM tb_customer u
LEFT JOIN tb_order o ON o.fk_customer = u.pk_customer
GROUP BY u.pk_user, u.email, u.name, u.created_at

Example - Composed View:

-- v_orders_by_customer: Pre-computed composition
CREATE VIEW v_orders_by_customer AS
SELECT 
    u.id,
    u.name,
    json_agg(
        json_build_object(
            'id', o.id,
            'date', o.order_date,
            'total', o.total
        )
    ) AS orders
FROM v_user u
LEFT JOIN tb_order o ON o.fk_customer = u.pk_customer
GROUP BY u.id, u.name

When to use:

• ✅ For simple denormalized reads
• ✅ When 1-2 JOINs needed
• ✅ Data doesn't change frequently
• ✅ For GraphQL queries without complex nesting

When NOT to use:

• ❌ For complex queries with 3+ JOINs (use tv_*)
• ❌ For high-volume queries (use tv_* for caching)
• ❌ For analytics (use tf_* fact tables)

---

5. TV_{Entity}: Read-Side Denormalized Tables (Materialized)

Purpose: Table-backed materialized denormalized data for performance-critical reads

Format:

tv_{entity}  → Materialized view (table-backed)

Characteristics:

• ✅ Denormalized (fastest reads)
• ✅ Table-backed (stored physically)
• ✅ Requires refresh strategy
• ✅ Handles complex 3+ JOIN queries
• ❌ Slightly out-of-date (until refreshed)

Example - Complex Materialized View:

-- tv_user_complete: Table-backed materialized view
CREATE TABLE tv_user_complete AS
SELECT 
    u.id,
    u.name,
    u.email,
    COUNT(DISTINCT o.pk_order) AS total_orders,
    SUM(o.total)::DECIMAL AS lifetime_spending,
    MAX(o.order_date) AS last_order_date,
    json_agg(DISTINCT cat.name) AS purchased_categories,
    json_agg(DISTINCT prod.name)::jsonb AS favorite_products,
    -- Nested complex denormalization
    json_build_object(
        'tier', CASE 
            WHEN SUM(o.total) > 10000 THEN 'platinum'
            WHEN SUM(o.total) > 5000 THEN 'gold'
            ELSE 'standard'
        END,
        'status', CASE 
            WHEN NOW() - MAX(o.order_date) < '1 year' THEN 'active'
            ELSE 'inactive'
        END
    ) AS segment_info
FROM v_user u
LEFT JOIN tb_order o ON o.fk_customer = u.pk_customer
LEFT JOIN tb_order_item oi ON oi.fk_order = o.pk_order
LEFT JOIN tb_product p ON p.pk_product = oi.fk_product
LEFT JOIN tb_category cat ON cat.pk_category = p.fk_category
GROUP BY u.pk_user, u.id, u.name, u.email;

-- Create unique index for refresh strategy
CREATE UNIQUE INDEX tv_user_complete_id_idx ON tv_user_complete(id);

-- Refresh materialized view hourly
CREATE OR REPLACE FUNCTION refresh_tv_user_complete() AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY tv_user_complete;
END;
$$ LANGUAGE plpgsql;

When to use:

• ✅ For queries needing 3+ JOINs
• ✅ For high-volume analytical reads
• ✅ When denormalization significantly speeds reads
• ✅ When you can tolerate slight staleness

When NOT to use:

• ❌ For real-time always-fresh data (use v_*)
• ❌ For simple queries (use v_*)
• ❌ For analytics (use tf_* fact tables)

---

6. TF_{Entity}: Analytics Fact Tables

Purpose: Denormalized fact tables for analytics with JSONB dimensions

Format:

tf_{entity}  → Fact table (measurements + dimensions)

Characteristics:

• ✅ Fact table pattern for OLAP
• ✅ Measures as direct SQL columns (revenue, quantity)
• ✅ Dimensions as JSONB (flexible GROUP BY)
• ✅ Date info for time-based aggregation
• ✅ Optimized for analytics queries
• ❌ Cannot be used for OLTP (no granular updates)

Example - Event Analytics:

-- tf_events: Fact table for analytics
CREATE TABLE tf_events (
    -- Unique identifier
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Occurred at timestamp
    occurred_at TIMESTAMP NOT NULL,

    -- Measures (direct numeric columns for fast aggregation)
    revenue DECIMAL,
    quantity INT,
    sessions INT,
    bounce_count INT,

    -- Dimensions as JSONB (no separate dimension tables)
    data JSONB NOT NULL,  -- Flexible dimensions

    -- Date info for time-series (pre-computed for speed)
    date_info JSONB NOT NULL  -- {'year': 2026, 'month': 2, 'quarter': 1, 'week': 5}
);

-- Example data
INSERT INTO tf_events (occurred_at, revenue, quantity, sessions, data, date_info) 
VALUES (
    '2026-02-05 14:30:00',
    150.00,
    5,
    1,
    jsonb_build_object(
        'product_id', 'prod-123',
        'category', 'Electronics',
        'region', 'US-West',
        'user_id', 'user-456',
        'customer_segment', 'premium'
    ),
    jsonb_build_object(
        'year', 2026,
        'month', 2,
        'quarter', 1,
        'week', 5,
        'day_of_week', 4
    )
);

Example - Analytics Query:

-- Fast aggregation: measures + JSONB dimension extraction
SELECT 
    data->>'category' AS category,
    data->>'region' AS region,
    date_info->>'year' AS year,
    COUNT(*) AS event_count,
    SUM(revenue) AS total_revenue,
    AVG(revenue) AS avg_revenue,
    SUM(quantity) AS total_quantity
FROM tf_events
WHERE occurred_at >= '2026-01-01'
GROUP BY 
    data->>'category',
    data->>'region',
    date_info->>'year'
ORDER BY total_revenue DESC

Fact Table Rules:

1. Measures are direct SQL columns - revenue DECIMAL, quantity INT
2. Dimensions are JSONB - data JSONB (no normalization)
3. Date info is pre-computed JSONB - date_info JSONB with year, month, quarter, week, day_of_week
4. One row per event - Immutable fact (insert-only)
5. No foreign keys - Dimensions embedded in JSONB

When to use:

• ✅ For analytics and business intelligence
• ✅ For time-series data aggregation
• ✅ For OLAP queries (GROUP BY multiple dimensions)
• ✅ When dimension cardinality is high
• ✅ For event tracking (ad impressions, pageviews, etc.)

When NOT to use:

• ❌ For OLTP queries (use tb_*, v_*)
• ❌ For real-time row lookups
• ❌ For anything that requires UPDATE/DELETE

---

Quick Decision Tree

Is this for GraphQL queries?
├─ YES: Use ID (UUID v4)
└─ NO: Use pk_/fk_ (BIGINT internal keys)

Is this a write-side table?
├─ YES: Use tb_{entity}
└─ NO: Is this for reading?

Is this a read view?
├─ Logical view (recomputed each query)?
│   ├─ YES: Use v_{entity}
│   └─ NO: Is it table-backed?
│
└─ Table-backed materialized view?
    ├─ YES: Use tv_{entity}
    └─ NO: Is this for analytics?

Is this an analytics fact table?
├─ YES: Use tf_{entity} with JSONB dimensions
└─ NO: What are you trying to do?

---

Complete Example: E-Commerce Schema

Scenario: E-commerce platform with products, orders, and analytics

-- WRITE SIDE: tb_ tables (normalized)
CREATE TABLE tb_product (
    pk_product BIGINT PRIMARY KEY,
    id UUID NOT NULL UNIQUE,
    sku VARCHAR NOT NULL,
    name VARCHAR NOT NULL,
    price DECIMAL NOT NULL
);

CREATE TABLE tb_customer (
    pk_customer BIGINT PRIMARY KEY,
    id UUID NOT NULL UNIQUE,
    email VARCHAR NOT NULL UNIQUE,
    name VARCHAR NOT NULL
);

CREATE TABLE tb_order (
    pk_order BIGINT PRIMARY KEY,
    id UUID NOT NULL UNIQUE,
    fk_customer BIGINT NOT NULL REFERENCES tb_customer(pk_customer),
    order_date TIMESTAMP NOT NULL,
    total DECIMAL NOT NULL
);

-- READ SIDE: v_ view (logical denormalization)
CREATE VIEW v_order AS
SELECT 
    o.id,
    c.id AS customer_id,
    c.email,
    o.order_date,
    o.total,
    json_agg(
        json_build_object(
            'product_id', p.id,
            'name', p.name,
            'quantity', oi.quantity,
            'price', p.price
        )
    ) AS line_items
FROM tb_order o
JOIN tb_customer c ON c.pk_customer = o.fk_customer
LEFT JOIN tb_order_item oi ON oi.fk_order = o.pk_order
LEFT JOIN tb_product p ON p.pk_product = oi.fk_product
GROUP BY o.pk_order, o.id, c.pk_customer, c.id, c.email;

-- ANALYTICS: tf_ table (fact table with JSONB dimensions)
CREATE TABLE tf_sales (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    occurred_at TIMESTAMP NOT NULL,

    -- Measures
    revenue DECIMAL,
    quantity INT,
    discount_amount DECIMAL,

    -- Dimensions (JSONB - no separate tables)
    data JSONB NOT NULL,  -- product_id, category, region, customer_segment

    -- Date info
    date_info JSONB NOT NULL  -- year, month, quarter, week
);

---

Enforcement Rules

Rule	Pattern	Enforced By	Example
Singular entity names	tb_user, not tb_user	Schema validation	✅ tb_order, ❌ tb_order
No abbreviations	tb_customer, not tb_cust	Naming conventions	✅ tb_customer, ❌ tb_cust
UUID for public ID	Always UUID, never VARCHAR	Type checking	✅ id: UUID, ❌ id: VARCHAR
BIGINT for internal keys	pk_*, fk_* always BIGINT	Schema validation	✅ pk_user BIGINT, ❌ pk_user UUID
Unique constraint on ID	id field must be UNIQUE	Database constraints	✅ id UUID UNIQUE, ❌ missing
JSONB for tf_ dimensions	data JSONB with schema	Documentation	✅ data JSONB, ❌ data VARCHAR

---

See Also

• Schema Conventions Specification
• Data Planes Architecture
• Analytics Patterns
• View Selection Guide