Public Schema - User Experience Layer

Owner: Frontend Team Access: All authenticated users (with RLS)

users

• User profiles and preferences (extends Supabase Auth)
• Usage tracking for search counts and activity monitoring

searches Table

Stores all user-initiated searches and their processing status. Powers the "My Searches" dashboard and provides search result persistence.

Field Definitions

Field	Type	Description	Enum Values
id	uuid	Primary key	-
user_id	uuid	Links to authenticated user (FK to auth.users)	-
query	text	Original search term entered by user	-
status	search_status_enum	Processing status of the search	running, completed, failed, result_count_exceeds_limit
results_count	integer	Number of entities found and processed	-
created_at	timestamp	Search initiation timestamp	-
completed_at	timestamp	Search completion timestamp	-

Business Logic:

• New searches start with `status = 'running'
• Status updated by store_discovery_results RPC function
• result_count_exceeds_limit status indicates >20 results found (user must refine search)
• Links to found entities via search_entities junction table

search_entities Table

A junction table that links searches to the entities they discovered, maintaining the history of search results.

Field Definitions

Field	Type	Description	Enum Values
id	uuid	Primary key	-
search_id	uuid	Links to the parent search (FK to searches.id)	-
entity_id	uuid	Links to the discovered entity (FK to content.entities.id)	-
result_order	integer	The position of the entity in the search results list	-
created_at	timestamp	Timestamp of when the entity was discovered	-

Business Logic:

• Created automatically by the store_discovery_results RPC function.
• Enables "My Search History" functionality in the frontend.
• Survives entity processing status changes (e.g., from discovered to xml_ready to completed).

chat_sessions

• Individual chat conversations tied to specific entities
• Allows multiple chat sessions per company

chat_messages

• Individual messages within chat sessions
• Stores user questions and AI responses with source citations

Views

search_entities_with_data

• Combines search results with entity details for efficient frontend queries
• Joins public.search_entities with content.entities to provide complete entity information
• Preserves search result ordering and timestamps for consistent user experience
• Used by the "My Searches" dashboard to display search history with entity details
• Security: Uses security_invoker = true to inherit caller's permissions
• Access: SELECT granted to authenticated users
• Fields: search_id entity_id result_order search_entity_created_at name legal_form register_court register_number address si_document_xml si_document_retrieved_at processing_status entity_created_at entity_updated_at