Entity Index

[martastain]

Problem

Project data is correctly split into per-entity tables (folders, tasks, products, versions, representations, workfiles), but that makes cross-type querying expensive and awkward:

• Building an entity path often requires joining entity tables with hierarchy.
• Activity filtering by entity properties is hard because activity_feed only joins entity_paths, which currently stores just entity_id, entity_type, and path.
• Inherited attributes are not directly queryable for non-folder entities.
• Queries that need a mixed set of entity types end up re-encoding the hierarchy logic repeatedly.

This becomes more painful at AYON scale, especially with projects around:

• ~10 000 folders
• ~1 000 000 versions

Current relevant pieces

• hierarchy is a materialized view over folders, used to derive folder paths.
• exported_attributes stores inherited folder attributes.
• entity_paths exists as a placeholder table and is already joined into activity_feed.
• Folder writes already trigger REFRESH MATERIALIZED VIEW project_<name>.hierarchy.
• Rebuilding inherited attributes currently does a project-wide pass after refreshing hierarchy.

That means there is already an accepted pattern for maintaining derived project-local query structures, but the existing structures are folder-centric and do not solve mixed-entity filtering.

Proposed direction

Introduce a real table per project schema called entity_index.

This table is not the source of truth. It is a denormalized query surface optimized for:

• cross-type filtering
• activity feed joins
• subtree/path filters
• inherited/effective attribute filters
• fast lookup of canonical paths without reconstructing them on every query

Recommended shape

Use a hybrid design: typed columns for common filters and JSONB for entity-specific data.

Using only a single metadata JSONB column would make the table flexible, but it would also push too much of the important query surface into expression indexes and JSON operators. The hot fields should stay explicit.

Suggested shape:

entity_index(
    entity_id UUID PRIMARY KEY,
    entity_type VARCHAR NOT NULL,

    active BOOLEAN NOT NULL,
    status VARCHAR,

    name VARCHAR,

    folder_id UUID,
    product_id UUID,
    version_id UUID,
    task_id UUID,
    parent_id UUID,

    folder_path VARCHAR NOT NULL,
    path VARCHAR NOT NULL,

    search_vector tsvector,

    metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
    attrib JSONB NOT NULL DEFAULT '{}'::JSONB,

    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ
)

Why both folder_path and path

• folder_path gives one comparable hierarchy anchor across all entity types.
• path gives the entity's own canonical path.

Examples:

• folder: folder_path = "assets/charA", path = "assets/charA"
• product: folder_path = "assets/charA", path = "assets/charA/modelMain"
• version: folder_path = "assets/charA", path = "assets/charA/modelMain/v003"
• representation: folder_path = "assets/charA", path = "assets/charA/modelMain/v003/exr"
• task: folder_path = "assets/charA", path = "assets/charA/rig"

folder_path is the better default for subtree filters in feed-style queries. path is the better default for display and exact lookup.

What goes into metadata

Only entity-specific fields that are useful for filtering or display and are not worth promoting to dedicated columns.

Examples:

• common: tags, created_by, updated_by
• folder: folder_type, label
• task: task_type, assignees
• product: product_type, product_base_type
• version: version, author
• representation: representation_name, traits, files
• workfile: original path

Foreign keys on lineage columns

folder_id, product_id, version_id, and task_id can reference the source entity tables.

That is a good fit for the denormalized index because those columns are not polymorphic; each one always points to one concrete source table.

Recommended pattern:

• folder_id -> folders(id)
• product_id -> products(id)
• version_id -> versions(id)
• task_id -> tasks(id)

Using ON DELETE CASCADE on those lineage references is reasonable, because:

• it helps cleanup when source entities are removed
• it protects against orphaned index rows
• it reduces the amount of explicit delete bookkeeping needed during subtree deletes

Important caveat: entity_id itself cannot be a normal foreign key, because it is polymorphic and may refer to different source tables depending on entity_type.

So the practical model is:

• entity_id: no DB-level FK, maintained by rebuild/upsert logic
• lineage columns: normal FKs with ON DELETE CASCADE

This is still worth doing, because most useful ancestry relationships in the index are expressed through the lineage columns anyway.

What goes into attrib

attrib should contain the effective attributes seen by queries after inheritance has been applied.

That is the important part if the goal is to make filters such as "all activities referencing entities where attrib.xxx = y" practical.

Using the name attrib is better for consistency with the rest of the schema, even though the value stored here is the resolved/effective attribute set, not necessarily the raw entity-local attribute payload from the source table.

Keeping attrib separate from metadata is useful because:

• the semantics are different
• rebuild rules are different
• indexing strategy is different

search_vector

Adding a search_vector column is a good extension of this design.

It fits naturally into entity_index, because the same denormalized row already has the pieces needed to build a useful cross-entity full-text document.

Typical inputs would be:

• name
• path
• selected labels from metadata
• selected searchable values from attrib

The exact mix should stay selective. Not every JSON key belongs in full-text search, especially on large projects.

Recommended usage:

• store search_vector physically on the row
• populate it during the same upsert/rebuild flow as path, metadata, and attrib
• add a GIN index on it

That gives a practical full-text search surface across folders, tasks, products, versions, and representations without rebuilding unions over many source tables.

Why not a normal view

A plain view does not solve the main problem.

It would still perform the joins and path reconstruction at query time. Even if the underlying tables have indexes, the planner still has to execute a cross-table union/join shape for each query. That is exactly the complexity this feature is trying to hide.

