Homepro DB Schema

This document is recording table creation I did in the order. It is not current state of DB, as some foriegn keys will be added later. I will try to keep it up tp date. I will also look for finding a solution to store this more efficiently, till then this is a place holder.

User Table

      Column      |            Type             | Collation | Nullable |      Default
------------------+-----------------------------+-----------+----------+-------------------
 id               | uuid                        |           | not null | gen_random_uuid()
 firebase_uid     | character varying(128)      |           | not null |
 name             | character varying(500)      |           |          |
 email            | character varying(150)      |           |          |
 phone_number     | character varying(15)       |           |          |
 profile          | jsonb                       |           |          | '{}'::jsonb
 created_by       | character varying(100)      |           |          |
 created_at       | timestamp without time zone |           |          | CURRENT_TIMESTAMP
 last_modified_by | character varying(100)      |           |          |
 last_modified_at | timestamp without time zone |           |          | CURRENT_TIMESTAMP
 deleted_at       | timestamp without time zone |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "idx_active_users" btree (deleted_at) WHERE deleted_at IS NULL
    "idx_email" btree (email)
    "idx_firebase_uid" btree (firebase_uid)
    "idx_phone_number" btree (phone_number)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
    "users_firebase_uid_key" UNIQUE CONSTRAINT, btree (firebase_uid)

Insert User

INSERT INTO users (
    id,
    firebase_uid,
    name,
    email,
    created_at,
    created_by,
    last_modified_at,
    last_modified_by
) VALUES (
    gen_random_uuid(), -- Automatically generate a UUID
    '3Ncm6SL4riYMPA44TAHculbjjhJ2', -- Replace with the Firebase UID
    'Vikrant Singh', -- Replace with the user's name
    'vikrant.thakur@gmail.com', -- Replace with the user's email
    NOW(), -- Current timestamp for created_at
    'vikrant', -- Replace with the creator's name or identifier
    NOW(), -- Current timestamp for last_modified_at
    'vikrant' -- Replace with the modifier's name or identifier
);


INSERT INTO users (
    firebase_uid,
    name,
    email,
    created_by,
    last_modified_by
) VALUES (
    '45Zi5WDQewfygJ1cEwHYuKx8y8Y2', -- Replace with the Firebase UID
    'Vikrant Admin Singh', -- Replace with the user's name
    'vikrant.subscribe@gmail.com', -- Replace with the user's email
    'vikrant', -- Replace with the creator's name or identifier
    'vikrant' -- Replace with the modifier's name or identifier
);

Create Role Table

CREATE TABLE roles ( id SERIAL PRIMARY KEY, -- Auto-incrementing ID for each role name VARCHAR(255) NOT NULL UNIQUE, -- Role name, must be unique (e.g., Admin, User) description TEXT, -- Optional description of the role created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when the role was created updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when the role was last updated deleted_at TIMESTAMP -- Soft deletion timestamp );

-- Indexes CREATE INDEX idx_roles_name ON roles(name); -- To quickly search roles by name CREATE INDEX idx_roles_deleted_at ON roles(deleted_at); -- For querying non-deleted roles

create user role

CREATE TABLE user_roles ( id SERIAL PRIMARY KEY, -- Auto-incrementing ID for the user-role relationship user_id UUID NOT NULL, -- Foreign key to the users table role_id INT NOT NULL, -- Foreign key to the roles table created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Timestamp when the relationship was created deleted_at TIMESTAMP, -- Soft deletion timestamp UNIQUE (user_id, role_id), -- Ensures a user cannot have the same role multiple times CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE ON UPDATE CASCADE );

-- Indexes CREATE INDEX idx_user_roles_user_id ON user_roles(user_id); -- To quickly find all roles for a user CREATE INDEX idx_user_roles_role_id ON user_roles(role_id); -- To quickly find all users with a specific role CREATE INDEX idx_user_roles_deleted_at ON user_roles(deleted_at); -- For querying non-deleted user-role relationships