makima/migrations/20250110000001_create_users_table.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- Create users table as local mirror of Supabase auth.users
-- This allows faster queries and storing additional user metadata

CREATE TABLE IF NOT EXISTS users (
    -- Matches Supabase auth.users(id)
    id UUID PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    display_name VARCHAR(255),
    avatar_url TEXT,
    -- User's personal owner (created on signup)
    default_owner_id UUID REFERENCES owners(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_default_owner_id ON users(default_owner_id);

-- Trigger to update updated_at timestamp (reuse existing function)
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

COMMENT ON TABLE users IS 'Local mirror of Supabase auth.users with additional metadata';
COMMENT ON COLUMN users.id IS 'Matches Supabase auth.users(id) - set by trigger on signup';
COMMENT ON COLUMN users.default_owner_id IS 'User personal owner, created automatically on signup';