fc6f4a12e6
Backend (replaces PHP api.php + auth.php): - FastAPI app with routers: jobs, auth, billing - Supabase JWT authentication in deps.py - Celery + Redis job queue (process_pdf_task) - MinIO S3-compatible storage service - PDF checker wrapper (delegates to enterprise_pdf_checker.py) - Stripe billing: checkout, portal, webhook handler Multi-tenancy (Phase 3): - Alembic migration 001: workspaces, workspace_members, jobs, usage_events - Row-Level Security on all tenant tables via app.workspace_id session var - Monthly quota enforcement per workspace (402 on exceeded) - Plan tiers: free(5) / pro(100) / business(unlimited) Config: - pydantic-settings based config.py (no hardcoded values) - docker-compose.yml rewritten: postgres, redis, minio, api, celery Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
96 lines
5.1 KiB
Python
96 lines
5.1 KiB
Python
"""Initial SaaS schema — workspaces, jobs, usage_events with RLS
|
|
|
|
Revision ID: 001
|
|
Revises:
|
|
Create Date: 2026-05-19
|
|
"""
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
from sqlalchemy.dialects.postgresql import UUID, JSONB
|
|
|
|
revision = "001"
|
|
down_revision = None
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.execute('CREATE EXTENSION IF NOT EXISTS "pgcrypto"')
|
|
|
|
# ── Workspaces ───────────────────────────────────────────────────────────
|
|
op.create_table(
|
|
"workspaces",
|
|
sa.Column("id", UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()")),
|
|
sa.Column("name", sa.String(255), nullable=False),
|
|
sa.Column("slug", sa.String(100), unique=True, nullable=False),
|
|
sa.Column("plan_tier", sa.String(50), nullable=False, server_default="free"),
|
|
sa.Column("monthly_quota", sa.Integer, nullable=False, server_default="5"),
|
|
sa.Column("stripe_customer_id", sa.String(255), nullable=True),
|
|
sa.Column("stripe_subscription_id", sa.String(255), nullable=True),
|
|
sa.Column("created_at", sa.TIMESTAMP(timezone=True), server_default=sa.text("NOW()")),
|
|
)
|
|
|
|
# ── Workspace Members ────────────────────────────────────────────────────
|
|
op.create_table(
|
|
"workspace_members",
|
|
sa.Column("workspace_id", UUID(as_uuid=True), sa.ForeignKey("workspaces.id", ondelete="CASCADE"), primary_key=True),
|
|
sa.Column("user_id", sa.String(255), primary_key=True),
|
|
sa.Column("role", sa.String(50), nullable=False, server_default="member"),
|
|
sa.Column("created_at", sa.TIMESTAMP(timezone=True), server_default=sa.text("NOW()")),
|
|
)
|
|
|
|
# ── Jobs ─────────────────────────────────────────────────────────────────
|
|
op.create_table(
|
|
"jobs",
|
|
sa.Column("id", UUID(as_uuid=True), primary_key=True, server_default=sa.text("gen_random_uuid()")),
|
|
sa.Column("workspace_id", UUID(as_uuid=True), sa.ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False),
|
|
sa.Column("user_id", sa.String(255), nullable=False),
|
|
sa.Column("filename", sa.String(500), nullable=False),
|
|
sa.Column("file_size", sa.Integer, nullable=True),
|
|
sa.Column("status", sa.String(50), nullable=False, server_default="pending"),
|
|
sa.Column("accessibility_score", sa.Float, nullable=True),
|
|
sa.Column("result", JSONB, nullable=True),
|
|
sa.Column("error_message", sa.Text, nullable=True),
|
|
sa.Column("created_at", sa.TIMESTAMP(timezone=True), server_default=sa.text("NOW()")),
|
|
sa.Column("updated_at", sa.TIMESTAMP(timezone=True), server_default=sa.text("NOW()")),
|
|
sa.Column("completed_at", sa.TIMESTAMP(timezone=True), nullable=True),
|
|
)
|
|
op.create_index("idx_jobs_workspace", "jobs", ["workspace_id"])
|
|
op.create_index("idx_jobs_status", "jobs", ["status"])
|
|
|
|
# ── Usage Events ─────────────────────────────────────────────────────────
|
|
op.create_table(
|
|
"usage_events",
|
|
sa.Column("id", sa.BigInteger, primary_key=True, autoincrement=True),
|
|
sa.Column("workspace_id", UUID(as_uuid=True), sa.ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False),
|
|
sa.Column("event_type", sa.String(100), nullable=False),
|
|
sa.Column("job_id", UUID(as_uuid=True), nullable=True),
|
|
sa.Column("created_at", sa.TIMESTAMP(timezone=True), server_default=sa.text("NOW()")),
|
|
)
|
|
op.create_index("idx_usage_workspace_month", "usage_events", ["workspace_id", "created_at"])
|
|
|
|
# ── Row-Level Security ────────────────────────────────────────────────────
|
|
# app.workspace_id is set per-request in FastAPI deps.py
|
|
for table in ("jobs", "usage_events"):
|
|
op.execute(f"ALTER TABLE {table} ENABLE ROW LEVEL SECURITY")
|
|
op.execute(f"""
|
|
CREATE POLICY workspace_isolation ON {table}
|
|
USING (workspace_id = current_setting('app.workspace_id', true)::uuid)
|
|
""")
|
|
|
|
op.execute("ALTER TABLE workspaces ENABLE ROW LEVEL SECURITY")
|
|
op.execute("""
|
|
CREATE POLICY workspace_self ON workspaces
|
|
USING (id = current_setting('app.workspace_id', true)::uuid)
|
|
""")
|
|
|
|
op.execute("ALTER TABLE workspace_members ENABLE ROW LEVEL SECURITY")
|
|
op.execute("""
|
|
CREATE POLICY members_workspace ON workspace_members
|
|
USING (workspace_id = current_setting('app.workspace_id', true)::uuid)
|
|
""")
|
|
|
|
|
|
def downgrade() -> None:
|
|
for table in ("usage_events", "jobs", "workspace_members", "workspaces"):
|
|
op.execute(f"DROP TABLE IF EXISTS {table} CASCADE")
|