Use a plain view only if the goal is API simplification, not performance or cross-type filtering.

Why not a materialized view

A materialized view is attractive because it can be indexed, but it is a poor fit here.

Reasons:

• PostgreSQL refreshes a materialized view as a whole.
• Even REFRESH MATERIALIZED VIEW CONCURRENTLY still rebuilds the entire result.
• With up to 1 000 000 versions, refreshing the whole index after every relevant change would be too expensive.
• Staleness becomes hard to reason about for activity feed and interactive filters.

Materialized views work well here for small, naturally global derived structures like folder hierarchy. They are much less suitable for a large mixed-entity index that should react incrementally to writes.

Why not pure row triggers

Pure row triggers on every entity table would keep data fresh, but they are likely too heavy and too hard to maintain once path and inheritance invalidation are included.

The hard cases are not simple entity updates. The hard cases are:

• folder rename
• folder reparent/move
• folder inherited attribute change
• project-level inheritable attribute change

Those events affect subtrees, not single rows. Implementing all of that as row-level PL/pgSQL trigger logic would make writes harder to reason about and harder to debug.

Recommended implementation model

Recommendation: use a real table with incremental rebuild functions, not a view and not a materialized view.

More specifically:

1. Maintain entity_index as a physical table.
2. Keep per-row upserts/deletes incremental for simple entity writes.
3. Handle subtree-sensitive changes with set-based rebuild functions.
4. Keep a full-project rebuild path as a repair/migration tool.

This can be driven either from the application layer or from lightweight triggers that only mark entities/scopes as dirty.

Best practical option

The safest first implementation is a hybrid table + rebuild-functions approach:

• simple entity change: upsert one row
• entity delete: delete one row
• folder rename/move: rebuild affected subtree paths
• folder/project inheritable attribute change: rebuild affected effective attributes
• searchable field change: rebuild search_vector for affected rows
• migration/repair: rebuild whole project

This keeps the expensive logic set-based and explicit.

App-managed vs trigger-managed upkeep

App-managed upkeep

Pros:

• easier to express complex logic
• can reuse existing hierarchy and inherited-attribute rebuild flow
• easier to test in Python than deep trigger logic

Cons:

• depends on all write paths going through the application

Trigger-managed dirty marking

Pros:

• safer if multiple write paths exist
• database guarantees that affected scope is recorded

Cons:

• still needs a rebuild worker/function for anything beyond trivial per-row changes

Recommendation

If almost all writes already go through the AYON server entity layer, keep the actual rebuild logic in the application layer and optionally use lightweight triggers only to record dirty scopes.

Do not put full subtree recalculation directly into row triggers.

Build strategy

Initial backfill

Create the table empty, then populate it with set-based inserts:

1. folders from folders + hierarchy + exported_attributes
2. tasks joined to folder path
3. products joined to folder path
4. versions joined to products and folder path
5. representations joined to versions/products/folder path
6. workfiles joined to tasks and folder path

This should be a dedicated rebuild command/function, because it will also be needed for repair and migrations.

Incremental maintenance rules

Folder insert/update

Update the folder's own row immediately.

If any of these changed:

• name
• parent_id
• inheritable attrib

then rebuild the subtree, because descendants depend on that folder for path and/or effective attributes.

Product update

Usually single-row upsert.

If product name changes, rebuild descendants that include product path information, especially versions and representations.

Version update

Usually single-row upsert.

If fields used in path or hot filters change, rebuild the row and descendant representations.

Representation/task/workfile update

Usually single-row upsert.

Delete

Delete indexed rows together with source entity deletion. Cascading entity deletes should translate into cascading index deletes, either explicitly or by rebuilding the affected scope.

Indexing strategy

Recommended initial indexes:

• primary key on entity_id
• btree on (entity_type)
• btree on (entity_type, status)
• btree on (folder_id) for contained-entity lookups
• btree on (product_id) and (version_id) where relevant
• btree on (updated_at) if feed joins will sort/filter by recency
• GIN trigram index on folder_path
• GIN trigram index on path
• GIN on search_vector
• GIN on metadata using jsonb_path_ops
• GIN on attrib using jsonb_path_ops

Then add expression indexes only for genuinely hot keys, for example:

• (metadata->>'folder_type')
• (metadata->>'product_type')
• (attrib->>'fps')

The main goal is to avoid over-indexing a table that may contain more than a million rows.

Query impact

This would simplify several query classes significantly.

Product path lookup

Instead of joining products to hierarchy, queries can read entity_index.path directly for entity_type = 'product'.

Activity feed filtering

activity_feed could join entity_index instead of entity_paths, exposing:

• current entity_path
• folder_path
• status
• metadata
• attrib

That makes filters like these much simpler:

• activities on assets under assets/charA/%
• activities referencing entities with status = 'approved'
• activities referencing folders of folder_type = 'Shot'
• activities referencing entities with effective attribute fps = 24

Consistency model

This design needs a clear answer to one question:

Should the index be transactionally up to date, or is short post-commit lag acceptable?

If strict read-after-write behavior is required, keep maintenance synchronous for the changed row and for any explicitly rebuilt subtree.

If short lag is acceptable, dirty-scope queuing becomes much more attractive for heavy subtree updates.

For AYON, a good default is likely:

• synchronous for direct row changes
• synchronous subtree rebuild on folder moves/renames if scope is small
• background rebuild only for clearly large scopes or admin repair operations