Xiaoxia AI
a2bbc7345b
- Implement PostgresUserRepository with full CRUD operations - Support all query methods (by_id/email/username/token) - Use psycopg2 with RealDictCursor for clean mapping - Create initial schema migration (users/workspaces/members/invitations) - Add database indexes for performance - Setup foreign key constraints for data integrity - Include migration guide and rollback instructions - Add psycopg2 dependency Phase 4 Task 28/68 completed
88 lines
3.1 KiB
SQL
88 lines
3.1 KiB
SQL
"""
|
|
数据库迁移脚本 - 初始化表结构
|
|
"""
|
|
|
|
-- 用户表
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
display_name VARCHAR(255) NOT NULL,
|
|
username VARCHAR(255) UNIQUE,
|
|
password_hash VARCHAR(255),
|
|
email_verified BOOLEAN DEFAULT FALSE,
|
|
email_verification_token VARCHAR(255),
|
|
password_reset_token VARCHAR(255),
|
|
password_reset_expires_at TIMESTAMP,
|
|
last_login_at TIMESTAMP,
|
|
last_login_ip VARCHAR(50),
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_users_email ON users(email);
|
|
CREATE INDEX idx_users_username ON users(username);
|
|
CREATE INDEX idx_users_email_verification_token ON users(email_verification_token);
|
|
CREATE INDEX idx_users_password_reset_token ON users(password_reset_token);
|
|
|
|
|
|
-- 工作空间表
|
|
CREATE TABLE IF NOT EXISTS workspaces (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
owner_user_id VARCHAR(255) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
subscription_plan VARCHAR(50) DEFAULT 'free',
|
|
subscription_status VARCHAR(50) DEFAULT 'active',
|
|
subscription_expires_at TIMESTAMP,
|
|
max_projects INTEGER DEFAULT 3,
|
|
max_storage_gb INTEGER DEFAULT 10,
|
|
used_storage_gb DECIMAL(10, 2) DEFAULT 0,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_workspaces_owner ON workspaces(owner_user_id);
|
|
|
|
|
|
-- 工作空间成员表
|
|
CREATE TABLE IF NOT EXISTS workspace_members (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
workspace_id VARCHAR(255) NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
user_id VARCHAR(255) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role VARCHAR(50) NOT NULL,
|
|
invited_by VARCHAR(255) REFERENCES users(id),
|
|
joined_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
UNIQUE(workspace_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX idx_workspace_members_workspace ON workspace_members(workspace_id);
|
|
CREATE INDEX idx_workspace_members_user ON workspace_members(user_id);
|
|
|
|
|
|
-- 工作空间邀请表
|
|
CREATE TABLE IF NOT EXISTS workspace_invitations (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
workspace_id VARCHAR(255) NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
inviter_user_id VARCHAR(255) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
invitee_email VARCHAR(255) NOT NULL,
|
|
role VARCHAR(50) NOT NULL,
|
|
invitation_token VARCHAR(255) UNIQUE NOT NULL,
|
|
status VARCHAR(50) DEFAULT 'pending',
|
|
expires_at TIMESTAMP,
|
|
accepted_at TIMESTAMP,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_invitations_workspace ON workspace_invitations(workspace_id);
|
|
CREATE INDEX idx_invitations_token ON workspace_invitations(invitation_token);
|
|
CREATE INDEX idx_invitations_workspace_email ON workspace_invitations(workspace_id, invitee_email, status);
|
|
|
|
|
|
-- 项目表（占位，后续 Phase 完善）
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id VARCHAR(255) PRIMARY KEY,
|
|
workspace_id VARCHAR(255) NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_projects_workspace ON projects(workspace_id);
|