System Tables

Addresses

Addresses (addresses)

Purpose: Store physical addresses for billing, shipping, and vendor locations

Use Case Example: Store the production office address, vendor billing addresses, or equipment delivery locations.

Column	Type	Description
id	UUID	Unique identifier
address_line_1	TEXT	Primary address line (street number and name)
address_line_2	TEXT	Secondary address line (suite, unit, etc.)
city	TEXT	City name
state_province	TEXT	State or province name
postal_code	TEXT	Postal or ZIP code
country	TEXT	Country name
location	geography(Point)	PostGIS geographic point for mapping/distance calculations
metadata	JSONB	JSON metadata including external_address_id (e.g., Google Place ID), provider, etc.
search_query	TSVECTOR	Full-text search vector for fast address matching and deduplication (auto-generated)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

• PostGIS support for geographic queries
• Full-text search support for efficient address matching
• Unique constraint on all address fields combined
• Can be referenced by multiple entities
• RLS Policies:
• SELECT is scoped to referencing-row accessibility: a caller may read an address only if they can see a row that references it — entities.address_id, transactions.billing_address_id / customer_address_id, or the payment_details.bank.address_id JSONB pointer on project_relationships / entities. This is enforced by the caller_can_access_address(uuid) SECURITY INVOKER function (each inner EXISTS runs under the caller's own RLS, so it matches only references the caller can see). Previously SELECT was true — any user could read every address (a cross-tenant PII read hole).
• UPDATE: no policy — addresses are immutable for the authenticated role. addresses are globally deduplicated/shared across tenants (the all-fields unique constraint collapses identical addresses into one row), so an in-place UPDATE — even scoped to caller_can_access_address — let any referencing tenant tamper with a row another tenant also references. There is no legitimate in-place edit: every flow that "changes an address" find-or-creates a new address row via address/server.findOrCreateAddress and re-points the parent FK under the parent's own edit permission. service_role still updates addresses (RLS bypass) for the system geocoding-enrichment path inside findOrCreateAddress.
• Adding a new FK to addresses: add one more EXISTS clause to caller_can_access_address — that function is the single chokepoint; nothing else in the addresses RLS changes.
• Sanctioned RLS bypass for display: private.format_address(uuid) (SECURITY DEFINER, not API-exposed) formats an address WITHOUT re-running caller_can_access_address, for view columns whose address id is already visibility-gated upstream (a visible entities.address_id, or a bank address_id surviving the payment_details field-mask). It is a formatter, not a read surface — any new call site needs a security review against those provenance rules; see VIEWS_AND_FUNCTIONS.md § Field-masking chokepoint helpers.
• INSERT: any authenticated user (a new address is created before it is linked; an orphan discloses nothing). Because the scoped SELECT policy can't see a brand-new, not-yet-referenced row, address creation runs under service-role in the server layer (address/server.findOrCreateAddress); the relationship bank-address bag is resolved in project/server.updateProjectRelationship for the same reason.
• DELETE intentionally omitted — addresses are preserved for audit history and linked records.

Abuse Prevention

Rate Limit Configs (rate_limit_configs)

Purpose: Configurable rate limits by channel and user type

Use Case Example: WhatsApp limited to 5 messages/minute for regular users, 10 for premium users.

Column	Type	Description
id	UUID	Unique identifier
channel	TEXT	Channel to apply limit to
limit_type	TEXT	per_minute/per_hour/per_day/per_instance
message_count	INTEGER	Maximum messages allowed
user_type	TEXT	User category for differentiated limits
is_active	BOOLEAN	Whether this limit is enforced
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

• Flexible limits per channel
• Different limits for user types
• Can be updated without code changes
• Per-instance limits prevent endless conversations
• RLS Policies: Authenticated read-only. Rate limit configuration managed by service_role

User Type Examples:

The user_type field enables differentiated rate limiting based on user categories:

• 'all' (default): Standard rate limits applied to all users

• Example: 5 messages/minute, 60/hour, 200/day

• 'new': Stricter limits for newly registered users (e.g., first 7 days)

• Example: 3 messages/minute, 30/hour, 100/day

• Use case: Prevent spam from newly created accounts

• 'verified': Standard limits for users who have verified their phone/email

• Example: 5 messages/minute, 60/hour, 200/day

• Use case: Normal usage for authenticated users

• 'premium': Higher limits for paid subscribers

• Example: 10 messages/minute, 120/hour, 500/day

• Use case: Enhanced service for paying customers

• 'trusted': Minimal limits for long-standing users with good history

• Example: 20 messages/minute, 240/hour, 1000/day
• Use case: Reward loyal users with fewer restrictions

Implementation Note: The check_rate_limits_and_abuse() function accepts a p_user_type parameter (defaults to 'all') to apply the appropriate limits based on user category.

Limit Types:

The three time-window limits (per_minute, per_hour, per_day) are counted from conversations.rate_limit_data and reset on a clock. per_instance is different in kind:

• per_instance caps the number of messages on a single conversation_instances row (500 for WhatsApp/'all'; check_rate_limits_and_abuse falls back to 500 when no active config row matches). It exists to bound the context an AI conversation carries, not to punish the user.
• It is counted against the ACTIVE instance only, never the sum across the conversation's closed and summarized instances — a historical sum only ever grows, which would make the cap permanent and permanently reject every further message.
• Reaching it produces allowed = false, limit_type = 'instance', reset_at = NULL. Unlike the time-window limits there is nothing to wait for: the caller rolls the conversation onto a fresh instance via rollover_conversation_instance and retries. See VIEWS_AND_FUNCTIONS.md § Messaging System Functions.
• Time-window limits apply a throttle (writing is_throttled/throttled_until back to the conversation); the instance limit writes nothing.

Abuse Patterns (abuse_patterns)

Purpose: Tracks detected abuse patterns in messaging

Use Case Example: User sends identical message 10 times rapidly, system detects spam pattern with 95% confidence.

Column	Type	Description
id	UUID	Unique identifier
user_id	UUID	Reference to user
conversation_id	UUID	Reference to conversation
pattern_type	TEXT	spam/flooding/gibberish/profanity/phishing/api_abuse
confidence_score	NUMERIC	Pattern detection confidence (0.00-1.00)
detected_at	TIMESTAMPTZ	When pattern was detected
sample_messages	TEXT[]	Sample of problematic messages
action_taken	TEXT	warning/throttle/block/report
metadata	JSONB	Additional pattern details
created_at	TIMESTAMPTZ	Creation timestamp

Key Features:

• Multiple pattern types detected
• Confidence scoring for accuracy
• Preserves evidence samples
• Tracks enforcement actions

Abuse Actions (abuse_actions)

Purpose: Progressive penalty system for repeat offenders

Use Case Example: User's first spam gets warning, second gets 15min throttle, third gets 1hr throttle, etc.

Column	Type	Description
id	UUID	Unique identifier
user_id	UUID	Reference to user
action_type	TEXT	Type of action taken
action_severity	INTEGER	1=warning, 2=short throttle, 3=long, 4=block
expires_at	TIMESTAMPTZ	When action expires
created_at	TIMESTAMPTZ	Creation timestamp

Key Features:

• Progressive penalties increase
• Time-based expiration
• 30-day lookback for repeat offenses
• Automatic enforcement

Export System

The export system enables users to download data exports (transactions, compliance documents, etc.) with associated attachments in a ZIP format. Exports are processed asynchronously, stored temporarily in the exports storage bucket, and made available via time-limited signed URLs.

Exports (exports)

Purpose: Tracks export requests, their processing status, and download information

Schema:

Column	Type	Description
id	UUID	Unique identifier for the export
user_id	UUID	User who requested the export
project_id	UUID	Project the export is for (nullable for organisation-level exports)
organisation_id	UUID	Organisation the export belongs to
type	export_type	Type of export: transactions, compliance
status	export_status	Current status: pending, processing, completed, failed
file_path	TEXT	Path to the zip file in the exports bucket (e.g., user_id/transactions_export_timestamp.zip)
download_url	TEXT	Current signed URL for downloading (valid until expires_at)
file_size	BIGINT	Size of the exported file in bytes
record_count	INTEGER	Number of records included in the export
filters	JSONB	JSON object containing the filters applied during export (for audit trail)
error_message	TEXT	Error details if the export failed
expires_at	TIMESTAMPTZ	When the download link expires (typically 24 hours after completion)
metadata	JSONB	Additional metadata: {download_link_regenerations, last_regenerated_at, etc.}
created_at	TIMESTAMPTZ	When the export was requested
updated_at	TIMESTAMPTZ	Last time the export record was updated
created_by_user_id	UUID	User who created the export (default: auth.uid())
updated_by_user_id	UUID	User who last updated the export (default: auth.uid())

Key Features:

• Tracks export lifecycle from request to completion
• Stores signed download URLs with expiration times
• Supports multiple export types (transactions, compliance, etc.)
• Includes audit trail with filters and metadata
• Links to organisation and optionally to project
• Automatic cleanup after expiration

Indexes:

• idx_exports_user_id - User's exports
• idx_exports_project_id - Project-specific exports
• idx_exports_organisation_id - Organisation exports
• idx_exports_status - Query by status
• idx_exports_created_at - Order by creation time
• idx_exports_expires_at - Find expired exports (partial index where status = 'completed')

RLS Policies:

• Users can view their own exports
• Users can create exports
• Users can update their own exports
• Service role has full access

Storage Bucket: exports

• Private bucket (requires signed URLs)
• File structure: {user_id}/{export_filename}.zip
• 500MB max file size
• Allowed MIME types: application/zip, application/x-zip-compressed, application/octet-stream
• RLS policies ensure users can only access their own export files

Notification Templates:

• export_ready - Sent when export is complete (in-app + optional email)
• export_failed - Sent when export fails (in-app + optional email)

Storage Buckets Summary

Bucket	Public	Contents	Read access	Write access
files	No	Tenant data: receipts, invoices, contracts, identity documents	Uploader, OR a member of the attachment's project (via attachments join + caller_accessible_project_ids)	Members of the org named in the path (organisations/<code>/...), or the user's own users/...
exports	No	Per-user data exports (ZIP)	The exporting user only ({user_id}/... folder scope)	The exporting user only
public-assets	Yes	Global, non-tenant reference assets (provider logos, CSV-import templates)	Any authenticated user	Service-role only (seed / admin)

• The files bucket is tenant-scoped: its SELECT policy joins storage.objects.name back to public.attachments.storage_path_or_url ('files/' || name) and admits the row only when the caller uploaded it or can access its project_id. The INSERT policy scopes uploads to the caller's organisation via caller_can_access_organisation_code((storage.foldername(name))[2]) (a SECURITY DEFINER helper — a security_invoker view does not resolve inside a storage.objects policy). UPDATE/DELETE remain owner-scoped.
• Global reference assets (provider logos, import templates) live in public-assets, NOT files, so the private bucket carries no world-readable carve-out.