-- 001_create_organizations.sql
CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    clerk_org_id TEXT UNIQUE NOT NULL
);

-- 002_add_org_name.sql
ALTER TABLE organizations ADD COLUMN name TEXT NOT NULL;

-- 003_create_memberships.sql
CREATE TABLE organization_memberships (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id UUID REFERENCES organizations(id),
user_id UUID REFERENCES users(id)
);

-- 004_add_membership_role.sql
ALTER TABLE organization_memberships
ADD COLUMN role TEXT DEFAULT 'member';

-- 005_add_org_metadata.sql
ALTER TABLE organizations
ADD COLUMN metadata JSONB DEFAULT '{}';

-- 006_add_membership_timestamps.sql
ALTER TABLE organization_memberships
ADD COLUMN joined_at TIMESTAMPTZ DEFAULT NOW();

-- 007_add_org_indexes.sql
CREATE INDEX idx_orgs_clerk_id ON organizations(clerk_org_id);

-- 008_add_membership_indexes.sql
CREATE INDEX idx_memberships_org
ON organization_memberships(organization_id);
CREATE INDEX idx_memberships_user
ON organization_memberships(user_id);

identifier clerk_org_id TEXT -- Organization identifier clerk_session_id TEXT -- Session
tracking public_metadata JSONB -- Public JWT claims private_metadata JSONB -- Private backend
data organization_role TEXT -- Role in org context ```
</div>
<p className="font-bold text-sm mt-3">☑ Field names, types, and constraints preserved exactly</p>
</div>

### Session Management

<div className="brutalist-card p-6 my-6">
<h4 className="font-black uppercase mb-3">SESSION PATTERNS</h4>
<ul className="space-y-2 font-bold text-sm">
<li>► Session tables consolidated safely</li>
<li>► Expiration timestamps preserved</li>
<li>► Device tracking maintained</li>
<li>► Token refresh patterns optimized</li>
</ul>
</div>

### User Provisioning

<div className="brutalist-card p-6 my-6">
<h4 className="font-black uppercase mb-3">PROVISIONING WORKFLOWS</h4>
<p className="font-bold text-sm mb-3">Common Clerk webhook patterns recognized:</p>
<div className="brutalist-code text-xs">
```sql
-- User creation triggers
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
-- Clerk webhook handler
INSERT INTO user_profiles (user_id, clerk_id)
VALUES (NEW.id, NEW.clerk_user_id);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Organization membership sync
CREATE OR REPLACE FUNCTION sync_org_membership()
RETURNS TRIGGER AS $$
BEGIN
-- Clerk org webhook handler
-- CAPYSQUASH preserves these exactly
RETURN NEW;
END;

$$
LANGUAGE plpgsql;

001_create_users_with_clerk.sql
002_create_organizations.sql
003_create_org_memberships.sql
004_add_clerk_webhooks.sql
005_add_user_metadata_indexes.sql

-- Users table bridges both systems
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    clerk_user_id TEXT UNIQUE NOT NULL,     -- Clerk auth
    supabase_user_id UUID,                   -- Optional Supabase linkage
    email TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- CAPYSQUASH detects both patterns!

-- User created webhook
CREATE OR REPLACE FUNCTION clerk_user_created()
RETURNS TRIGGER AS
$$

BEGIN
-- Initialize user profile
INSERT INTO user_profiles (
user_id,
clerk_id,
public_metadata,
private_metadata
) VALUES (
NEW.id,
NEW.clerk_user_id,
NEW.public_metadata,
NEW.private_metadata
);

    RETURN NEW;

END;

$$
LANGUAGE plpgsql;

CREATE TRIGGER on_user_created
    AFTER INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION clerk_user_created();

-- Organization created webhook
CREATE OR REPLACE FUNCTION clerk_org_created()
RETURNS TRIGGER AS
$$

BEGIN
-- Initialize organization settings
INSERT INTO organization_settings (org_id, defaults)
VALUES (NEW.id, '{"notifications": true}'::jsonb);

    RETURN NEW;

END;

$$
LANGUAGE plpgsql;

-- CAPYSQUASH preserves these trigger functions exactly!

ALTER TABLE users
ADD CONSTRAINT users_clerk_id_unique
UNIQUE (clerk_user_id);

-- Find invalid JSON
SELECT id FROM users
WHERE NOT (public_metadata::text)::jsonb IS NOT NULL;

-- Fix with default
UPDATE users
SET public_metadata = '{}'::jsonb
WHERE public_metadata IS NULL;