# Database Schema Design for Multi-Tenant Geek-Life

## Overview
This document outlines the PostgreSQL database schema for the multi-tenant version of Geek-Life.

## Tables

### tenants
- id (BIGSERIAL PRIMARY KEY)
- name (VARCHAR(255) NOT NULL)
- created_at (TIMESTAMP NOT NULL DEFAULT NOW())
- updated_at (TIMESTAMP NOT NULL DEFAULT NOW())

### users
- id (BIGSERIAL PRIMARY KEY)
- tenant_id (BIGINT NOT NULL REFERENCES tenants(id))
- username (VARCHAR(100) NOT NULL)
- email (VARCHAR(255) NOT NULL)
- password_hash (VARCHAR(255) NOT NULL)
- created_at (TIMESTAMP NOT NULL DEFAULT NOW())
- updated_at (TIMESTAMP NOT NULL DEFAULT NOW())
- UNIQUE(tenant_id, username)
- UNIQUE(tenant_id, email)

### projects
- id (BIGSERIAL PRIMARY KEY)
- tenant_id (BIGINT NOT NULL REFERENCES tenants(id))
- user_id (BIGINT NOT NULL REFERENCES users(id))
- title (VARCHAR(255) NOT NULL)
- uuid (UUID NOT NULL DEFAULT gen_random_uuid())
- created_at (TIMESTAMP NOT NULL DEFAULT NOW())
- updated_at (TIMESTAMP NOT NULL DEFAULT NOW())
- INDEX(tenant_id, user_id)
- UNIQUE(uuid)

### tasks
- id (BIGSERIAL PRIMARY KEY)
- tenant_id (BIGINT NOT NULL REFERENCES tenants(id))
- user_id (BIGINT NOT NULL REFERENCES users(id))
- project_id (BIGINT NOT NULL REFERENCES projects(id))
- uuid (UUID NOT NULL DEFAULT gen_random_uuid())
- title (VARCHAR(500) NOT NULL)
- details (TEXT)
- completed (BOOLEAN NOT NULL DEFAULT FALSE)
- due_date (TIMESTAMP)
- created_at (TIMESTAMP NOT NULL DEFAULT NOW())
- updated_at (TIMESTAMP NOT NULL DEFAULT NOW())
- INDEX(tenant_id, user_id)
- INDEX(project_id)
- INDEX(completed)
- INDEX(due_date)
- UNIQUE(uuid)

### user_sessions
- id (BIGSERIAL PRIMARY KEY)
- user_id (BIGINT NOT NULL REFERENCES users(id))
- token (VARCHAR(255) NOT NULL UNIQUE)
- expires_at (TIMESTAMP NOT NULL)
- created_at (TIMESTAMP NOT NULL DEFAULT NOW())
- INDEX(token)
- INDEX(expires_at)

## Row Level Security (RLS)
All tables will have RLS enabled to ensure users can only access their own data within their tenant.