Supabase Schema by Claude And Why Another AI Called It ‘Nearly Perfect’ (9.8/10)

I designed a production-ready database schema for a visual asset management platform (wowscreenshots.com) through conversations with Claude. Another AI DB expert gave it a 9.8/10 rating.

When I started building my screenshot management app, I thought database design was just about creating tables. Then I showed my schema to another AI expert and discovered I was about to build something that would completely fall apart under real usage.

My Initial Requirements to Claude

I started with this prompt:

You are a database expert that puts security first and then scalability.

I'm building a visual asset management platform using Supabase.

Business Model
Free Plan
– Up to 100 screenshots (can delete to free up space)
– 5GB storage
– 100 AI credits/month

Pro Plan – $4.99/month
– 20GB storage
– Unlimited screenshots/docs
– 500 AI credits

Business Plan – $19/month
– 100GB storage
– Unlimited screenshots/docs
– 2000 AI credits

Note: No limit on users on any of the plans.

Future Features (Roadmap)
Metadata for each screenshot/image:
– Time with timezone
– Location
– Device info
– OCR (if text is present)
– Detected colors/objects for searchable indexing

Chrome Extension:
– Full-page auto-scrolling screenshots
– Upload + metadata capture

Advanced Redaction:
– Redacts sensitive data
– Replaces redacted text with random strings to prevent decoding

User Management:
– Unlimited members
– Invite others via email

Workspaces:
– Unlimited workspaces
– Each workspace has its own pricing plan

AI Organization:
– Auto-organizes screenshots into folders
– Auto-tags each screenshot

Customization Tools:
– Adjust colors, dimensions
– Presets for social media platforms

Hidden Text Support:
– Ability to store and manage invisible (selected but not visible) text on images

The Section-by-Section Review Process

After Claude showed me an initial schema, I wanted to review it carefully:

"let's go over each part of the schema so I can review here and give more feedback, I noticed the pricing plan name and details so I need to make sure every section has to be reviewed by me"

Section 1: Core Foundation Review

Claude asked:

"Subscription Plans: I assumed 'free', 'pro', 'team', 'enterprise' - are these your actual plan names?"

Me: "let's do free, pro, business bcoz you can add as many people as you want in any plan"

"Asset Types: Beyond 'screenshot', do you plan to support 'document', 'video' uploads?"

Me: "screenshot and let's add document and video as well"

I also caught that Claude had removed image: "don't remove image bcoz you can also upload image and all formats of images. if we don't support a format then we'll be converting those images on the fly"

"Edit Actions: Are 'resize', 'overlay_text', 'redact', 'annotate', 'crop' the main editing features?"

Me: "yes and adding background colors and also corners like round, rectangle and uploaded screenshots for some more edit actions"

Section 2: Workspaces & Multi-Tenancy Review

Claude asked:

"Workspace URLs: Should workspaces have custom slugs like acme-team.yourapp.com?"

Me: "no, let's do /acme-team"

"Custom domains: Do you want custom domains for enterprise?"

Me: "no custom domain"

"Viewer role: Can they upload assets or just view/comment?"

Me: "only view in the invited workspace"

"Editor role: Can they invite new users or only admin/owner?"

Me: "no invites"

"Invite expiry: 7 days good or different timeframe?"

Me: "30 days"

"Bulk invites: Support multiple emails at once?"

Me: "yes"

"Personal messages: Allow custom message with invites?"

Me: "yes but if they don't then default message"

"Free plan 100 assets: Hard block or soft warning to upgrade?"

Me: "Hard and they should either upgrade or delete an asset"

"AI credits exhausted: Block features or allow overage?"

Me: "block, ask them to upgrade"

"Storage warnings: Should users get warnings at 80% of storage limit?"

Me: "yes and then again at 90%"

"Analytics: Should workspace owners see individual user activity or just totals?"

Me: "individual"

"Should detailed analytics be a paid feature?"

Me: "no, it can be free"

Section 3: Assets & File Management Review

Claude asked:

"Folder colors: Visual organization with color coding?"

Me: "yes"

"AI auto-organize: Should AI automatically sort uploads into smart folders?"

Me: "yes, it should"

"Folder depth: Any limits on nested folder levels?"

Me: "5 max"

"AI tags vs Manual tags: Should they look different in the UI?"

Me: "yes, a little bit. make the AI standout"

"Tag suggestions: Based on workspace patterns or global data?"

Me: "workspace only"

"Tag hierarchies: Nested tags like Design > UI > Buttons?"

Me: "yes but they can just be tags in general with each file"

"Search engine: PostgreSQL full-text search or external (Elasticsearch)?"

Me: "no external search but might find opensource like algolia"

