Issue 03: PostgreSQL Schema and Migrations

[laraibg786]

Goal

Define the PostgreSQL schema and set up an Alembic migration pipeline for MVP entities (vendors, licenses, license_sessions, audit_logs).

Requirements

Functional

• Initialize Alembic for migration management.
• Create vendors table (id, email, password_hash, name, timestamps).
• Create licenses table (vendor_id, key, status, customer info, fingerprint, expiration, timestamps).
• Create license_sessions table (license_id, session_token, timestamps, heartbeat tracking).
• Create audit_logs table (vendor_id, action, actor, target info, metadata, timestamps).
• Implement Foreign Key constraints and appropriate indexes.

Technical

• ORM/Migrations: SQLAlchemy + Alembic 1.x.
• Database: PostgreSQL 15+.
• Primary Keys: Use UUIDs (gen_random_uuid()).
• Constraints: Foreign keys with ON DELETE CASCADE, unique constraints on email and license keys, check constraint for license status enum (active, expired, revoked).
• Indexes: High-cardinality columns and frequently queried fields.

Acceptance Criteria

• Initial migration creates all 4 tables with correct schema.
• Foreign keys, unique constraints, and check constraints are enforced by the DB.
• Migrations are reversible (downgrade/upgrade) and idempotent.
• Schema diagram or ERD documented in README.

Dependencies

• Blocked by: Issue 02: Docker Compose Provisioning #2.
• Blocks: Issue 04: Row-Level Security (RLS) #4, Issue 06: JWT Authentication #6, Issue 10: License Key Generator #10, etc.