All database tables across 53 migrations. Tables are grouped by migration and listed with key columns, purpose, and the service that primarily uses them.
Count: 177 unique tables across 53 migrations (180 CREATE TABLE statements; organizations, intentions, and context_snapshots are each declared twice across different migrations with IF NOT EXISTS). All tables have owner_id + RLS unless noted.
Core Entities (Migration: 00001_initial_schema.sql)
Extensions enabled: uuid-ossp, pgcrypto, vector (pgvector)
| Table | Key Columns | Purpose | Used By |
|---|
| persons | id, owner_id, name, emails (jsonb), phones (jsonb), addresses (jsonb), tags (jsonb), birthday, notes, avatar_url, sources (jsonb) | Contact records | DataService |
| organizations | id, owner_id, name, domain, industry, address (jsonb), phone, website, sources (jsonb) | Company/org records | DataService |
| channels | id, owner_id, type (phone/email/sms/chat), provider, provider_channel_id, display_name, is_active, config (jsonb) | Communication channel registrations | ChannelService |
| conversations | id, owner_id, channel_id, participants (jsonb), subject, status (active/archived/closed), tags (jsonb), last_message_at | Conversation threads | DataService |
| messages | id, owner_id, conversation_id, channel_id, sender_id, sender_type, direction (inbound/outbound), content_type, body, metadata (jsonb) | Individual messages in conversations | DataService |
| attachments | id, owner_id, message_id, filename, mime_type, size_bytes, storage_path | File attachments on messages | DataService |
| events | id, owner_id, title, description, start_at, end_at, all_day, recurrence (jsonb), location, attendees (jsonb), source, source_id, reminders (jsonb) | Calendar events | DataService |
| locations | id, owner_id, name, label (home/work/gym/custom), latitude, longitude, radius_m, address | Named location definitions | DataService |
| devices | id, owner_id, name, platform (android/ios/windows/macos/linux), device_token, capabilities (jsonb), last_seen_at, is_active | Registered user devices | DataService |
| location_history | id, owner_id, device_id, latitude, longitude, accuracy_m, altitude_m, speed_mps, heading, timestamp, location_id | GPS location history stream | DataService |
| tasks | id, owner_id, title, description, status (pending/in_progress/done/cancelled), priority (low/medium/high/urgent), due_at, completed_at, tags (jsonb), assigned_to | Task/todo items | DataService |
| documents | id, owner_id, title, content, mime_type, storage_path, size_bytes, tags (jsonb), source, source_id, summary | Document store | DataService |
| rules | id, owner_id, name, description, conditions (jsonb), actions (jsonb), is_active, priority, last_triggered_at, trigger_count | Automation rules (DSL) | RulesService |
| job_records | id, owner_id, queue (realtime/interactive/background), event_type, payload (jsonb), status, attempts, max_attempts, error, started_at, completed_at | Job execution log | Job Queue |
| audit_log | id, owner_id, actor_id, actor_type, action, resource_type, resource_id, details (jsonb), ip_address, user_agent, correlation_id, created_at | Append-only audit trail (no UPDATE/DELETE) | AuditService |
| embeddings | id, owner_id, entity_type, entity_id, embedding (vector(1536)), model, created_at | pgvector embeddings for semantic search | EmbeddingPipelineService |
Intentions (Migration: 00002_phase2_intentions.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| intentions | id, owner_id, type, title, description, conditions (jsonb), actions (jsonb), status, priority, schedule (jsonb), context (jsonb), expires_at, resolved_at | Future-plane scheduled intentions | IntentionsService |
| presence_snapshots | id, owner_id, location_id, device_id, detected_at, confidence, is_current | Location presence snapshots for rules | RulesService |
AI (Migration: 00003_phase3_ai.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| agent_conversations | id, owner_id, title, model, messages (jsonb), context (jsonb), token_count, status | AI agent chat history | AgentService |
| workflow_definitions | id, owner_id, name, description, steps (jsonb), triggers (jsonb), is_active | Multi-step workflow definitions | WorkflowService |
| workflow_runs | id, definition_id, owner_id, status, current_step, step_results (jsonb), context (jsonb), started_at, completed_at, error | Workflow execution runs | WorkflowService |
| llm_usage | id, owner_id, model, provider, prompt_tokens, completion_tokens, total_tokens, cost_usd, latency_ms, request_type | LLM usage tracking and cost accounting | AgentService |
LLM Routing Rules (Migration: 00037_llm_routing_rules.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| llm_routing_rules | id, owner_id, name, priority, conditions (jsonb), model, provider, max_tokens, temperature, is_active | Per-user LLM model routing rules | AgentService |
Federation (Migration: 00004_phase4_federation.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| dids | id, owner_id, method, did_document (jsonb), private_key_encrypted, public_key, is_primary | Decentralised Identifiers | DIDService |
| verifiable_credentials | id, owner_id, issuer_did, subject_did, type (jsonb), credential_subject (jsonb), proof (jsonb), status (active/revoked/expired), issued_at, expires_at | W3C Verifiable Credentials | VCService |
| federated_actors | id, owner_id, actor_uri, inbox_url, outbox_url, public_key_pem, display_name, is_local | ActivityPub actor profiles | ActivityPubService |
| follows | id, owner_id, follower_uri, following_uri, status (pending/accepted/rejected), followed_at | ActivityPub follow relationships | ActivityPubService |
| activities | id, owner_id, type, actor_uri, object (jsonb), target (jsonb), published_at, is_local | ActivityPub activity stream | ActivityPubService |
| didcomm_messages | id, owner_id, from_did, to_did, type, body (jsonb), encrypted_body, thread_id, status (sent/delivered/read), sent_at | DIDComm v2 encrypted messages | DIDCommService |
Marketplace (Migration: 00005_phase5_marketplace.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| listings | id, owner_id, title, description, category, price (jsonb), status (draft/active/sold/expired), tags (jsonb), images (jsonb), location_id, visibility | Marketplace item listings | MarketplaceService |
| data_products | id, owner_id, listing_id, data_type, schema (jsonb), sample_data (jsonb), privacy_level, access_policy (jsonb), row_count | Privacy-preserving data products | DataProductBuilder |
| marketplace_transactions | id, buyer_id, seller_id, listing_id, amount (jsonb), status (pending/completed/refunded/disputed), payment_method, escrow_id, completed_at | Purchase transactions | MarketplaceService |
| declarations | id, owner_id, type (offer/need), title, description, category, requirements (jsonb), availability (jsonb), status, location_id | Offer/Need declarations | ResourceCoordinationService |
| exchanges | id, offer_id, need_id, offerer_id, requester_id, status (proposed/accepted/in_progress/completed/cancelled), terms (jsonb), started_at, completed_at | Resource exchange workflows | ResourceCoordinationService |
| trust_relationships | id, owner_id, subject_id, trust_level, context, evidence (jsonb), established_at, expires_at | Explicit trust declarations | ReputationService |
| reputation_scores | id, owner_id, domain, score, confidence, attestation_count, decay_factor, last_updated | Multi-domain reputation scores | ReputationService |
Governance (Migration: 00006_phase6_governance.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| daos | id, owner_id, name, description, governance_model (direct/representative/liquid), membership_policy, treasury (jsonb), charter (jsonb) | DAO definitions | DAOService |
| dao_members | id, dao_id, user_id, role (member/delegate/admin), voting_power, delegated_to, joined_at | DAO membership | DAOService |
| proposals | id, dao_id, author_id, title, description, type (policy/budget/membership/amendment), status (draft/discussion/voting/passed/rejected/executed), voting_config (jsonb), discussion_ends_at, voting_ends_at | Governance proposals | ProposalService |
| votes | id, proposal_id, voter_id, choice, weight, reasoning, delegated_from, cast_at | Individual votes on proposals | VotingService |
| time_credits | id, owner_id, balance, earned_total, spent_total, currency (hours) | Time bank credit balances | TimeBankService |
| time_credit_transactions | id, from_id, to_id, amount, description, type (earn/spend/transfer), reference_type, reference_id, created_at | Time credit transfers | TimeBankService |
| disputes | id, owner_id, respondent_id, type, title, description, evidence (jsonb), status (filed/investigating/mediation/arbitration/resolved/dismissed), resolution, resolved_at | Dispute cases | DisputeService |
| moderation_actions | id, owner_id, moderator_id, target_type, target_id, action (warn/suspend/remove/restrict), reason, expires_at | Moderation action log | ModerationService |
Web3 (Migration: 00007_phase8_web3.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| wallet_connections | id, owner_id, chain, address, public_key, is_primary, connected_at | Crypto wallet connections | WalletService |
| time_credit_bridge_requests | id, owner_id, amount, direction (on_chain/off_chain), chain, tx_hash, status (pending/confirming/completed/failed) | Bridge credits to/from blockchain | TimeCreditBridgeService |
Federated Marketplace + Calling (Migrations: 00008, 00009, 00010)
| Table | Key Columns | Purpose | Used By |
|---|
| federated_listing_cache | id, source_hub_url, original_listing_id, title, description, category, price (jsonb), seller_actor_uri, cached_at, expires_at | Cached listings from federated hubs | FederatedMarketplaceService |
| hub_subscriptions | id, local_hub_url, remote_hub_url, status (active/paused/expired), categories (jsonb), subscribed_at | Hub-to-hub marketplace subscriptions | FederatedMarketplaceService |
| call_sessions | id, owner_id, call_sid, direction, from_number, to_number, status, duration_s, recording_url, started_at, ended_at | Twilio call session records | TwilioCallingService |
| agent_memory | id, owner_id, conversation_id, role, content, embedding (vector), importance, accessed_at, expires_at | Agent long-term memory entries | AgentMemoryService |
| agent_tool_calls | id, conversation_id, tool_name, input (jsonb), output (jsonb), latency_ms, status, called_at | Agent tool execution log | ToolRegistry |
Privacy + Notifications (Migrations: 00011, 00012)
| Table | Key Columns | Purpose | Used By |
|---|
| retention_policies | id, owner_id, entity_type, max_age_days, action (delete/anonymize/archive), is_active | Per-entity data retention rules | PrivacyEngine |
| consent_records | id, owner_id, purpose, scope, granted, granted_at, expires_at, revoked_at | GDPR-style consent tracking | PrivacyEngine |
| pii_scan_log | id, owner_id, entity_type, entity_id, fields_found (jsonb), risk_level, scanned_at | PII detection scan results | PrivacyEngine |
| notification_subscriptions | id, owner_id, device_id, endpoint, keys (jsonb), is_active, subscribed_at | Push notification subscriptions | NotificationService |
| outbound_webhooks | id, owner_id, url, events (jsonb), secret, is_active, created_at | Registered webhook endpoints | OutboundWebhookService |
| webhook_delivery_log | id, webhook_id, event_type, payload (jsonb), status_code, response_body, attempts, delivered_at | Webhook delivery attempts | OutboundWebhookService |
Presence + Audit (Migrations: 00013, 00014)
| Table | Key Columns | Purpose | Used By |
|---|
| user_presence | id, owner_id, status (online/away/busy/offline), last_seen_at, device_id, metadata (jsonb) | Real-time user presence | PresenceService |
| collaboration_sessions | id, owner_id, type, resource_type, resource_id, participants (jsonb), started_at, ended_at | Real-time collaboration sessions | PresenceService |
| collaboration_cursors | id, session_id, user_id, position (jsonb), color, updated_at | Cursor positions in collaborative editing | PresenceService |
| compliance_reports | id, owner_id, type, period_start, period_end, data (jsonb), status, generated_at | Compliance report records | AuditService |
| audit_alert_rules | id, owner_id, name, conditions (jsonb), notify_channels (jsonb), is_active | Alert rules on audit patterns | AuditService |
Plugins + Documents (Migrations: 00015, 00016)
| Table | Key Columns | Purpose | Used By |
|---|
| plugin_registry | id, name, version, description, author, category, manifest (jsonb), icon_url, downloads, is_verified, published_at | Plugin marketplace catalog | PluginRegistryService |
| user_plugins | id, owner_id, plugin_id, is_enabled, config (jsonb), installed_at | User-installed plugins | PluginRegistryService |
| plugin_ratings | id, plugin_id, user_id, rating, review, created_at | Plugin ratings and reviews | PluginRegistryService |
| document_jobs | id, owner_id, document_id, type (analyse/classify/extract/summarise), status, result (jsonb), error, created_at, completed_at | Document processing job queue | DocumentIntelligenceService |
| document_chunks | id, document_id, owner_id, content, chunk_index, embedding (vector), metadata (jsonb) | Document chunks for RAG | DocumentIntelligenceService |
| document_entities | id, document_id, owner_id, entity_type, entity_value, confidence, position (jsonb) | Entities extracted from documents | DocumentIntelligenceService |
Rules + Scheduled Jobs (Migrations: 00017, 00018)
| Table | Key Columns | Purpose | Used By |
|---|
| rule_templates | id, name, description, category, conditions_template (jsonb), actions_template (jsonb), variables (jsonb), popularity | Pre-built rule templates | RuleTemplateService |
| rule_execution_log | id, rule_id, owner_id, event_type, matched, actions_fired (jsonb), latency_ms, executed_at | Rule execution audit trail | RuleAnalyticsService |
Migration 00018 sets up pg_cron scheduled jobs (no new tables — uses cron.schedule() for retention, audit alerts, presence sync, weekly digest, document job expiry).
Rate Limiting (Migration: 00019_phase16_rate_limits.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| rate_limit_events | id, owner_id, endpoint, window_start, request_count, limited_at | Rate limit hit tracking | RateLimiterService |
| circuit_breaker_snapshots | id, service_name, state (closed/open/half_open), failure_count, last_failure_at, snapshot_at | Circuit breaker state persistence | CircuitBreakerService |
Event Store (Migration: 00020_phase17_event_store.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| event_store | id, stream_id, event_type, data (jsonb), metadata (jsonb), version, created_at | Immutable event store for event sourcing | EventStoreService |
| event_snapshots | id, stream_id, data (jsonb), version, created_at | Periodic snapshots for replay optimization | EventStoreService |
Streaming (Migration: 00021_phase18_streaming.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| activity_feed | id, owner_id, actor_id, action, target_type, target_id, data (jsonb), read, created_at | User activity feed items | ActivityFeedService |
Multi-Tenancy (Migration: 00022_phase19_orgs.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| organizations (extended) | id, name, slug, owner_id, plan, settings (jsonb), created_at | Org/tenant accounts (extends core schema) | OrgService |
| org_memberships | id, org_id, user_id, role (owner/admin/member), joined_at | Organisation membership | OrgService |
| org_invites | id, org_id, email, role, token, status (pending/accepted/expired), invited_by, created_at, expires_at | Org invitation tokens | OrgService |
| usage_records | id, org_id, metric, value, recorded_at | Per-org usage metering | UsageService |
| billing_events | id, org_id, type, amount, currency, metadata (jsonb), created_at | Billing event log | UsageService |
Developer API (Migration: 00023_phase20_developer.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| oauth_apps | id, owner_id, name, client_id, client_secret_hash, redirect_uris (jsonb), scopes (jsonb), is_active | Registered OAuth2 applications | OAuthService |
| oauth_auth_codes | id, app_id, user_id, code, redirect_uri, scopes (jsonb), expires_at | OAuth2 authorization codes | OAuthService |
| oauth_tokens | id, app_id, user_id, access_token_hash, refresh_token_hash, scopes (jsonb), expires_at | OAuth2 access/refresh tokens | OAuthService |
| api_keys | id, owner_id, name, key_hash, scopes (jsonb), last_used_at, expires_at, is_active | Developer API keys | OAuthService |
Advanced Search (Migration: 00024_phase21_search.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| saved_searches | id, owner_id, name, query, filters (jsonb), sort_by, notify_on_new, last_run_at | Persistent saved search definitions | SavedSearchService |
| recommendations | id, owner_id, source_type, source_id, target_type, target_id, score, reason, seen, created_at | AI-generated recommendations | RecommendationEngine |
Data Portability (Migration: 00025_phase22_data_portability.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| export_jobs | id, owner_id, format (json/csv/vcard/ical), entity_types (jsonb), filters (jsonb), status, file_path, file_size, started_at, completed_at | Data export job records | ExportService |
| import_jobs | id, owner_id, format, file_path, status, stats (jsonb), errors (jsonb), started_at, completed_at | Data import job records | ImportService |
Preferences (Migration: 00026_phase23_preferences.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| user_preferences | id, owner_id, theme, locale, timezone, notification_settings (jsonb), privacy_settings (jsonb), dashboard_layout (jsonb) | User settings and preferences | UserPreferencesService |
Monitoring + Email (Migration: 00027_phase26_monitoring.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| tracked_errors | id, owner_id, error_type, message, stack_trace, context (jsonb), fingerprint, occurrences, first_seen, last_seen, status (open/acknowledged/resolved) | Error aggregation | ErrorTrackingService |
| email_queue | id, owner_id, to_address, subject, body_html, body_text, status (queued/sending/sent/failed), attempts, sent_at | Transactional email queue | EmailService |
Billing + Feature Flags (Migration: 00028_phase27_billing.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| stripe_customers | id, owner_id, stripe_customer_id, plan, status (active/past_due/cancelled), current_period_end, created_at | Stripe customer records | BillingService |
| checkout_sessions | id, owner_id, stripe_session_id, plan, status, amount, currency, created_at, completed_at | Stripe checkout sessions | BillingService |
| feature_flags | id, name, description, is_enabled, targeting_rules (jsonb), rollout_percentage, created_at | Feature flag definitions | FeatureFlagService |
Security + Analytics (Migration: 00029_phase28_security.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| user_sessions | id, owner_id, token_hash, ip_address, user_agent, device_id, expires_at, created_at, revoked_at | User session management | SessionService |
| analytics_events | id, owner_id, event_name, properties (jsonb), session_id, device_type, created_at | User behaviour analytics events | AnalyticsService |
Data Lineage (Migration: 00030_phase29_lineage.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| entity_lineage | id, entity_type, entity_id, field_name, source, source_id, confidence, recorded_at | Field-level data provenance | LineageService |
| merge_candidates | id, owner_id, entity_type, entity_a_id, entity_b_id, similarity_score, status (pending/merged/rejected), detected_at | Duplicate entity detection | EntityMergeService |
| conflict_resolution_rules | id, owner_id, entity_type, field_name, strategy (newest/highest_confidence/manual/source_priority), priority_sources (jsonb) | Merge conflict resolution rules | EntityMergeService |
Credential Wallet (Migration: 00031_credential_wallet.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| user_credentials | id, owner_id, service_name, credential_type, encrypted_data, metadata (jsonb), is_active, created_at | Encrypted credential storage (Vault) | Plugin Manager / Credential Wallet API |
| Table | Key Columns | Purpose | Used By |
|---|
| tool_jobs | id, owner_id, tool_name, input (jsonb), output (jsonb), status (pending/running/completed/failed), error, priority, created_at, started_at, completed_at | Tool execution job queue | ToolsService |
| fonts | id, name, family, style, weight, format, file_path, created_at | Font registry for document generation | ToolsService |
Audio Intelligence (Migrations: 00033, 00034)
| Table | Key Columns | Purpose | Used By |
|---|
| audio_sessions | id, owner_id, device_id, started_at, ended_at, duration_s, status, source, storage_path, file_size | Audio recording session records | AudioETLService |
| transcripts | id, owner_id, audio_session_id, content, language, confidence, model, segments (jsonb), started_at, completed_at | Audio transcriptions | AudioETLService |
| voice_commands | id, owner_id, transcript_id, command_text, intent, confidence, parameters (jsonb), status (detected/executing/completed/failed), result (jsonb) | Interpreted voice commands | VoiceCommandService |
| extracted_intelligence | id, owner_id, transcript_id, type (action_item/decision/question/commitment/topic), content, confidence, entity_refs (jsonb), context (jsonb) | Intelligence extracted from audio | AudioETLService |
| daily_briefs | id, owner_id, date, summary, key_items (jsonb), audio_sessions_count, total_duration_s, generated_at | Daily audio intelligence summaries | AudioETLService |
| context_snapshots | id, owner_id, device_id, timestamp, active_app, window_title, url, location (jsonb), calendar_event_id | Device context at time of audio capture | AudioETLService |
| transcript_chunks | id, owner_id, transcript_id, content, chunk_index, start_time_s, end_time_s, speaker_id, embedding (vector), importance_score | Chunked transcript segments for RAG | AudioETLService |
| passive_intents | id, owner_id, transcript_chunk_id, intent_type (reminder/todo/follow_up/meeting/contact), description, confidence, extracted_entities (jsonb), status (detected/confirmed/dismissed/actioned), action_taken (jsonb) | Passive intent detection from conversation | VoiceCommandService |
Device Commands (Migration: 00035_device_commands.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| device_commands | id, owner_id, device_id, module (filesystem/process/clipboard/display/power/network/registry/notifications/input/shell/agent), action, params (jsonb), status (pending/sent/running/completed/failed/timeout), result (jsonb), source, priority, progress (jsonb) | OS-level device command queue | DeviceCommandService |
Migration 00036 extends device_commands with agent module and agent-worker source for AI agent sessions.
Unified Scheduler (Migration: 00038_unified_scheduler.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| schedules | id, owner_id, name, schedule_type, cron_expression, next_run_at, payload (jsonb), is_active | Unified scheduled job definitions | SchedulerService |
| schedule_runs | id, schedule_id, owner_id, status, started_at, completed_at, error | Scheduled job execution history | SchedulerService |
Wiki (Migration: 00040_wiki.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| wiki_pages | id, owner_id, slug, title, body, tags (jsonb), updated_at | Persistent compiled wiki pages | WikiService |
| wiki_links | id, owner_id, from_page_id, to_page_id, link_type | Internal wiki page link graph | WikiService |
Inventory (Migration: 00041_phase41_inventory.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| inventory_items | id, owner_id, name, category, space_id, quantity, metadata (jsonb) | Inventory item records | InventoryService |
| inventory_spaces | id, owner_id, name, parent_space_id, metadata (jsonb) | Hierarchical storage locations | InventoryService |
| inventory_maintenance | id, owner_id, item_id, maintenance_type, performed_at, next_due_at, notes | Item maintenance history | InventoryService |
| inventory_events | id, owner_id, item_id, event_type, occurred_at, metadata (jsonb) | Inventory lifecycle event log | InventoryService |
Migration 00048 (provider_abstraction.sql) adds no new tables — it introduces a provider abstraction layer over existing tables.
Unified Communications Inbox (Migration: 00042_deep_comms.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| inbox_items | id, owner_id, type (call/sms/email/video/calendar_event/voicemail), conversation_id, contact_id, channel_type (twilio_voice/twilio_sms/ms_graph_email/twilio_video), direction, subject, preview, timestamp, read, starred, archived, source_table, source_id | Unified inbox — materialized view of all communication events across channels | DeepCommsService |
Migration also extends conversations with thread_type (default: single_channel) and related_conversation_ids (uuid[]) columns.
Indexes: owner+timestamp (main inbox query), owner+read+timestamp (unread filter), owner+contact+timestamp (contact timeline)
Deep Voice / Business Phone System (Migration: 00043_deep_voice.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| phone_lines | id, owner_id, phone_number, twilio_sid, label, settings (jsonb), is_default | Multi-line phone number management | DeepVoiceService |
| voicemails | id, owner_id, call_session_id, phone_line_id, from_number, contact_id, recording_url, recording_sid, transcription, duration_seconds, listened | Voicemail inbox with transcription | DeepVoiceService |
| call_recordings | id, owner_id, call_session_id, recording_sid, url, duration_seconds | Call recording storage references | DeepVoiceService |
| call_transcripts | id, owner_id, call_session_id, segments (jsonb), full_text, provider (default: whisper) | Call transcriptions with speaker segments | DeepVoiceService |
| call_analyses | id, owner_id, call_session_id, transcript_id, summary, action_items (jsonb), sentiment (positive/neutral/negative), key_topics (text[]), entities (jsonb) | AI analysis of call transcripts | DeepVoiceService |
| ivr_flows | id, owner_id, phone_line_id, name, flow_definition (jsonb), is_active | IVR flow definitions per phone line | DeepVoiceService |
Indexes: phone_lines: owner, unique owner+number; voicemails: owner+created, owner+listened+created; call_recordings: owner, session; call_transcripts: session, owner; call_analyses: session, owner+created; ivr_flows: owner, line+active
Full Email Client (Migration: 00044_email_client.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| email_folders | id, owner_id, name, ms_graph_id, parent_folder_id, unread_count, total_count, folder_type (inbox/sent/drafts/trash/archive/junk/custom) | Email folder hierarchy synced from MS Graph | EmailClientService |
| email_threads | id, owner_id, ms_graph_conversation_id, subject, last_message_at, folder_id, participants (jsonb), snippet, unread, starred, message_count, has_attachments | Email conversation threads | EmailClientService |
| email_signatures | id, owner_id, name, body_html, is_default | User email signatures | EmailClientService |
Migration also extends messages with email-specific columns: email_thread_id, folder_id, cc (jsonb), bcc (jsonb), reply_to, has_attachments, is_draft, subject, from_address, to_addresses (jsonb).
Indexes: email_folders: owner, owner+type, owner+ms_graph_id, parent; email_threads: owner+folder+last_message, owner+unread+last_message, owner+starred+last_message, owner+ms_graph_conv; messages: email_thread+timestamp, owner+folder+timestamp, owner+is_draft (partial)
Video Meetings (Migration: 00045_meetings.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| meetings | id, owner_id, title, room_name (unique), scheduled_start, scheduled_end, status (scheduled/active/ended), settings (jsonb), calendar_event_id, invite_link | Full meeting lifecycle — scheduled through ended | MeetingService |
| meeting_participants | id, meeting_id, contact_id, email, role (host/participant), status (invited/joined/left), joined_at, left_at | Meeting participant roster and attendance | MeetingService |
| meeting_recordings | id, meeting_id, recording_sid, url, duration_seconds, composition_status (enqueued/processing/completed/failed) | Twilio Video recording composition | MeetingService |
| meeting_analyses | id, meeting_id, transcript_id, summary, action_items (jsonb), key_decisions (jsonb), sentiment (positive/neutral/negative), key_topics (text[]), entities (jsonb) | AI analysis of meeting recordings | MeetingService |
RLS for meeting_participants, meeting_recordings, and meeting_analyses uses a subquery to check meetings.owner_id = auth.uid() rather than a direct owner_id column.
Indexes: meetings: owner+status, owner+scheduled, room_name; meeting_participants: meeting, contact; meeting_recordings: meeting; meeting_analyses: meeting
| Table | Key Columns | Purpose | Used By |
|---|
| contact_notes | id, owner_id, contact_id, body | Free-form annotations on contact records | ContactTimelineService |
| contact_merges | id, owner_id, primary_id, merged_id, merged_at, undone_at; UNIQUE (primary_id, merged_id) | Contact deduplication merge history with undo support | ContactTimelineService |
Indexes: contact_notes: owner+contact+created; contact_merges: owner, primary_id, merged_id
Device Push Tokens (Migration: 00047_device_push_tokens.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| device_push_tokens | id, owner_id, device_id, fcm_token, platform (android/ios/web), device_name, is_active | FCM push notification tokens for mobile/web devices | NotificationService / PushService |
Indexes: unique owner+device_id, owner+is_active, fcm_token
Unified Life Architecture — Foundation (Migration: 00049_unified_life_foundation.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| entity_registry | id, owner_id, entity_type, entity_id, uri (unique), display_name, metadata (jsonb) | Stable URIs for every entity across all domains | EntityRegistryService |
| entity_relationships | id, owner_id, from_uri, to_uri, relationship_type, strength, metadata (jsonb), started_at, ended_at | First-class relationships between any two entities | RelationshipService |
| observations | id, owner_id, domain, event_type, entity_uri, occurred_at, location_id, source, payload (jsonb), tags (jsonb) | Universal observation spine — every domain event indexed here | ObservationService |
| observation_links | id, observation_id, linked_entity_uri, link_type | Cross-entity links on observations | ObservationService |
| context_state | id, owner_id, dimension, value, confidence, derived_at, expires_at | Current context per dimension (location, activity, social, etc.) | ContextEngineV2 |
| context_history | id, owner_id, dimension, value, confidence, valid_from, valid_to | Historical context dimension values | ContextEngineV2 |
| context_snapshots | id, owner_id, snapshot_at, full_context (jsonb) | Point-in-time full context snapshots | ContextEngineV2 |
| reaction_log | id, owner_id, action_type, actor_uri, resource_uri, authorization_chain (jsonb), context_snapshot_id, occurred_at | Append-only immutable log of all system actions | ReactionLogService |
Unified Life Architecture — Intentions & PM (Migration: 00050_life_intentions_pm.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| intentions | id, owner_id, type (goal/aspiration/commitment/maintenance/consent), title, description, target_metric, target_value, current_value, status, priority, due_at, context_conditions (jsonb) | Declarative persistent goals with progress tracking | IntentionService |
| projects | id, owner_id, title, description, status (planning/active/on_hold/completed/cancelled), priority, due_at, parent_project_id, tags (jsonb) | Project definitions with hierarchy | ProjectService |
| milestones | id, project_id, owner_id, title, due_at, completed_at, status | Project milestones | ProjectService |
| time_entries | id, owner_id, project_id, task_id, started_at, ended_at, duration_seconds, description | Time tracking on projects and tasks | ProjectService |
| habits | id, owner_id, title, description, frequency (daily/weekly/custom), target_count, reminder_conditions (jsonb), is_active | Recurring behavior definitions | HabitService |
| habit_logs | id, habit_id, owner_id, completed_at, note, streak_count | Habit completion log with streak tracking | HabitService |
Migration also extends tasks with project_id, parent_task_id, estimated_seconds, actual_seconds, subtask_count, completed_subtask_count columns.
Unified Life Architecture — Health, Fitness & Finance (Migration: 00051_life_health_finance.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| health_metrics | id, owner_id, metric_type (heart_rate/blood_pressure/weight/glucose/temperature/spo2/hrv/steps/calories/custom), value, unit, recorded_at, source, notes | Health metric time series | HealthService |
| health_symptoms | id, owner_id, symptom, severity (1-10), onset_at, resolved_at, notes | Symptom tracking | HealthService |
| fitness_activities | id, owner_id, activity_type, title, started_at, ended_at, duration_seconds, distance_m, calories, gps_track (jsonb), splits (jsonb), heart_rate_zones (jsonb), notes | Workout activity records with GPS and analytics | FitnessService |
| sleep_records | id, owner_id, sleep_start, sleep_end, duration_seconds, quality_score, stages (jsonb), notes, source | Sleep session records | HealthService |
| meals | id, owner_id, meal_type (breakfast/lunch/dinner/snack), eaten_at, notes, total_calories, total_protein_g, total_carbs_g, total_fat_g | Meal records | NutritionService |
| meal_items | id, meal_id, owner_id, food_name, quantity, unit, calories, protein_g, carbs_g, fat_g, fiber_g | Individual items within a meal | NutritionService |
| water_intake | id, owner_id, amount_ml, recorded_at | Water intake log | NutritionService |
| dietary_preferences | id, owner_id, preference_type, value, notes | Dietary restrictions and preferences | NutritionService |
| financial_accounts | id, owner_id, name, account_type (checking/savings/credit/investment/loan), institution, balance, currency, last_synced_at | Financial account definitions | FinanceService |
| financial_transactions | id, owner_id, account_id, amount, currency, direction (debit/credit), category, merchant, description, occurred_at, tags (jsonb) | Financial transaction records | FinanceService |
| budgets | id, owner_id, name, category, amount, currency, period (weekly/monthly/annual), start_date, end_date | Budget definitions | FinanceService |
| subscriptions | id, owner_id, name, amount, currency, billing_cycle, next_billing_date, category, is_active, notes | Subscription tracking | FinanceService |
Unified Life Architecture — Collections, Journal & Knowledge (Migration: 00052_life_collections_knowledge.sql)
| Table | Key Columns | Purpose | Used By |
|---|
| collection_types | id, owner_id, name, icon, color, schema_definition (jsonb), is_system | Collection type definitions with custom field schemas | CollectionService |
| collections | id, owner_id, collection_type_id, title, description, tags (jsonb), is_public | User collections | CollectionService |
| collection_items | id, collection_id, owner_id, title, data (jsonb), notes, added_at, sort_order | Items within a collection | CollectionService |
| journal_entries | id, owner_id, body, mood (1-5), energy (1-5), tags (jsonb), written_at, is_private | Journal entries with mood and energy tracking | JournalService |
| media_consumption | id, owner_id, media_type (book/podcast/movie/tv/article/video/music/game), title, creator, status (want/in_progress/completed/abandoned), progress, rating (1-5), started_at, completed_at, notes | Media consumption tracking | MediaService |
| knowledge_captures | id, owner_id, capture_type (note/bookmark/highlight/quote/annotation), content, source_url, source_title, tags (jsonb), captured_at, embedding (vector) | Knowledge capture with semantic indexing | KnowledgeService |
| environment_readings | id, owner_id, location_id, sensor_type (weather/air_quality/noise/light/humidity/temperature/pressure), value, unit, recorded_at, source | Environmental sensor readings | EnvironmentService |
| home_readings | id, owner_id, device_id, reading_type (power/water/gas/temperature/humidity/motion/door/window), value, unit, recorded_at | Home/IoT sensor readings | HomeService |
| utility_bills | id, owner_id, utility_type (electric/gas/water/internet/phone), amount, currency, period_start, period_end, due_date, paid_at | Utility bill records | HomeService |
| learning_subjects | id, owner_id, title, description, category, target_level, current_level, started_at, target_completion_at, is_active | Learning subject definitions | LearningService |
| study_sessions | id, owner_id, subject_id, duration_seconds, notes, quality_score, studied_at | Study session records | LearningService |
| certifications | id, owner_id, name, issuer, issued_at, expires_at, credential_url, subject_id | Certifications and credentials | LearningService |
| capture_rules | id, owner_id, name, trigger_type (app_focus/url_pattern/schedule/keyword/idle), trigger_config (jsonb), capture_types (jsonb), is_active | Computer activity capture rules | CaptureService |
| activity_captures | id, owner_id, rule_id, active_app, window_title, url, started_at, ended_at, duration_seconds | Application activity captures | CaptureService |
| screen_captures | id, owner_id, capture_id, storage_path, thumbnail_path, ocr_text, captured_at | Screen capture frames with OCR | CaptureService |
| clipboard_events | id, owner_id, content_type, content_hash, content_preview, captured_at | Clipboard event log | CaptureService |
| input_activity | id, owner_id, period_start, period_end, keystrokes, mouse_clicks, mouse_distance_px, active_seconds | Input activity metrics | CaptureService |
| dependents | id, owner_id, entity_uri, dependent_type (child/pet/elderly_parent/other), name, date_of_birth, notes, metadata (jsonb) | Dependent records linked to entity registry | DependentService |
Migration 00053 performs entity_registry backfill — no new tables. Backfills entity_registry rows for all existing persons, organizations, devices, and collections.
Cross-Cutting Patterns
Row Level Security (RLS)
All user-facing tables have RLS enabled with owner_id policies:
ALTER TABLE <table> ENABLE ROW LEVEL SECURITY;
CREATE POLICY "users own their <table>" ON <table>
USING (auth.uid() = owner_id)
WITH CHECK (auth.uid() = owner_id);
Common Columns
Most tables share:
id — UUID primary key (gen_random_uuid())owner_id — UUID FK to auth.users(id)created_at / updated_at — timestamptz DEFAULT now()status — text with CHECK constraint for valid states
Audit Log Immutability
The audit_log table has no UPDATE or DELETE policies — append-only by design.
Extensions
- uuid-ossp — UUID generation
- pgcrypto — Encryption functions
- vector (pgvector) — Vector similarity search for embeddings
- pg_cron — Scheduled database jobs (migration 00018)
- pgsodium — Supabase Vault encryption (credentials)