"Search scope: Just metadata or also file contents (PDF text)?"

Me: "both"

"Saved searches: Should users be able to bookmark searches?"

Me: "yes"

"Auto-detection: Find similar/duplicate uploads automatically?"

Me: "yes"

"What to do: Merge duplicates, delete, or just notify user?"

Me: "notify the user and ask them for the decide with options"

"Similarity threshold: How similar before flagging as duplicate?"

Me: "yes"

"Thumbnail sizes: What sizes for mobile/desktop views?"

Me: "we can use cloudflare Image API for optimization"

"Storage tiers: Auto-move old files to cheaper storage?"

Me: "no, they can all stay at cheap"

"Access tracking: Track when assets were last viewed?"

Me: "yes and last edited and even last shared"

When Claude asked about AI analysis, I said: "yes and also if there's a chair, glass or anything else... yes but colors on objects as well... yes, even emoji's"

Section 4: Editing & Customization Review

Claude asked:

"Platform Presets: What specific platforms should have built-in presets?"

Me: "yes, all platforms and their own formats so you can easily resize images instantly"

"Custom presets: Should users be able to share presets between workspaces?"

Me: "yes"

"Undo/redo within editing sessions?"

Me: "yes for undo/redo and version restore as well"

"How many versions to keep per asset?"

Me: "unlimited versions but free plan should only have 10 version limit"

"Preset templates for common editing workflows?"

Me: "yes"

"Should fake text match original formatting exactly?"

Me: "no, random so it can never be detected"

"Different templates for emails vs names vs addresses?"

Me: "yes"

"Should redacted data be encrypted separately?"

Me: "yes"

"Who can access the original redacted content?"

Me: "no one, once it's done. It can't be restored but you can see the original image and you can reset and use that image to redact again"

Me: "you can undo anything unless it's been final"

"Real-time editing: Like Figma, multiple users editing simultaneously?"

Me: "yes"

"Conflict resolution: How to handle when users edit the same area?"

Me: "you block the area and only one can work at a time"

"Live cursors: Show other users' selections and cursors?"

Me: "yes"

"Comments during editing: Add feedback while editing?"

Me: "yes"

"Rendering strategy: Cache edited versions or generate on-demand?"

Me: "yes" (for caching)

"Cloudflare integration: Use Cloudflare Images for edited versions too?"

Me: "yes"

"Cache duration: How long to keep rendered versions?"

Me: "for 1 year"

"Pre-generation: Create common preset variations automatically?"

Me: "yes"

The Game-Changing Question

At one point, I asked Claude: "does it cover future apps for mobile, desktop and chrome?"

This question made Claude add comprehensive multi-platform support tables that I hadn't even considered.

The Platform Constraint

Later, I told Claude: "I only gonna be using supabase"

This made Claude optimize everything specifically for Supabase, removing features like pg_cron and external Redis.

Here's What Claude Designed

Based on our conversation, Claude created a comprehensive schema with:

Core Tables

• User profiles with preferences

• Workspaces with usage tracking

• Assets with AI metadata

• Folders with 5-level nesting

• Tags with hierarchical structure

• Platform presets for all social media

• Version control with limits

• Advanced redaction system

• Real-time collaboration sessions

• Multi-platform device tracking

Key Features in the Schema

-- Pricing plans with my exact requirements
INSERT INTO pricing_plans (name, tier, monthly_price_cents, yearly_price_cents, features) VALUES
('Free', 'free', 0, 0, '{
    "max_users": -1,
    "max_storage_gb": 5,
    "ai_credits_monthly": 100,
    "max_workspaces": -1,
    "max_assets_total": 100,
    "max_versions_per_asset": 10
}');

-- Asset AI metadata structure
ai_metadata JSONB DEFAULT '{}' NOT NULL,
/* Stores:
{
    "ocr_text": "extracted text from image",
    "objects_detected": ["chair", "glass", "laptop"],
    "object_colors": {"chair": ["brown", "black"], "glass": ["clear"]},
    "dominant_colors": ["#FF5733", "#C70039"],
    "emojis_detected": ["😀", "👍"],
    "sentiment": "professional",
    "accessibility_score": 0.92
}
*/

-- Folders with my 5-level constraint
CONSTRAINT max_folder_depth CHECK (folder_depth <= 5)

-- Advanced redaction that can't be decoded
CREATE TABLE hidden_data (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    asset_version_id UUID NOT NULL REFERENCES asset_versions(id) ON DELETE CASCADE,
    data_type TEXT NOT NULL,
    original_content TEXT NOT NULL, -- Encrypted at app level
    replacement_method redaction_type,
    replacement_content TEXT, -- Random fake text
    access_level TEXT DEFAULT 'destroyed',
    original_destroyed BOOLEAN DEFAULT false
);

