Database Schema & RLS

Relevant source files

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

• README.md
• CHANGELOG.md
• backend/lib/supabase.ts
• backend/routes/pdf-page-summaries.ts
• Oddity1Dashboard/src/hooks/useDocuments.js
• extension/src/background/index.ts

The Oddity1 database architecture utilizes Supabase to manage user profiles, document storage, and annotation caching. It implements Row Level Security (RLS) to ensure that users can only access their own data, while a service-role client handles administrative tasks and global configuration management.

The system partitions data between user-owned tables (such as profiles and documents) and system-managed tables (such as site_adapters and annotation_cache). This structure supports multi-tier subscriptions, rate limiting, and cross-platform synchronization between the Chrome extension and the web dashboard.

Sources: README.md, CHANGELOG.md, backend/lib/supabase.ts:1-20

Core Data Structures

The database consists of several primary tables that store user-generated content and system configurations.

Database Tables Summary

Table	Purpose	Access Control (RLS)
profiles	User metadata, preferences, and subscription tier	Owner (Own row only)
documents	User-created documents, content, and local sync state	Owner (Own rows only)
annotation_cache	AI-generated annotations, keyed by content hash and intensity	Service-role only
user_annotations	Manual annotations created by users per page	Owner (Own rows only)
annotation_feedback	User feedback on AI annotations (thumbs, replies)	Owner (Own rows only)
site_adapters	Site extraction configurations and seeded patterns	Service-role only
pdf_page_summaries	AI summaries of specific PDF pages	Owner (Own rows only)

Sources: README.md, CHANGELOG.md, backend/routes/pdf-page-summaries.ts:74-85, Oddity1Dashboard/src/hooks/useDocuments.js:46-55

Entity Relationships

The following diagram illustrates the relationships between the core database entities.

The diagram shows the central role of the profiles table, which serves as the anchor for all user-owned entities in the system.

Sources: README.md, extension/src/background/index.ts:543-550, Oddity1Dashboard/src/hooks/useDocuments.js:80-90

Row Level Security (RLS) Implementation

RLS is enforced at the database level to maintain strict data isolation between users.

• User Ownership: Tables like profiles, documents, and user_annotations restrict operations to rows where the user_id matches the authenticated user's ID.
• System Integrity: Tables containing system configurations, such as site_adapters and the annotation_cache, are restricted to service-role access only to prevent user tampering.
• Function Execution: Stored procedures, such as check_and_record_usage, are executed via the serviceClient to manage rate limits across all users regardless of their individual RLS permissions.

Sources: README.md, backend/lib/supabase.ts:5-18, backend/routes/pdf-page-summaries.ts:127-130

Usage and Rate Limiting

The system tracks usage through a dedicated Postgres function.

-- Conceptual implementation of the usage tracking
CREATE OR REPLACE FUNCTION check_and_record_usage(
  p_user_id UUID,
  p_url TEXT,
  p_limit INTEGER
) RETURNS JSONB AS $$
  -- Logic to check if user has exceeded their monthly limit
  -- Returns allowed status and current count
$$ LANGUAGE plpgsql;

Sources: backend/routes/pdf-page-summaries.ts:127-135

Data Synchronization Logic

The application implements a "Supabase as source of truth" model with local storage fallbacks to support offline usage and reduce latency.

Document Sync Flow

The useDocuments hook manages the synchronization between the browser's localStorage and the Supabase documents table.

The flow shows how the system prioritizes local "dirty" states to prevent data loss during network instability.

Sources: Oddity1Dashboard/src/hooks/useDocuments.js:33-78

API and Client Interfaces

The backend uses two distinct Supabase clients to interact with the database.

1. User Client (createUserClient): Created using the user's Supabase access token. It respects RLS and is used for operations like fetching documents or saving manual annotations.
2. Service Client (serviceClient): Created using the SUPABASE_SERVICE_ROLE_KEY. It bypasses RLS and is used for administrative tasks, usage tracking, and caching.

Sources: backend/lib/supabase.ts:8-18, backend/routes/pdf-page-summaries.ts:53-56

Caching Strategy

The annotation_cache table uses a specific keying strategy to optimize LLM usage:

• Key: (content_hash, intensity).
• TTL: 30 days via the expires_at column.
• Behavior: If the same text is found on different URLs, the extension retrieves the cached AI annotations from the database.

Sources: README.md, CHANGELOG.md, extension/src/background/index.ts:318-325

Conclusion

The Oddity1 database schema is designed for secure, scalable annotation management. By combining strict RLS policies with a robust synchronization hook and a dual-client backend architecture, the system ensures data privacy while maintaining high performance through aggressive caching and usage tracking.