5f009673aa
Major overhaul of drop-and-recreate-database.sh, DDL schema updates, seed data cleanup. Add utility scripts for auth table fixes. Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
71 lines
2.6 KiB
SQL
71 lines
2.6 KiB
SQL
-- Fix auth tables for erp_construccion_demo
|
|
|
|
-- 1. Add missing columns to auth.users
|
|
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS roles VARCHAR[] DEFAULT ARRAY['viewer']::VARCHAR[];
|
|
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS default_tenant_id UUID;
|
|
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS last_login_at TIMESTAMPTZ;
|
|
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS username VARCHAR(100);
|
|
|
|
-- 2. Add missing columns to auth.tenants
|
|
ALTER TABLE auth.tenants ADD COLUMN IF NOT EXISTS code VARCHAR(50) UNIQUE;
|
|
ALTER TABLE auth.tenants ADD COLUMN IF NOT EXISTS settings JSONB DEFAULT '{}';
|
|
ALTER TABLE auth.tenants ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
|
|
|
|
-- Update tenants code from slug
|
|
UPDATE auth.tenants SET code = slug WHERE code IS NULL;
|
|
|
|
-- 3. Create refresh_tokens table
|
|
CREATE TABLE IF NOT EXISTS auth.refresh_tokens (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
token TEXT NOT NULL UNIQUE,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
revoked_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
user_agent VARCHAR(500),
|
|
ip_address VARCHAR(45)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user ON auth.refresh_tokens(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_token ON auth.refresh_tokens(token);
|
|
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_expires ON auth.refresh_tokens(expires_at);
|
|
|
|
-- 4. Create test tenant if not exists
|
|
INSERT INTO auth.tenants (id, name, slug, code, is_active, created_at, updated_at)
|
|
VALUES (
|
|
'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
|
|
'Test Company',
|
|
'test-company',
|
|
'test-company',
|
|
true,
|
|
NOW(),
|
|
NOW()
|
|
) ON CONFLICT (id) DO NOTHING;
|
|
|
|
-- 5. Create admin user with password 'Admin123!'
|
|
INSERT INTO auth.users (
|
|
id, tenant_id, email, password_hash, first_name, last_name,
|
|
is_active, email_verified, roles, default_tenant_id, created_at, updated_at
|
|
)
|
|
VALUES (
|
|
'b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a22',
|
|
'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
|
|
'admin@test.com',
|
|
'$2a$10$4M7jT3YmH14xyx3L7uojmuRFyggZcM/qb8A/54B2XXDtYBmVI7/J2',
|
|
'Admin',
|
|
'User',
|
|
true,
|
|
true,
|
|
ARRAY['admin']::VARCHAR[],
|
|
'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
|
|
NOW(),
|
|
NOW()
|
|
) ON CONFLICT (id) DO UPDATE SET
|
|
password_hash = '$2a$10$4M7jT3YmH14xyx3L7uojmuRFyggZcM/qb8A/54B2XXDtYBmVI7/J2',
|
|
roles = ARRAY['admin']::VARCHAR[],
|
|
default_tenant_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';
|
|
|
|
-- 6. Verify
|
|
SELECT 'Tenant created:', id, name FROM auth.tenants WHERE id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';
|
|
SELECT 'User created:', id, email, roles FROM auth.users WHERE email = 'admin@test.com';
|