Tax & Tagging Tables

Tax Configuration

Tax Schemes (tax_schemes)

Purpose: Tax configuration for different regions/productions

Use Case Example: Create "California Production" scheme with 8.5% sales tax and "Georgia Production" with 4% tax plus special incentives.

Column	Type	Description
id	UUID	Unique identifier
name	TEXT	Scheme name (e.g., "US Standard")
country_code	TEXT	Country this scheme applies to (FK to countries)
is_active	BOOLEAN	Whether scheme is available for use (default: true)
rules	TEXT	Tax eligibility rules for AI prompt customization
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Constraints:

• UNIQUE constraint on country_code ensures only one tax scheme per country for reliable auto-population when creating projects

Tax Rates (tax_rates)

Purpose: Specific tax rates within tax schemes

Use Case Example: UK VAT scheme has Standard Rate (20%), Reduced Rate (5%), Zero Rate (0%), and Exempt categories.

Column	Type	Description
id	UUID	Unique identifier
tax_scheme_id	UUID	Reference to the parent tax scheme
label	TEXT	Tax rate label (e.g., "Standard Rate", "Zero Rate")
description	TEXT	Detailed description of when this rate applies
percentage	NUMERIC	Tax percentage (e.g., 0.20 for 20%)
is_default	BOOLEAN	Whether this is the default rate for the scheme
is_active	BOOLEAN	Whether this rate is currently available for use
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

• Multiple rates per tax scheme
• Unique constraint on (tax_scheme_id, label)
• Support for zero and exempt rates
• RLS Policies: Authenticated read-only. Writes managed by service_role (reference data)

Tagging System

Tags (tags)

Purpose: Flexible tagging system with polymorphic ownership

Use Case Example: Create global tags like "Important" or "Review", organisation-specific tags like "Marketing", or project-specific tags like "Phase 1".

Column	Type	Description
id	UUID	Unique identifier
name	TEXT	Display name of the tag
slug	TEXT	URL-safe version of name (auto-generated)
color	TEXT	Hex color code for UI display (e.g., #FF5733)
description	TEXT	Optional description of the tag
owner_id	UUID	ID of the entity that owns this tag (NULL for global tags)
owner_type	TEXT	Type of owner entity (e.g., organisations, projects, entities)
category	tag_category	Tag category: Compliance or Transaction
metadata	JSONB	Additional tag properties (keywords, etc.)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who created this tag
updated_by_user_id	UUID	User who last updated this tag

Key Features:

• Polymorphic ownership - tags can belong to any entity type
• Global tags (owner_id = NULL) available system-wide
• Scoped tags belong to specific entities
• Auto-generated slugs for URL-safe identifiers
• Color support for visual differentiation

Taggings (taggings)

Purpose: Associate tags with any entity type (polymorphic association)

Use Case Example: Apply the "Important" tag to a transaction, "Review" tag to a budget, or "Phase 1" tag to a schedule item.

Column	Type	Description
id	UUID	Unique identifier
tag_id	UUID	Reference to the tag being applied
taggable_id	UUID	ID of the entity being tagged
taggable_type	TEXT	Type of entity being tagged (e.g., entities, transactions, budgets, projects)
metadata	JSONB	AI-generated metadata including confidence, relevance, and reasoning for the tag assignment
created_at	TIMESTAMPTZ	When the tag was applied
created_by_user_id	UUID	User who applied this tag

Key Features:

• Polymorphic association - any entity can be tagged
• Unique constraint prevents duplicate tag applications
• Tracks who applied each tag
• Cascade delete when tag is removed
• AI metadata support for storing confidence scores, relevance levels, and reasoning