The AI Expert Reviews

I shared the schema for feedback with Google Gemini and got expert review.

First AI Expert Review (Score: 9.0/10)

The expert said: "Well-architected, production-ready schema with excellent security implementation. The architecture is enterprise-grade ambitious."

But they found critical problems:

Problem 1: API Key Validation Bottleneck

My original approach would lock the database on every API request. The expert showed that at 100 requests/second, it would serialize down to ~10 requests/second.

The fix was to separate validation (read-only) from tracking (append-only):

-- Read-only validation
CREATE FUNCTION validate_api_key(key TEXT) AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM api_keys 
        WHERE key_hash = hash_key($1) 
        AND expires_at > NOW()
        AND is_active = true
    );
END;
$$;

-- Separate tracking table
CREATE TABLE api_key_usage_events (
    key_id UUID NOT NULL,
    timestamp TIMESTAMPTZ DEFAULT NOW()
);

Problem 2: High-Frequency Write Issues

The expert identified that live cursor updates (30+ times per second per user) would kill the database. The solution: move real-time data to Supabase Realtime, only persist session boundaries.

Second AI Expert Review (Score: 9.8/10)

After mentioning I was using Supabase, the second AI expert said:

"This schema is a blueprint for success on Supabase. The thoughtful optimization for Supabase's specific capabilities, combined with sophisticated handling of real-time collaboration and multi-platform sync, makes this a world-class database design. Deploy with confidence."

They noted the trade-off with not using partitioning but approved my batched cleanup strategy via Edge Functions:

// Edge Function for batched cleanup
export default async function cleanupOldRecords(req) {
    const supabase = createClient(
        Deno.env.get('SUPABASE_URL'),
        Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')
    );
    
    let totalDeleted = 0;
    let batchDeleted = 0;
    
    do {
        const { data } = await supabase
            .from('asset_access_events')
            .delete()
            .lt('created_at', thirtyDaysAgo.toISOString())
            .limit(1000) // Small batch to avoid locks
            .select();
            
        batchDeleted = data?.length || 0;
        totalDeleted += batchDeleted;
        
        if (batchDeleted > 0) {
            await new Promise(resolve => setTimeout(resolve, 100));
        }
    } while (batchDeleted > 0);
}

What I Learned

1. Start with business requirements - Claude's first question about pricing shaped the entire architecture

2. Review systematically - Going section by section caught many issues

3. Be specific - Every "yes" or "no" I gave shaped the final design

4. Platform matters - Saying "I only gonna be using supabase" changed everything

5. Expert review is crucial - They caught performance killers I never would have seen

The Process That Works

1. Give Claude your complete vision and constraints upfront

2. Review the schema section by section

3. Ask "what if" questions (like my mobile/desktop question)

4. Get another AI agent expert feedback from Google Gemini or ChatGPT

5. Iterate based on real-world concerns

The complete schema ended up being 2,847 lines of production-ready SQL that handles:

• Multi-platform apps (iOS, Android, Desktop, Chrome Extension, Web)

• Real-time collaboration without database meltdown

• AI-powered search across millions of assets

• Advanced redaction with undecodeable fake text

• Unlimited users with workspace isolation

• Performance patterns that actually scale

You don't need 10 years of database experience. You need clear requirements, AI assistance, expert feedback, and the willingness to iterate.

---

The Schema Architecture

The final schema consists of 50+ tables organized into these core components:

📦 Core Tables

• user_profiles: Extends Supabase Auth with preferences

• workspaces: Multi-tenant isolation with usage tracking

• assets: Files with AI metadata, platform source tracking

• folders: 5-level hierarchical organization with colors

🔍 AI & Search

• ai_metadata: OCR text, objects detected, colors, emojis

• tags: Hierarchical tags with AI vs manual distinction

• asset_relationships: Duplicate detection with similarity scores

• saved_searches: Bookmarked searches with usage tracking

✏️ Editing & Customization

• asset_versions: Non-destructive editing with version limits

• presets: Platform templates for all social media formats

• hidden_data: Advanced redaction with fake text generation

• rendered_cache: Cloudflare-optimized cache storage

🤝 Collaboration

• collaboration_sessions: User presence tracking

• editing_sessions: Actual editing state management

• comments: Threaded discussions with positioning

• asset_shares: Secure sharing with access levels

📱 Multi-Platform Support

• device_tokens: Push notification management

• platform_preferences: Per-platform settings (mobile/desktop/extension)

• sync_events: Cross-platform synchronization

• activity_log: Comprehensive audit trail

→ Download the Complete Production Schema (2,000+ lines)