Database Schema Design

Relevant source files

The following files were used as context for generating this wiki page:

• supabase/migrations/20260503011925_event_phase1.sql
• supabase/migrations/20260515043737_places_cache_schema.sql
• src/lib/supabase/database.types.ts
• supabase/migrations/20260512120000_sponsor_schema_edge_acl.sql
• supabase/migrations/20260524022749_mdeapp_canonical_schema_cleanup.sql
• src/mastra/tools/search-restaurants.ts
• src/mastra/tools/search-attractions.ts

The database schema for the mdeapp project is built on Supabase (PostgreSQL) and designed to support an AI-first discovery and ticketing platform. It encompasses several specialized modules including event management, ticketing, venue tracking, external place caching, and AI agent runtime state. The design prioritizes strict data integrity through foreign keys and check constraints, while utilizing Row Level Security (RLS) and Security Definer functions to manage access across anonymous and authenticated user roles.

Sources: README.md:14-14, supabase/migrations/20260503011925_event_phase1.sql:1-15

Event and Ticketing System

The core of the application is a comprehensive event management and ticketing system introduced in Phase 1. This system handles the lifecycle of an event from draft to closure, including venue association, multi-tier ticket sales, and secure check-ins.

Event Lifecycle and Capacity Management

Events transition through various statuses: draft, published, live, closed, and archived. To prevent overselling, the schema employs a qty_pending mechanism in the event_tickets table, which reserves seats during the checkout process before payment is finalized.

The diagram above illustrates the relationships between core ticketing entities. Sources: supabase/migrations/20260503011925_event_phase1.sql:128-260

Key Ticketing Tables

Table	Primary Responsibility	Critical Constraints
public.events	Master record for event details and status.	Slug uniqueness, valid status enum.
public.event_tickets	Defines ticket types, pricing (in cents), and inventory.	qty_sold + qty_pending <= qty_total.
public.event_orders	Tracks transaction status and buyer info.	Unique access_token for anonymous lookups.
public.event_attendees	Per-person QR holder record.	Unique qr_token.
public.event_check_ins	Immutable audit log of scan attempts.	Valid result enum (e.g., consumed, already_used).

Sources: supabase/migrations/20260503011925_event_phase1.sql:103-340

External Places and Enrichment

The schema includes specialized caching and enrichment fields to integrate with the Google Places API while adhering to ToS and performance requirements.

Places API Cache

Two tables manage external data persistence to reduce API latency and costs:

• places_search_cache: Caches raw search results based on a SHA-256 hash of the query and location. It has a default TTL of 48 hours.
• place_details_cache: Caches enriched details for specific Place IDs with a 14-day TTL.

Sources: supabase/migrations/20260515043737_places_cache_schema.sql:20-56

Data Enrichment (MASTRA-048)

Inventory tables such as restaurants, tourist_destinations, and events are enriched with AI-generated summaries and canonical Google Maps links.

ALTER TABLE public.restaurants
  ADD COLUMN IF NOT EXISTS maps_url   text,
  ADD COLUMN IF NOT EXISTS ai_summary text;

COMMENT ON COLUMN public.restaurants.ai_summary IS
  'Gemini-generated 2-sentence venue description. Cached at seeding time.';

Sources: supabase/migrations/20260515043737_places_cache_schema.sql:101-118

AI Agent Runtime (Mastra)

The system tracks AI agent activities through a suite of mastra_* tables. This infrastructure supports versioning, observability, and long-running tasks.

• mastra_agents & mastra_agent_versions: Manage agent configurations and instructions.
• mastra_ai_spans: Traceability for AI operations, tracking inputs, outputs, and metadata.
• mastra_messages: Chat history decoupled from legacy message structures.
• mastra_background_tasks: Manages asynchronous tool execution with retry logic.

Sources: src/lib/supabase/database.types.ts:494-878, supabase/migrations/20260524022749_mdeapp_canonical_schema_cleanup.sql:31-36

Security and Data Access

The project strictly implements Row Level Security (RLS) and handles complex logic through Security Definer RPCs to maintain a "strict by default" security posture.

Security Definer RPCs

These functions allow granular control over sensitive operations, often granting service_role or specific user access while bypassing standard RLS for atomic transactions.

Function	Access Role	Purpose
ticket_checkout_create_pending	service_role	Atomic capacity check and pending order creation.
ticket_payment_finalize	service_role	Converts pending reservations to sold status upon payment success.
ticket_validate_consume	service_role	Race-safe single-use ticket consumption during door scanning.
get_anonymous_order	anon, authenticated	Allows buyers to view tickets using an opaque access_token.

Sources: supabase/migrations/20260503011925_event_phase1.sql:410-660

Transactional Integrity

The system uses PostgreSQL advisory locks (pg_advisory_xact_lock) at the event granularity during the checkout process to prevent race conditions during high-volume sales.
Sources: supabase/migrations/20260503011925_event_phase1.sql:475-480

Canonical Schema Cleanup

As part of the migration to the new mdeapp codebase, legacy tables and columns were removed to focus on the Mastra-based architecture and specific Medellín discovery verticals. Dropped modules include legacy chat, duplicate rentals, and unused trip planning tables.
Sources: supabase/migrations/20260524022749_mdeapp_canonical_schema_cleanup.sql:5-65

Conclusion

The mdeapp database schema is a specialized Postgres implementation optimized for AI-driven discovery and high-integrity ticketing. By combining rigorous state management for sales, efficient caching for external location data, and a robust tracing system for AI agents, the schema provides a stable foundation for the platform's discovery and payment features.