Category System Tables

The category system provides a flexible, hierarchical classification structure that replaces rigid type definitions. It supports infinite depth through self-referencing relationships and organizes categories into namespaced groups.

Category Groups (category_groups)

Purpose: High-level namespaces for different category types (e.g., project_type, task_priority)

Use Case Example: Create a "project_type" group containing Film, Television, etc., and a separate "task_priority" group with Low, Medium, High classifications.

Column	Type	Description
id	UUID	Primary key (auto-generated UUID)
code	TEXT UNIQUE	Unique identifier (e.g., 'project_type')
name	TEXT	Display name (e.g., 'Project Types')
description	TEXT	Human-readable description of the group's purpose and usage
created_at	TIMESTAMPTZ	When the group was created
updated_at	TIMESTAMPTZ	When the group was last updated

Key Features:

• Provides namespacing to prevent category code conflicts
• Allows different classification systems to coexist
• Public read access via RLS policies

Categories (categories)

Purpose: Hierarchical categories with self-referencing parent-child relationships

Use Case Example:

• Film (top-level) → Feature Film (child) → Action (grandchild)
• Television (top-level) → Series (child) → Drama (grandchild)

Column	Type	Description
id	UUID	Primary key (auto-generated UUID)
group_id	UUID	FK to category_groups (namespace)
parent_id	UUID	Self-referencing FK (NULL for top-level)
code	TEXT	Unique within group+parent context
name	TEXT	Display name
description	TEXT	Human-readable description of what this category represents
is_active	BOOLEAN	Whether category is active (default: true)
is_default	BOOLEAN	Whether this is the default category in its group
display_order	INTEGER	Display order within parent or group
is_media	BOOLEAN	Whether this is a media project type (default: false)
is_accounting	BOOLEAN	Whether this is an accounting project type (default: false)
is_personal_accounting	BOOLEAN	Whether this is personal accounting type (default: false)
metadata	JSONB	Flexible storage for category-specific data
created_at	TIMESTAMPTZ	When the category was created
updated_at	TIMESTAMPTZ	When the category was last updated

Key Features:

• Infinite Hierarchy: Add unlimited levels via parent_id
• Project Type Flags: Direct boolean columns (is_media, is_accounting, is_personal_accounting) replace JSONB metadata for better performance and type safety
• Flag Propagation: For media projects, is_media = true is automatically propagated to all child categories via recursive update
• Metadata Storage: Store default_phases, can_have_parent, etc. in JSONB for flexible configuration
• Unique Constraint: (group_id, parent_id, code) ensures uniqueness within context
• Indexed Flags: Partial indexes on is_media, is_accounting, is_personal_accounting (WHERE true) for query performance
• Backward Compatibility: Migrated data from project_type_defaults stored in metadata

Example Hierarchy:

Categories (group_id = 'project_type'):
  ├─ Film (parent_id = NULL)
  │   ├─ Feature Film (parent_id = Film.id)
  │   └─ Short Film (parent_id = Film.id)
  └─ Television (parent_id = NULL)
      ├─ Series (parent_id = Television.id)
      └─ Miniseries (parent_id = Television.id)

Project Categories (project_categories)

Purpose: Many-to-many junction table linking projects to categories

Use Case Example: A project can be tagged with multiple categories like "Film" + "Action" + "High Budget".

Column	Type	Description
id	UUID	Primary key
project_id	UUID	FK to projects table
category_id	UUID	FK to categories table
created_by_user_id	UUID	FK to users - who created this link
created_at	TIMESTAMPTZ	When the link was created

Key Features:

• Primary Key: id (UUID)
• Unique Constraint: (project_id, category_id) - prevents duplicate category assignments
• Cascade Delete: Removes links when project or category is deleted
• Default User: created_by_user_id defaults to auth.uid()
• RLS Policies: Public read access for authenticated users (USING true) - project-level RLS controls access to actual project data

Helper Function: get_category_hierarchy(p_group_code TEXT)

• Returns recursive hierarchy for a category group
• Includes level and path for tree traversal
• Used by service layer to build tree structures

Role Definition Categories (role_definition_categories)

Purpose: Many-to-many junction table linking role definitions to categories

Use Case Example: A "Director" role can be applicable to both "Film" and "Television" categories.

Column	Type	Description
id	UUID	Primary key
role_definition_id	UUID	FK to role_definitions table
category_id	UUID	FK to categories table
created_at	TIMESTAMPTZ	When the link was created

Key Features:

• Primary Key: id (UUID)
• Unique Constraint: (role_definition_id, category_id) - prevents duplicate category assignments
• Cascade Delete: Removes links when role definition or category is deleted
• RLS Policies: Authenticated read-only. Junction table managed by service_role
• Indexed: Both foreign keys indexed for query performance