115 lines
4.4 KiB
Python
115 lines
4.4 KiB
Python
from sqlalchemy import inspect, text
|
|
|
|
from app.database import engine
|
|
|
|
|
|
def run_migrations() -> None:
|
|
"""Apply lightweight schema migrations for existing databases."""
|
|
inspector = inspect(engine)
|
|
tables = inspector.get_table_names()
|
|
dialect = engine.dialect.name
|
|
|
|
if "document_templates" in tables:
|
|
columns = {col["name"] for col in inspector.get_columns("document_templates")}
|
|
if "is_public" not in columns:
|
|
default = "0" if dialect == "sqlite" else "false"
|
|
with engine.begin() as conn:
|
|
conn.execute(
|
|
text(
|
|
f"ALTER TABLE document_templates "
|
|
f"ADD COLUMN is_public BOOLEAN NOT NULL DEFAULT {default}"
|
|
)
|
|
)
|
|
|
|
if "verification_tokens" not in tables:
|
|
with engine.begin() as conn:
|
|
if dialect == "postgresql":
|
|
conn.execute(
|
|
text(
|
|
"""
|
|
CREATE TABLE verification_tokens (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
code VARCHAR(10) NOT NULL,
|
|
token_type VARCHAR(20) NOT NULL,
|
|
expires_at TIMESTAMP NOT NULL,
|
|
created_at TIMESTAMP NOT NULL
|
|
)
|
|
"""
|
|
)
|
|
)
|
|
else:
|
|
conn.execute(
|
|
text(
|
|
"""
|
|
CREATE TABLE verification_tokens (
|
|
id INTEGER NOT NULL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL,
|
|
code VARCHAR(10) NOT NULL,
|
|
token_type VARCHAR(20) NOT NULL,
|
|
expires_at DATETIME NOT NULL,
|
|
created_at DATETIME NOT NULL,
|
|
FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE
|
|
)
|
|
"""
|
|
)
|
|
)
|
|
conn.execute(
|
|
text(
|
|
"CREATE INDEX IF NOT EXISTS ix_verification_tokens_code "
|
|
"ON verification_tokens (code)"
|
|
)
|
|
)
|
|
|
|
if "audit_logs" not in tables:
|
|
with engine.begin() as conn:
|
|
if dialect == "postgresql":
|
|
conn.execute(
|
|
text(
|
|
"""
|
|
CREATE TABLE audit_logs (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
username VARCHAR(50),
|
|
action VARCHAR(100) NOT NULL,
|
|
resource_type VARCHAR(50),
|
|
resource_id INTEGER,
|
|
details JSON,
|
|
ip_address VARCHAR(45),
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
)
|
|
"""
|
|
)
|
|
)
|
|
else:
|
|
conn.execute(
|
|
text(
|
|
"""
|
|
CREATE TABLE audit_logs (
|
|
id INTEGER NOT NULL PRIMARY KEY,
|
|
user_id INTEGER,
|
|
username VARCHAR(50),
|
|
action VARCHAR(100) NOT NULL,
|
|
resource_type VARCHAR(50),
|
|
resource_id INTEGER,
|
|
details JSON,
|
|
ip_address VARCHAR(45),
|
|
created_at DATETIME NOT NULL,
|
|
FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE SET NULL
|
|
)
|
|
"""
|
|
)
|
|
)
|
|
conn.execute(
|
|
text(
|
|
"CREATE INDEX IF NOT EXISTS ix_audit_logs_created_at "
|
|
"ON audit_logs (created_at DESC)"
|
|
)
|
|
)
|
|
conn.execute(
|
|
text(
|
|
"CREATE INDEX IF NOT EXISTS ix_audit_logs_action "
|
|
"ON audit_logs (action)"
|
|
)
|
|
)
|