Database Schema & Migrations
Relevant source files
The following files were used as context for generating this wiki page:
The database layer of HEX-YT-INTEL is built on Supabase, serving as the persistent backbone for YouTube content intelligence and synthesis. The system manages core intelligence data, application settings, and usage metrics while enforcing security through Row Level Security (RLS) and cryptographic integrity for cross-platform persistence.
The architecture follows a "Hybrid Edge Symphony" model where data persistence is secured via HMAC signatures during server-to-server (S2S) operations between Cloudflare Workers and Supabase.
Sources: README.md:20-22, CLAUDE.md:14-16
Core Entities and Schema Architecture
The database schema is organized into several key functional areas: content analysis storage, application configuration, and usage logging.
Identified Database Tables
Based on migrations and system documentation, the following tables form the core schema:
| Table Name | Description | Key Constraints |
|---|---|---|
analyses | Stores synthesized YouTube intelligence data, including the multi-dimensional UCIS reports. | Linked to video_id and user_id. |
app_settings | Key-value store for global application configurations, including prompt versions. | Unique key. |
usage_logs | Tracks API consumption and tool usage for quota enforcement. | auth.uid() = user_id for reads. |
stripe_events | Stores webhook events from Stripe for subscription management. | Restricted to service_role. |
Sources: CLAUDE.md:6-8, supabase/migrations/20260611142500_add_prompt_config.sql:2-4, test-results/web-tests-pr1-fixes-PR-1-F-b81cc-utes-respond-without-errors/error-context.md:85-95
Data Relationships and Flows
The following diagram illustrates the relationship between users, their generated analyses, and the underlying configuration system.
Diagram shows the entity relationships within the Supabase database.
Sources: CLAUDE.md:6-8, test-results/web-tests-pr1-fixes-PR-1-F-b81cc-utes-respond-without-errors/error-context.md:85-115
Row Level Security (RLS) & Security Policies
Security is enforced at the database level using Supabase Row Level Security (RLS). This ensures that users can only access their own data while allowing the system's service role to perform administrative tasks.
RLS Implementation Details
- Analysis Access: Queries to the
analysestable must match bothvideo_idanduser_id. - Usage Logs: Users are restricted to reading their own logs (
auth.uid() = user_id). - System Services: Table
stripe_eventsand certainusage_logsoperations are restricted to theservice_roleonly. - Persistence: Data persistence from the Cloudflare Edge to Supabase is secured via HMAC signatures to prevent tamper-prone S2S writes.
Sources: CLAUDE.md:6-8, 14-16, test-results/web-tests-pr1-fixes-PR-1-F-b81cc-utes-respond-without-errors/error-context.md:85-95, README.md:20-22
Configuration Management
Global application state and prompt logic are managed within the app_settings table. This allows the system to update the "Ultimate Content Intelligence & Implementation System" (UCIS) prompt versioning without requiring code redeployments.
Prompt Configuration Schema
The system stores a prompt_config object in app_settings with the following structure:
latest: The current active version (e.g., "5.1").history: A ledger of changes including timestamps and authors.versions: A dictionary mapping version numbers to the full Markdown prompt text.
-- Example of inserting prompt configuration
insert into public.app_settings (key, value)
values (
'prompt_config',
'{"latest":"5.1", "history": [...], "versions": {"5.1": "# PROMPT..."}}'
);
Sources: supabase/migrations/20260611142500_add_prompt_config.sql:2-10
Maintenance and Cleanup Operations
The system utilizes pg_cron for automated database maintenance, specifically focusing on data retention policies for different user tiers.
Automated Cleanup Jobs
A scheduled job named delete-old-free-analyses runs daily at 2 AM. It targets analyses created by users on the "free" tier that are older than 30 days. This ensures the database remains performant and adheres to tier-based storage limits.
Sources: test-results/web-tests-pr1-fixes-PR-1-F-b81cc-utes-respond-without-errors/error-context.md:103-115
Diagram shows the automated cleanup flow for free tier data.
Sources: test-results/web-tests-pr1-fixes-PR-1-F-b81cc-utes-respond-without-errors/error-context.md:108-113
Summary
The database schema and migration strategy of HEX-YT-INTEL provide a secure, scalable foundation for AI content intelligence. By combining standard SQL tables with Supabase's RLS, automated maintenance through pg_cron, and a flexible configuration system for prompts, the project maintains high data integrity while supporting complex "Hybrid Edge" workflows.