# DDL Reference
Complete reference for TVIEW creation and management with FraiseQL patterns.
**Version**: 0.1.0-beta.1 • **Last Updated**: December 11, 2025
## Overview
pg_tviews provides transactional materialized views through DDL and SQL functions. TVIEWs follow FraiseQL's trinity identifier pattern and CQRS architecture.
## Creating TVIEWs
### DDL Method: CREATE TABLE tv_* AS SELECT
**Syntax**:
```sql
CREATE TABLE tv_<entity> AS
SELECT
<pk_column> as pk_<entity>, -- Required: lineage root
<uuid_column> as id, -- Optional: GraphQL ID
<other_columns>, -- Optional: cascade FKs, filtering FKs
<jsonb_data> as data -- Required: JSONB read model
FROM tb_<entity> t
[LEFT JOIN tb_<related> r ON ...]
[WHERE ...]
[GROUP BY ...];
```
**Note**: The ProcessUtility hook automatically intercepts `CREATE TABLE tv_* AS SELECT` statements and converts them to TVIEW creation. This provides DDL-like syntax for TVIEW creation.
### Function Method: pg_tviews_create()
**Syntax**:
```sql
SELECT pg_tviews_create('tv_<entity>', '
SELECT
<pk_column> as pk_<entity>, -- Required: lineage root
<uuid_column> as id, -- Optional: GraphQL ID
<other_columns>, -- Optional: cascade FKs, filtering FKs
<jsonb_data> as data -- Required: JSONB read model
FROM tb_<entity> t
[LEFT JOIN tb_<related> r ON ...]
[WHERE ...]
[GROUP BY ...]
');
```
**Note**: This is the programmatic approach that can be used in scripts and applications.
### FraiseQL Naming Conventions
Following FraiseQL patterns:
- **TVIEW name**: `tv_<entity>` (e.g., `tv_post`, `tv_user`)
- **Source tables**: `tb_<entity>` (e.g., `tb_post`, `tb_user`)
- **Backing view**: `tviews.<schema>__tv_<entity>` (automatically created in
pg_tviews' own schema, named after the TVIEW's table and fitted to 63 bytes;
`tviews.registry.view` reports it). The application's own `v_<entity>` view is
left alone: a TVIEW can materialize it (`pg_tviews_create('tv_order', 'SELECT * FROM
v_order')`) or read views that read it. Its privileges follow the TVIEW's table:
whoever can `SELECT` from `tv_<entity>` can `SELECT` from it (see
[Privileges](#privileges))
- **Embedding another TVIEW**: read its table `tv_<entity>` (`JOIN tv_user u ON
u.pk_user = p.fk_user`)
- **Entity name**: Derived from TVIEW name by removing `tv_` prefix
### Required Columns
#### Primary Key Column (`pk_<entity>`)
Every TVIEW must have exactly one primary key column named `pk_<entity>`:
```sql
-- Correct: Follows trinity pattern
SELECT p.pk_post as pk_post, ... FROM tb_post p
-- Incorrect: Wrong name
SELECT tb_post.id as pk_post, ... FROM tb_post -- ERROR: not lineage root
-- Incorrect: Wrong type
SELECT tb_post.id::bigint as pk_post, ... FROM tb_post -- ERROR: not original PK
```
**Requirements**:
- Must be named `pk_<entity>` where `<entity>` matches TVIEW name
- Must be the actual primary key from source table (no casting)
- Used for lineage tracking and cascade propagation
#### JSONB Data Column (`data`)
Every TVIEW must have exactly one JSONB column named `data`:
```sql
-- Correct: JSONB read model
jsonb_build_object(
'id', p.id,
'title', p.title,
'author', jsonb_build_object('id', u.id, 'name', u.name)
) as data
-- Incorrect: Wrong type
jsonb_build_object(...)::text as data -- ERROR: not JSONB
-- Incorrect: Wrong name
jsonb_build_object(...) as json_data -- ERROR: not named 'data'
```
**Best Practices**:
- Include all GraphQL-required fields
- Use nested objects for relationships
- Include UUIDs for GraphQL filtering
- Add computed fields as needed
### Optional Columns
#### Trinity Identifiers
Following FraiseQL's trinity pattern:
```sql
SELECT
p.pk_post as pk_post, -- Required: lineage root
p.id as id, -- Optional: GraphQL ID (UUID)
p.identifier as identifier, -- Optional: SEO slug (text)
p.fk_user as fk_user, -- Optional: cascade FK (integer)
u.id as user_id, -- Optional: filtering FK (UUID)
jsonb_build_object(...) as data
FROM tb_post p
JOIN tb_user u ON p.fk_user = u.pk_user;
```
#### Cascade Foreign Keys
Include all foreign keys used for cascade propagation:
```sql
-- Include FKs for automatic cascade updates
SELECT
p.pk_post,
p.fk_user, -- Enables user → post cascades
p.fk_category, -- Enables category → post cascades
jsonb_build_object(...) as data
FROM tb_post p;
```
#### Filtering Foreign Keys
Include UUID FKs for efficient GraphQL filtering:
```sql
-- Include UUID FKs for WHERE clauses
SELECT
p.pk_post,
u.id as user_id, -- Filter posts by user UUID
c.id as category_id, -- Filter posts by category UUID
jsonb_build_object(...) as data
FROM tb_post p
JOIN tb_user u ON p.fk_user = u.pk_user
JOIN tb_category c ON p.fk_category = c.pk_category;
```
### Complete Examples
#### Simple TVIEW
```sql
CREATE TABLE tv_user AS
SELECT
u.pk_user as pk_user,
u.id,
u.identifier,
u.name,
jsonb_build_object(
'id', u.id,
'identifier', u.identifier,
'name', u.name,
'email', u.email,
'created_at', u.created_at
) as data
FROM tb_user u;
```
#### Complex TVIEW with Relationships
```sql
CREATE TABLE tv_post AS
SELECT
p.pk_post as pk_post,
p.id,
p.identifier,
p.fk_user,
u.id as user_id,
jsonb_build_object(
'id', p.id,
'identifier', p.identifier,
'title', p.title,
'content', p.content,
'created_at', p.created_at,
'author', jsonb_build_object(
'id', u.id,
'identifier', u.identifier,
'name', u.name
),
'comments', COALESCE(
jsonb_agg(
jsonb_build_object(
'id', c.id,
'text', c.text,
'author', jsonb_build_object('id', cu.id, 'name', cu.name)
)
) FILTER (WHERE c.id IS NOT NULL),
'[]'::jsonb
)
) as data
FROM tb_post p
JOIN tb_user u ON p.fk_user = u.pk_user
LEFT JOIN tb_comment c ON c.fk_post = p.pk_post
LEFT JOIN tb_user cu ON c.fk_user = cu.pk_user
GROUP BY p.pk_post, p.id, p.identifier, p.title, p.content,
p.created_at, p.fk_user, u.id, u.identifier, u.name;
```
### What Happens During TVIEW Creation
1. **SQL Analysis**: Parses SELECT statement to identify dependencies
2. **Schema Inference**: Determines column types and relationships
3. **Backing View Creation**: Creates `tviews.<schema>__tv_<entity>` with your SELECT,
owned by you
4. **Materialized Table Creation**: Creates `tv_<entity>` table
5. **Trigger Installation**: Sets up triggers on all source tables
6. **Initial Population**: Fills TVIEW with current data
7. **Metadata Registration**: Records TVIEW in system catalogs
**Column types.** Every column of `tv_<entity>` has the type of the backing view's
column, typmod included: an enum, a domain, a composite, an array of them, a type in
another schema, `varchar(5)`, `numeric(6,2)`, `bit(4)`. Only the convention columns
have fixed types: `pk_<entity>` and `fk_*` are `BIGINT`, `id` is `UUID`, `data` is
`JSONB`. A TVIEW created before 0.1.0-beta.21 stored enums, domains and composites as
`text` and dropped typmods; it keeps those types until
`pg_tviews_create_or_replace()` is run with its definition, which converts each such
column in place and returns `altered`.
Because the backing view's columns depend on their types, `DROP TYPE … CASCADE` of a
type the view returns drops the view, and pg_tviews then drops the whole TVIEW (its
table, triggers and registration), as when a base table is dropped with `CASCADE`.
### Supported SQL Features
#### ✅ Supported
- **JOINs**: INNER, LEFT, RIGHT, FULL OUTER
- **Aggregations**: GROUP BY, HAVING, jsonb_agg(), array_agg()
- **Expressions**: CASE, COALESCE, NULLIF, FILTER
- **Subqueries** in the SELECT list or WHERE (`(SELECT …)`, `ARRAY(SELECT …)`,
`EXISTS`, `IN (SELECT …)`), `LATERAL`, and plain views (with `GROUP BY` too):
writes to the tables they read cascade when a condition links them to the TVIEW
key (`l.fk_order = o.pk_order`, `l.pos > o.min_pos`). An uncorrelated subquery
links nothing: see [Tables no cascade reaches](#tables-no-cascade-reaches)
- **Outer joins**: a table on the preserved side of a `LEFT`/`RIGHT JOIN` is linked
through the nullable side when the key is on that side or the path goes on from it
by an equality (a view
`tb_line l LEFT JOIN tb_order o ON l.fk_order = o.pk_order` exposing `o.id AS
order_id`, read by the TVIEW with `v.order_id = t.id`): a row with no match yields
NULLs there and matches no key
- **`GROUP BY` / `DISTINCT ON` views**: a view column passes through when it is a
grouping or `DISTINCT ON` key, or equal to one through a join condition
(`DISTINCT ON (l.fk_order) o.pk_order` with `l.fk_order = o.pk_order`): its value
is the key's on every row that can match
- **View columns the TVIEW doesn't read**: a view, subquery or CTE is followed only
for the columns read from it (in the select list, `WHERE`, joins, or through a
whole-row reference). The tables behind the other columns get no trigger, so
writes to them cost nothing. A column used for sorting, grouping or `DISTINCT`,
or returning a set, always counts
- **Functions**: jsonb_build_object(), jsonb_array_elements(), etc.
- **Operators**: Standard PostgreSQL operators
- **UNION / UNION ALL**: incremental refresh cascades to every branch's base
table; branches must key on disjoint `pk_<entity>` values (otherwise
`pg_tviews.union_duplicate_policy` governs the duplicate)
- **INTERSECT / EXCEPT**: maintained branch by branch like UNION. A refresh
recomputes the view's row for each changed key, and both operators compare whole
rows, key included, so a row enters or leaves the TVIEW as the set operation says
- **CTEs (`WITH`)**: cascade paths resolve through a CTE whose body reads one or
several joined base tables, reads earlier CTEs (a chain of any length) or
subqueries in its `FROM`, or is a set operation. A CTE the view defines but never
uses is accepted; the tables it reads get no trigger
- **Computed columns**: a view, subquery or CTE column computed by an immutable
expression of base columns (`upper(n.name) AS code`) links like the expression
itself: a join on it (`x.code = s.code`) maps writes through `x.code =
upper(n.name)`. A column computed by a volatile or stable expression (`now()`,
`random()`) links nothing
- **Arrays of keys**: `a.pk_node = ANY (<array>)`, a join on `unnest(<array>)` in a
subquery's select list, and `LATERAL unnest(<array>)` are the same condition:
the array's element equals the key. A write to the joined table maps through
`<array> @> ARRAY[<key>]`, which a GIN index on the array expression serves; the
create-time notice names it (below)
- **Window functions, `LIMIT`/`OFFSET`, `GROUPING SETS`**: accepted, but a write to a
table read under one of them can change rows other than its own, so the table is
`all_keys` and the TVIEW's `uncascaded_policy` decides (see [Tables no cascade
reaches](#tables-no-cascade-reaches)). The same holds for a set-returning function
in the backing view's own select list. In a subquery's select list a set-returning
function only multiplies rows: the other columns pass through, and an `unnest`
output is an array element (above)
- **Recursive CTEs (`WITH RECURSIVE`)**, in the definition or in a view it reads: a
row of a recursive CTE comes from rows of the step before, so the tables read
inside it are `all_keys` (`read in a recursive CTE (public.v_category_path)`) and
the tables read outside it keep their mapping. A small lookup tree read by a large
entity view is the usual case: create the TVIEW with `uncascaded_policy =
'full_refresh'`, and the entity's own writes stay incremental
- **DISTINCT ON**: deduplicated read models, keyed on their `DISTINCT ON` key
([ADR 0169](../adr/0169-tview-row-identity.md)): its value names the TVIEW's
rows, it is the table's primary key, and `tviews.registry.identity` reports it.
- The key is a column, projected (`DISTINCT ON (o.id) o.pk_order, o.id …`; it may
be aliased, `DISTINCT ON (c.id_contract) c.id_contract AS pk_contract`) or equal
through a join condition to a projected column (`DISTINCT ON (l.fk_order)
o.pk_order` with `l.fk_order = o.pk_order`). Its type can be anything (`bigint`,
`uuid`, `text`, `numeric`, `date`, a quoted mixed-case column…).
- Tables read through joins are followed like any TVIEW's, whatever the key.
- Writes are followed from the old and the new row: a row that changes its key
leaves its old group and joins the new one, and a statement writing several
groups refreshes each of them. The refresh filters on the key with its type, so
PostgreSQL reaches the base table's index through the `DISTINCT ON`.
- `pk_<entity>` is still required: parents embed the TVIEW through
`fk_<entity> = pk_<entity>`, and they follow the winning row when it changes.
- Refused at create: a composite key (`DISTINCT ON (a, b)`: a TVIEW row is one
entity with one key; model "one row per (a, b)" as an entity of its own), and a
key that is an expression, or a column not projected that no projected column
equals. The message names the key.
- **Generated columns**: `STORED` columns are ordinary columns. A **virtual**
generated column (PostgreSQL 18's default) has no value in the rows a trigger
sees, so pg_tviews follows its inputs instead: a TVIEW reading `code = upper(name)`
refreshes when `name` changes. A key or join on a virtual column is mapped by
computing the column from its expression over the changed rows, and the direct
and fan-out patches never copy a virtual column or one of its inputs.
#### ❌ Not Supported
- **Self-Joins**: May cause dependency cycles
### How a write finds the TVIEW rows to refresh
When a TVIEW is created, pg_tviews reads PostgreSQL's query tree of its backing view
(views, CTEs, subqueries and `UNION` branches included) and records, per base table,
how a changed row maps to TVIEW keys (`tviews.registry.cascade_kinds`): the key is a
column of the row (`local`), a generated query over the changed rows finds it
(`mapped`, for chains of joins and non-equality conditions), a TVIEW it embeds
refreshes it (`propagated`), or nothing selective links them (`all_keys`). A `mapped`
query that would scan a large table sequentially is reported at create time with
the index that avoids it:
```
NOTICE: writes to public.tb_sku map to tv_order keys with a sequential scan of tb_line
(about 20000 rows); an index on tb_line (fk_sku) would make them cheaper
```
A lookup by an expression (an array of keys, a computed column) names the index to
create:
```
NOTICE: writes to public.tb_node map to tv_node keys with a sequential scan of tb_node
(about 3000 rows); CREATE INDEX ON public.tb_node USING gin
(((pg_catalog.string_to_array(path, '.'::pg_catalog.text))::bigint[])) would
make them cheaper
```
`tviews.pg_tviews_mapping_query('tv_order', 'tb_sku')` returns the query.
The triggers follow the kind of each table:
| `local` | row trigger | the key is read off each changed row (and its old image) |
| `mapped`, `all_keys` | three statement triggers with transition tables (`INSERT`, `UPDATE`, `DELETE`) | one mapping query over the statement's changed rows; under `full_refresh` an `all_keys` write refreshes the whole TVIEW |
| `propagated` | none | refreshing the embedded TVIEW refreshes this one |
Every table but a `propagated` one also gets an `AFTER TRUNCATE` trigger, which
refreshes the whole TVIEW. An `UPDATE` that changes none of the columns the TVIEW
reads from a `mapped` table maps nothing (rows are matched to their old image by
primary key).
**Partitioned tables.** A partitioned table of any kind but `propagated` gets the
row trigger, which PostgreSQL copies onto every partition, and maps each changed row
from it: the transition tables of a statement trigger on the partitioned table would
miss the rows of a statement that names a partition. Every partition, leaf or middle
level, also gets the flush and `AFTER TRUNCATE` triggers, because a statement
trigger fires only on the table the statement names. So a write or a `TRUNCATE` that
targets a partition directly refreshes the TVIEW like one through the partitioned
table, and a `TRUNCATE` of the partitioned table refreshes it once.
Partitions created (`CREATE TABLE … PARTITION OF`, also from a function such as a
partition manager's) or attached after the TVIEW get the same triggers, and a
detached one loses them. `ATTACH` and `DETACH` move rows in or out with no row
trigger firing, so each refreshes the TVIEWs over that table in full.
`pg_tviews_health_check()` reports a partition whose triggers are missing, and
`pg_tviews_reregister_all()` puts them back.
### Tables no cascade reaches
A table classified `all_keys` has no condition linking its rows to the TVIEW key,
so a write to it could change any row. It is reported when the TVIEW is created and
listed in `tviews.registry.uncascaded_tables`. Common shapes:
- an uncorrelated subquery (`(SELECT count(*) FROM tb_flag)` in every row);
- a window function, `LIMIT`/`OFFSET`, a set-returning function in the select list,
or `GROUPING SETS`, in the backing view's own SELECT (`count(*) OVER ()` changes
every row when one is inserted; `ORDER BY … LIMIT 10` changes which rows are in):
the reason reads `read under a window function in the top-level SELECT`;
- a subquery or view whose rows are not passed through to the key: under a window
function, `LIMIT`/`OFFSET` or `GROUPING SETS`, or joined on a column computed by a
volatile or stable expression;
- a table read inside a recursive CTE (`read in a recursive CTE (…)`).
A table read in several places is `all_keys` when one of them can't be traced, but
its other reads keep refreshing the rows they reach: a TVIEW's own table, read
where its key comes from, refreshes the row of each entity it writes. The reason
then ends with `the rows its other reads reach are still refreshed`, and the policy
decides only about the rest.
What happens is fixed per TVIEW by its `uncascaded_policy`, declared with the TVIEW
and stored with it:
| `error` (default) | `ERROR`: nothing is created; the HINT says what to declare | — |
| `full_refresh` | `NOTICE` | the whole TVIEW is brought up to date at flush, once per transaction or statement; unchanged rows are not rewritten |
| `warn` | `WARNING: writes to public.tb_flag will not refresh public.tv_report (read in a subquery, with no condition linking it to the TVIEW key)` | nothing: the rows stay stale until a mapped table changes |
A TVIEW with such a table and no declared policy is refused:
```
ERROR: writes to public.tb_flag would not refresh public.tv_report (read in a subquery, with no
condition linking it to the TVIEW key): declare what such a write does with the TVIEW's
uncascaded_policy
HINT: To refresh public.tv_report in full on such writes: pg_tviews_create_or_replace(
'public.tv_report', <definition>, options => '{"uncascaded_policy": "full_refresh"}');
before CREATE TABLE … AS or pg_tviews_create(): SET pg_tviews.uncascaded_policy =
'full_refresh'. "warn" accepts stale rows instead. Or join the tables on a column
pg_tviews can trace.
```
Declare it with the TVIEW:
```sql
SELECT pg_tviews_create_or_replace('tv_report', $$ … $$,
options => '{"uncascaded_policy": "full_refresh"}');
```
`CREATE TABLE … AS` and `pg_tviews_create()` take no options: they read the setting
`pg_tviews.uncascaded_policy` (default `error`) instead.
```sql
SET pg_tviews.uncascaded_policy = 'full_refresh';
CREATE TABLE tv_report AS SELECT …;
RESET pg_tviews.uncascaded_policy; -- the TVIEW keeps full_refresh
```
Changing the option of an existing TVIEW with `pg_tviews_create_or_replace()` is an
`altered` change: the policy is stored and the TVIEW re-registered, with no rebuild.
`full_refresh` recomputes every row of the TVIEW: on a 100 000-row TVIEW that is about
a second per flush that wrote to such a table. Use it for small TVIEWs, or rewrite the
definition so that the table is joined on a column pg_tviews can trace.
### Limitations
- **Dependency Depth**: Performance degrades with >5 cascade levels
- **Circular Dependencies**: Automatically detected and rejected
- **Column Name Conflicts**: Must resolve ambiguous column names
## Privileges
A TVIEW's table is an ordinary table: grant on it as on any other. Its backing view,
in `tviews`, follows it:
- whoever can `SELECT` from `tv_<entity>` (a role, or `PUBLIC`) can `SELECT` from its
backing view, so a role granted `SELECT ON ALL TABLES IN SCHEMA app`, or reading
`app` through default privileges, reads `tviews.app__tv_<entity>` too;
- the view's grants are made its table's when the TVIEW is created or rebuilt, and
after every `GRANT` or `REVOKE` on tables, including `ON ALL TABLES IN SCHEMA`;
- `ALTER TABLE tv_<entity> OWNER TO` (and `REASSIGN OWNED`) gives the view the new
owner, who reads the base tables through it;
- only `SELECT` is copied, without grant option; `INSERT`, `UPDATE` and the others
granted on the table are not. A grant made on the backing view alone is taken back
by the next of these.
`USAGE` on `tviews` is granted to `PUBLIC` by the extension.
## DROP TABLE tv_*
### Syntax
```sql
DROP TABLE [IF EXISTS] tv_<entity> [CASCADE];
```
### Examples
```sql
-- Drop a TVIEW
DROP TABLE tv_post;
-- Safe drop (no error if doesn't exist)
DROP TABLE IF EXISTS tv_missing;
-- Drop with CASCADE (drops dependent objects)
DROP TABLE tv_post CASCADE;
```
### What Happens During DROP TABLE tv_*
1. **Trigger Removal**: Uninstalls all triggers for this TVIEW
2. **Backing View Drop**: Removes its backing view in `tviews`
3. **Materialized Table Drop**: Removes `tv_<entity>` table
4. **Metadata Cleanup**: Removes entry from system catalogs
5. **Dependency Check**: Fails if other TVIEWs depend on this one
### Cascade Behavior
**CASCADE behavior**: PostgreSQL's standard CASCADE option is supported.
**Drop dependent TVIEWs first** (without CASCADE):
```sql
-- Find dependent TVIEWs (manual inspection for now)
-- Look for TVIEWs that reference this entity in their SELECT
-- Drop in reverse dependency order
DROP TABLE tv_post_comments; -- Depends on tv_post
DROP TABLE tv_post; -- Can now be dropped
```
**Or use CASCADE** (drops all dependents automatically):
```sql
DROP TABLE tv_post CASCADE; -- Drops tv_post and all dependent TVIEWs
```
## ALTER TVIEW
Change a TVIEW's definition or storage with `pg_tviews_create_or_replace()`, which makes
the smallest change (`altered`, `replaced` in place, or `rebuilt`):
```sql
SELECT tviews.pg_tviews_create_or_replace('tv_post', $$ SELECT ... -- new definition $$);
```
## Triggers
Creating a TVIEW installs, on each table its definition reads, a row-level trigger
(`tviews.pg_tview_trigger_handler`) that queues the affected keys, and a
statement-level trigger (`tviews.pg_tview_flush_trigger`) that refreshes them once per
statement. Nothing needs installing by hand. `tviews.pg_tviews_health_check()`
reports missing or orphaned triggers; `SELECT * FROM tviews.pg_tviews_reregister_all()`
re-installs any that are missing.
## Troubleshooting
### TVIEW Creation Errors
**"TVIEW name must follow tv_* convention"**
```sql
-- Fix: Use correct naming
SELECT pg_tviews_create('tv_post', '...'); -- ✅ Correct
SELECT pg_tviews_create('post_view', '...'); -- ❌ Wrong
```
**"Missing required column: pk_post"**
```sql
-- Fix: Include primary key column in SELECT
SELECT p.pk_post as pk_post, ... -- ✅ Correct
SELECT p.id as pk_post, ... -- ❌ Wrong column
```
**"Missing required column: data"**
```sql
-- Fix: Include JSONB data column
jsonb_build_object(...) as data -- ✅ Correct
jsonb_build_object(...) as json -- ❌ Wrong name
```
**"Dependency cycle detected"**
```sql
-- Fix: Restructure to avoid circular dependencies
-- TVIEW A references TVIEW B which references TVIEW A
```
### DROP TABLE tv_* Errors
**"Cannot drop tv_post: other TVIEWs depend on it"**
```sql
-- Fix: Drop dependent TVIEWs first
DROP TABLE tv_post_comments; -- Remove dependency
DROP TABLE tv_post; -- Now works
-- Or use CASCADE
DROP TABLE tv_post CASCADE; -- Drops all dependents
```
### Performance Issues
**Slow initial creation**:
- Complex SELECT with many JOINs
- Large tables (consider WHERE clauses for initial subset)
**Slow refreshes**:
- Deep cascade chains (>3 levels)
- Large JSONB objects (consider jsonb_delta extension)
## Best Practices
### Schema Design
1. **Follow Trinity Pattern**: Use id/pk_/fk_ consistently
2. **Include All FKs**: Both integer (cascade) and UUID (filtering)
3. **Use Meaningful Identifiers**: SEO-friendly slugs where appropriate
4. **Plan Cascade Depth**: Keep dependency chains shallow (<3 levels)
### TVIEW Design
1. **One Entity Per TVIEW**: Focus each TVIEW on a single primary entity
2. **Include GraphQL Fields**: All fields needed for API responses
3. **Use Efficient JOINs**: Prefer INNER JOINs where possible
4. **Test with Real Data**: Verify performance with production-scale data
### Maintenance
1. **Monitor Dependencies**: Track which TVIEWs depend on others
2. **Plan Drop Order**: Know dependency chains for maintenance
3. **Test Changes**: Use staging environment for DDL changes
4. **Backup First**: Always backup before major DDL operations
## See Also
- [FraiseQL Integration Guide](../getting-started/fraiseql-integration.md)
- [API Reference](api.md)
- [Troubleshooting Guide](../operations/troubleshooting.md)