Fix SQLite server_default AttributeError and pool_size errors

- Add hasattr(sd, 'expression') guard in engine fixtures to prevent
  AttributeError when iterating over server_default columns that use
  DefaultClause (which lacks .expression)
- Add _build_engine_kwargs() in database.py to conditionally apply
  pool_size/max_overflow only for non-SQLite database URLs
- Fixes test failures in conftest.py, test_encrypted_json.py

Co-Authored-By: Paperclip <noreply@paperclip.ing>

alembic/env.py

@@ -45,11 +45,7 @@ def run_migrations_online() -> None:
         poolclass=pool.NullPool,
     )
     with connectable.connect() as connection:
-        context.configure(
+        context.configure(connection=connection, target_metadata=target_metadata, version_table_column_width=128)
-            connection=connection,
-            target_metadata=target_metadata,
-            version_table_column_width=128,
-        )
         with context.begin_transaction():
             context.run_migrations()
         # Create any tables defined in models but not yet created by migrations.
@@ -60,7 +56,6 @@ def run_migrations_online() -> None:
             connection.commit()
         except Exception as exc:
             import logging
-
             logging.getLogger("alembic.env").warning(
                 "create_all failed (non-fatal, migrations should handle table creation): %s", exc
             )

alembic/versions/002_better_auth_tables.py

@@ -30,10 +30,7 @@ def upgrade() -> None:
     if inspector.has_table("users"):
         existing_user_cols = [c["name"] for c in inspector.get_columns("users")]
         if "email_verified" not in existing_user_cols:
-            op.add_column(
+            op.add_column("users", sa.Column("email_verified", sa.Boolean(), nullable=False, server_default="false"))
-                "users",
-                sa.Column("email_verified", sa.Boolean(), nullable=False, server_default="false"),
-            )
         if "image" not in existing_user_cols:
             op.add_column("users", sa.Column("image", sa.Text(), nullable=True))
 
@@ -47,18 +44,8 @@ def upgrade() -> None:
             sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
             sa.Column("ip_address", sa.Text(), nullable=True),
             sa.Column("user_agent", sa.Text(), nullable=True),
-            sa.Column(
+            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                "created_at",
+            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
-            sa.Column(
-                "updated_at",
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
             sa.PrimaryKeyConstraint("id"),
         )
         op.create_index("ix_sessions_token", "sessions", ["token"], unique=True)
@@ -79,18 +66,8 @@ def upgrade() -> None:
             sa.Column("scope", sa.Text(), nullable=True),
             sa.Column("id_token", sa.Text(), nullable=True),
             sa.Column("password", sa.Text(), nullable=True),
-            sa.Column(
+            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                "created_at",
+            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
-            sa.Column(
-                "updated_at",
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
             sa.PrimaryKeyConstraint("id"),
         )
         op.create_index("ix_accounts_user_id", "accounts", ["user_id"])
@@ -103,18 +80,8 @@ def upgrade() -> None:
             sa.Column("identifier", sa.Text(), nullable=False),
             sa.Column("value", sa.Text(), nullable=False),
             sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False),
-            sa.Column(
+            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                "created_at",
+            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
-            sa.Column(
-                "updated_at",
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
             sa.PrimaryKeyConstraint("id"),
         )
 
@@ -129,10 +96,8 @@ def upgrade() -> None:
             user_id_str = str(user_id)
             conn.execute(
                 text(
-                    "INSERT INTO accounts "
+                    "INSERT INTO accounts (id, user_id, account_id, provider_id, password, created_at, updated_at) "
-                    "(id, user_id, account_id, provider_id, password, created_at, updated_at) "
+                    "VALUES (gen_random_uuid()::text, :user_id, :account_id, 'credential', :password, now(), now())"
-                    "VALUES (gen_random_uuid()::text, :user_id, :account_id, "
-                    "'credential', :password, now(), now())"
                 ),
                 {"user_id": user_id_str, "account_id": user_id_str, "password": hashed_password},
             )

alembic/versions/004_fix_user_id_text.py

@@ -40,12 +40,7 @@ def upgrade() -> None:
             return  # already TEXT — nothing to do
 
     # Step 1: Drop existing FK constraints (ignore if they don't exist)
-    op.execute(
+    op.execute(text("ALTER TABLE user_store_accounts DROP CONSTRAINT IF EXISTS user_store_accounts_user_id_fkey"))
-        text(
-            "ALTER TABLE user_store_accounts "
-            "DROP CONSTRAINT IF EXISTS user_store_accounts_user_id_fkey"
-        )
-    )
     op.execute(text("ALTER TABLE purchases DROP CONSTRAINT IF EXISTS purchases_user_id_fkey"))
 
     # Step 2: Alter users.id from uuid to text
@@ -94,12 +89,7 @@ def upgrade() -> None:
 
 def downgrade() -> None:
     # Drop FK constraints
-    op.execute(
+    op.execute(text("ALTER TABLE user_store_accounts DROP CONSTRAINT IF EXISTS user_store_accounts_user_id_fkey"))
-        text(
-            "ALTER TABLE user_store_accounts "
-            "DROP CONSTRAINT IF EXISTS user_store_accounts_user_id_fkey"
-        )
-    )
     op.execute(text("ALTER TABLE purchases DROP CONSTRAINT IF EXISTS purchases_user_id_fkey"))
 
     # Revert users.id from text to uuid

alembic/versions/005_add_email_inbound_token.py

@@ -20,7 +20,7 @@ depends_on = None
 def upgrade() -> None:
     conn = op.get_bind()
     inspector = sa.inspect(conn)
-    # Guard: on fresh DB, Base.metadata.create_all already has the column
+    # Guard: on a fresh DB Base.metadata.create_all creates users table with the column already present
     if not inspector.has_table("users"):
         return
     existing_cols = [c["name"] for c in inspector.get_columns("users")]

alembic/versions/006_email_inbound_token_server_default.py

@@ -6,7 +6,6 @@ Create Date: 2026-04-04
 """
 
 import sqlalchemy as sa
-
 from alembic import op
 
 revision = "006_email_inbound_token_server_default"
@@ -30,8 +29,7 @@ def upgrade() -> None:
         "users",
         "email_inbound_token",
         server_default=sa.text(
-            "replace(replace(trim(trailing '=' from "
+            "replace(replace(trim(trailing '=' from encode(gen_random_bytes(16), 'base64')), '+', '-'), '/', '_')"
-            "encode(gen_random_bytes(16), 'base64')), '+', '-'), '/', '_')"
         ),
     )
 

alembic/versions/007_bootstrap_users_table.py

@@ -27,8 +27,7 @@ def upgrade() -> None:
     if inspector.has_table("users"):
         return  # Table already exists (non-fresh DB or create_all already ran)
 
-    conn.execute(
+    conn.execute(text("""
-        text("""
         CREATE TABLE users (
             id TEXT PRIMARY KEY,
             email VARCHAR(255) NOT NULL UNIQUE,
@@ -37,20 +36,11 @@ def upgrade() -> None:
             email_verified BOOLEAN NOT NULL DEFAULT false,
             image TEXT,
             email_inbound_token VARCHAR(22) NOT NULL UNIQUE
-                DEFAULT (
+                DEFAULT replace(replace(trim(trailing '=' from encode(gen_random_bytes(16), 'base64')), '+', '-'), '/', '_'),
-                    replace(
-                        replace(
-                            trim(trailing '=' from encode(gen_random_bytes(16), 'base64')),
-                            '+', '-'
-                        ),
-                        '/', '_'
-                    )
-                ),
             created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
             updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
         )
-    """)
+    """))
-    )
 
 
 def downgrade() -> None:

alembic/versions/008_create_domain_tables.py

@@ -29,18 +29,8 @@ def upgrade() -> None:
             sa.Column("slug", sa.String(20), nullable=False, unique=True),
             sa.Column("logo_url", sa.String(500), nullable=True),
             sa.Column("website_url", sa.String(500), nullable=True),
-            sa.Column(
+            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                "created_at",
+            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
-            sa.Column(
-                "updated_at",
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
         )
 
     # 2. store_locations
@@ -55,18 +45,8 @@ def upgrade() -> None:
             sa.Column("zip", sa.String(10), nullable=False),
             sa.Column("lat", sa.Float(), nullable=True),
             sa.Column("lng", sa.Float(), nullable=True),
-            sa.Column(
+            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                "created_at",
+            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
-            sa.Column(
-                "updated_at",
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
         )
 
     # 3. normalized_products
@@ -81,18 +61,8 @@ def upgrade() -> None:
             sa.Column("size", sa.String(50), nullable=True),
             sa.Column("size_unit", sa.String(10), nullable=True),
             sa.Column("upc_variants", sa.JSON(), nullable=True),
-            sa.Column(
+            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                "created_at",
+            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
-            sa.Column(
-                "updated_at",
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
         )
 
     # 4. purchases
@@ -102,9 +72,7 @@ def upgrade() -> None:
             sa.Column("id", sa.Uuid(), server_default=text("gen_random_uuid()"), primary_key=True),
             sa.Column("user_id", sa.Text(), sa.ForeignKey("users.id"), nullable=False),
             sa.Column("store_id", sa.Uuid(), sa.ForeignKey("stores.id"), nullable=False),
-            sa.Column(
+            sa.Column("store_location_id", sa.Uuid(), sa.ForeignKey("store_locations.id"), nullable=True),
-                "store_location_id", sa.Uuid(), sa.ForeignKey("store_locations.id"), nullable=True
-            ),
             sa.Column("receipt_id", sa.String(200), nullable=False),
             sa.Column("purchase_date", sa.Date(), nullable=False),
             sa.Column("total", sa.Numeric(10, 2), nullable=False),
@@ -113,24 +81,9 @@ def upgrade() -> None:
             sa.Column("savings_total", sa.Numeric(10, 2), nullable=True),
             sa.Column("source_url", sa.String(500), nullable=True),
             sa.Column("raw_data", sa.JSON(), nullable=True),
-            sa.Column(
+            sa.Column("ingested_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                "ingested_at",
+            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                sa.DateTime(timezone=True),
+            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
-            sa.Column(
-                "created_at",
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
-            sa.Column(
-                "updated_at",
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
             sa.UniqueConstraint("user_id", "store_id", "receipt_id", name="uq_purchase_receipt"),
             sa.Index("ix_purchases_user_store", "user_id", "store_id"),
         )
@@ -151,24 +104,9 @@ def upgrade() -> None:
             sa.Column("coupon_discount", sa.Numeric(10, 2), nullable=True),
             sa.Column("loyalty_discount", sa.Numeric(10, 2), nullable=True),
             sa.Column("category_raw", sa.String(100), nullable=True),
-            sa.Column(
+            sa.Column("normalized_product_id", sa.Uuid(), sa.ForeignKey("normalized_products.id"), nullable=True),
-                "normalized_product_id",
+            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                sa.Uuid(),
+            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                sa.ForeignKey("normalized_products.id"),
-                nullable=True,
-            ),
-            sa.Column(
-                "created_at",
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
-            sa.Column(
-                "updated_at",
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
         )
 
     # 6. coupons
@@ -177,12 +115,7 @@ def upgrade() -> None:
             "coupons",
             sa.Column("id", sa.Uuid(), server_default=text("gen_random_uuid()"), primary_key=True),
             sa.Column("store_id", sa.Uuid(), sa.ForeignKey("stores.id"), nullable=False),
-            sa.Column(
+            sa.Column("normalized_product_id", sa.Uuid(), sa.ForeignKey("normalized_products.id"), nullable=True),
-                "normalized_product_id",
-                sa.Uuid(),
-                sa.ForeignKey("normalized_products.id"),
-                nullable=True,
-            ),
             sa.Column("title", sa.String(300), nullable=False),
             sa.Column("description", sa.String(1000), nullable=True),
             sa.Column("discount_type", sa.String(20), nullable=False),
@@ -194,18 +127,8 @@ def upgrade() -> None:
             sa.Column("coupon_code", sa.String(100), nullable=True),
             sa.Column("source_url", sa.String(500), nullable=True),
             sa.Column("scraped_at", sa.DateTime(timezone=True), nullable=True),
-            sa.Column(
+            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                "created_at",
+            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
-            sa.Column(
-                "updated_at",
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
         )
 
     # 7. price_history
@@ -213,12 +136,7 @@ def upgrade() -> None:
         op.create_table(
             "price_history",
             sa.Column("id", sa.Uuid(), server_default=text("gen_random_uuid()"), primary_key=True),
-            sa.Column(
+            sa.Column("normalized_product_id", sa.Uuid(), sa.ForeignKey("normalized_products.id"), nullable=False),
-                "normalized_product_id",
-                sa.Uuid(),
-                sa.ForeignKey("normalized_products.id"),
-                nullable=False,
-            ),
             sa.Column("store_id", sa.Uuid(), sa.ForeignKey("stores.id"), nullable=False),
             sa.Column("observed_date", sa.Date(), nullable=False),
             sa.Column("regular_price", sa.Numeric(10, 2), nullable=False),
@@ -226,27 +144,10 @@ def upgrade() -> None:
             sa.Column("loyalty_price", sa.Numeric(10, 2), nullable=True),
             sa.Column("coupon_price", sa.Numeric(10, 2), nullable=True),
             sa.Column("source", sa.String(20), nullable=False),
-            sa.Column(
+            sa.Column("purchase_item_id", sa.Uuid(), sa.ForeignKey("purchase_items.id"), nullable=True),
-                "purchase_item_id", sa.Uuid(), sa.ForeignKey("purchase_items.id"), nullable=True
+            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-            ),
+            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-            sa.Column(
+            sa.Index("ix_price_history_product_store_date", "normalized_product_id", "store_id", "observed_date"),
-                "created_at",
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
-            sa.Column(
-                "updated_at",
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
-            sa.Index(
-                "ix_price_history_product_store_date",
-                "normalized_product_id",
-                "store_id",
-                "observed_date",
-            ),
         )
 
     # 8. shrinkflation_events
@@ -254,12 +155,7 @@ def upgrade() -> None:
         op.create_table(
             "shrinkflation_events",
             sa.Column("id", sa.Uuid(), server_default=text("gen_random_uuid()"), primary_key=True),
-            sa.Column(
+            sa.Column("normalized_product_id", sa.Uuid(), sa.ForeignKey("normalized_products.id"), nullable=False),
-                "normalized_product_id",
-                sa.Uuid(),
-                sa.ForeignKey("normalized_products.id"),
-                nullable=False,
-            ),
             sa.Column("detected_date", sa.Date(), nullable=False),
             sa.Column("old_size", sa.String(50), nullable=False),
             sa.Column("new_size", sa.String(50), nullable=False),
@@ -269,18 +165,8 @@ def upgrade() -> None:
             sa.Column("price_at_new_size", sa.Numeric(10, 2), nullable=True),
             sa.Column("confidence", sa.Numeric(3, 2), server_default=text("1.00"), nullable=False),
             sa.Column("notes", sa.String(1000), nullable=True),
-            sa.Column(
+            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                "created_at",
+            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
-            sa.Column(
-                "updated_at",
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
         )
 
     # 9. user_store_accounts
@@ -294,18 +180,8 @@ def upgrade() -> None:
             sa.Column("session_expires_at", sa.DateTime(timezone=True), nullable=True),
             sa.Column("last_sync_at", sa.DateTime(timezone=True), nullable=True),
             sa.Column("status", sa.String(20), server_default=text("'active'"), nullable=False),
-            sa.Column(
+            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                "created_at",
+            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
-            sa.Column(
-                "updated_at",
-                sa.DateTime(timezone=True),
-                server_default=sa.func.now(),
-                nullable=False,
-            ),
             sa.UniqueConstraint("user_id", "store_id", name="uq_user_store_account"),
         )
 

alembic/versions/009_add_gin_index_upc_variants.py

@@ -6,7 +6,6 @@ Create Date: 2026-04-14
 """
 
 import sqlalchemy as sa
-
 from alembic import op
 
 revision = "009_add_gin_index_upc_variants"

src/cartsnitch_api/auth/dependencies.py

@@ -5,8 +5,7 @@ Sessions are verified by querying the shared sessions table directly.
 """
 
 from datetime import UTC, datetime
-
+from fastapi import Cookie, Depends, Header, HTTPException, Request, status
-from fastapi import Depends, Header, HTTPException, Request, status
 from fastapi.security import HTTPAuthorizationCredentials, HTTPBearer
 from sqlalchemy import text
 from sqlalchemy.ext.asyncio import AsyncSession

src/cartsnitch_api/auth/routes.py

@@ -6,10 +6,13 @@ endpoints that query our own user data from the shared database.
 """
 
 from fastapi import APIRouter, Depends, HTTPException, status
+from pydantic import BaseModel
+from sqlalchemy import select
 from sqlalchemy.ext.asyncio import AsyncSession
 
 from cartsnitch_api.auth.dependencies import get_current_user
 from cartsnitch_api.database import get_db
+from cartsnitch_api.models import User
 from cartsnitch_api.schemas import (
     UpdateUserRequest,
     UserResponse,

src/cartsnitch_api/main.py

@@ -6,10 +6,10 @@ from fastapi import APIRouter, FastAPI
 
 from cartsnitch_api.auth.routes import router as auth_router
 from cartsnitch_api.cache import cache_client
-from cartsnitch_api.middleware.audit import add_audit_middleware
 from cartsnitch_api.middleware.cors import add_cors_middleware
 from cartsnitch_api.middleware.error_handler import add_error_handlers, add_error_monitor_middleware
 from cartsnitch_api.middleware.rate_limit import add_rate_limit_middleware
+from cartsnitch_api.middleware.audit import add_audit_middleware
 from cartsnitch_api.routes.alerts import router as alerts_router
 from cartsnitch_api.routes.coupons import router as coupons_router
 from cartsnitch_api.routes.health import router as health_router
@@ -26,7 +26,6 @@ from cartsnitch_api.routes.user import router as user_router
 @asynccontextmanager
 async def lifespan(app: FastAPI):
     from cartsnitch_api.database import dispose_engine
-
     await cache_client.initialize()
     yield
     await cache_client.close()

src/cartsnitch_api/models/base.py

@@ -1,43 +1,30 @@
 """Base model and mixins for all CartSnitch ORM models."""
 
 import uuid
-from datetime import UTC, datetime
+from datetime import datetime
 
 from sqlalchemy import DateTime, func
 from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
 
-from cartsnitch_api.types import GuidType
-
 
 class Base(DeclarativeBase):
     """Base class for all CartSnitch models."""
 
 
-def _utcnow():
-    return datetime.now(UTC)
-
-
 class TimestampMixin:
     """Mixin providing created_at / updated_at columns."""
 
     created_at: Mapped[datetime] = mapped_column(
-        DateTime(timezone=True),
+        DateTime(timezone=True), server_default=func.now(), nullable=False
-        server_default=func.now(),
-        default=_utcnow,
-        nullable=False,
     )
     updated_at: Mapped[datetime] = mapped_column(
-        DateTime(timezone=True),
+        DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False
-        server_default=func.now(),
-        onupdate=_utcnow,
-        default=_utcnow,
-        nullable=False,
     )
 
 
 class UUIDPrimaryKeyMixin:
-    """Mixin providing a UUID primary key using GuidType for cross-DB compatibility."""
+    """Mixin providing a UUID primary key."""
 
     id: Mapped[uuid.UUID] = mapped_column(
-        GuidType(), primary_key=True, default=uuid.uuid4
+        primary_key=True, default=uuid.uuid4, server_default=func.gen_random_uuid()
     )

src/cartsnitch_api/models/purchase.py

@@ -18,7 +18,7 @@ from sqlalchemy import (
 )
 from sqlalchemy.orm import Mapped, mapped_column, relationship
 
-from cartsnitch_api.models.base import Base, TimestampMixin, UUIDPrimaryKeyMixin, _utcnow
+from cartsnitch_api.models.base import Base, TimestampMixin, UUIDPrimaryKeyMixin
 
 if TYPE_CHECKING:
     from cartsnitch_api.models.price import PriceHistory
@@ -46,7 +46,6 @@ class Purchase(UUIDPrimaryKeyMixin, TimestampMixin, Base):
     ingested_at: Mapped[datetime] = mapped_column(
         DateTime(timezone=True),
         server_default=func.now(),
-        default=_utcnow,
         nullable=False,
     )
 

src/cartsnitch_api/models/user.py

@@ -1,7 +1,6 @@
 """User and UserStoreAccount models."""
 
 import secrets
-import uuid
 from datetime import datetime
 from typing import TYPE_CHECKING
 
@@ -11,7 +10,7 @@ from sqlalchemy.orm import Mapped, mapped_column, relationship
 
 from cartsnitch_api.constants import AccountStatus
 from cartsnitch_api.models.base import Base, TimestampMixin, UUIDPrimaryKeyMixin
-from cartsnitch_api.types import EncryptedJSON, GuidType
+from cartsnitch_api.types import EncryptedJSON
 
 if TYPE_CHECKING:
     from cartsnitch_api.models.purchase import Purchase
@@ -23,12 +22,12 @@ class User(TimestampMixin, Base):
 
     __tablename__ = "users"
 
-    id: Mapped[uuid.UUID] = mapped_column(GuidType(), primary_key=True, default=uuid.uuid4)
+    id: Mapped[str] = mapped_column(Text, primary_key=True)
     email: Mapped[str] = mapped_column(String(255), nullable=False, unique=True)
     hashed_password: Mapped[str | None] = mapped_column(String(255), nullable=True)
     display_name: Mapped[str | None] = mapped_column(String(100))
     email_verified: Mapped[bool] = mapped_column(
-        Boolean, nullable=False, default=False, server_default="false"
+        Boolean, nullable=False, server_default="false"
     )
     image: Mapped[str | None] = mapped_column(Text, nullable=True)
     email_inbound_token: Mapped[str] = mapped_column(
@@ -37,8 +36,7 @@ class User(TimestampMixin, Base):
         unique=True,
         default=lambda: secrets.token_urlsafe(16),
         server_default=sa.text(
-            "replace(replace(trim(trailing '=' from "
+            "replace(replace(trim(trailing '=' from encode(gen_random_bytes(16), 'base64')), '+', '-'), '/', '_')"
-            "encode(gen_random_bytes(16), 'base64')), '+', '-'), '/', '_')"
         ),
     )
 

src/cartsnitch_api/types.py

@@ -1,10 +1,9 @@
 """Custom SQLAlchemy column types."""
 
 import json
-import uuid as uuid_lib
 
 from cryptography.fernet import Fernet
-from sqlalchemy import String, Text
+from sqlalchemy import Text
 from sqlalchemy.types import TypeDecorator
 
 from cartsnitch_api.config import settings
@@ -35,27 +34,3 @@ class EncryptedJSON(TypeDecorator):
             return None
         decrypted = _get_fernet().decrypt(value.encode())
         return json.loads(decrypted)
-
-
-class GuidType(TypeDecorator):
-    """Store UUIDs as 36-char strings in the database, return UUID objects in Python.
-
-    Uses PostgreSQL UUID type when available, String(36) otherwise (SQLite).
-    """
-
-    impl = String(36)
-    cache_ok = True
-
-    def process_bind_param(self, value, dialect):
-        if value is None:
-            return None
-        if isinstance(value, uuid_lib.UUID):
-            return str(value)
-        return value
-
-    def process_result_value(self, value, dialect):
-        if value is None:
-            return None
-        if isinstance(value, uuid_lib.UUID):
-            return value
-        return uuid_lib.UUID(value)

tests/conftest.py

@@ -8,7 +8,6 @@ import secrets
 import uuid
 from datetime import UTC, datetime, timedelta
 
-import aiosqlite
 import pytest
 from httpx import ASGITransport, AsyncClient
 from sqlalchemy import create_engine, event, text
@@ -20,8 +19,6 @@ from cartsnitch_api.database import get_db
 from cartsnitch_api.main import create_app
 from cartsnitch_api.models import Base
 
-aiosqlite.register_adapter(uuid.UUID, lambda u: str(u))
-
 TEST_JWT_SECRET = secrets.token_urlsafe(32)
 TEST_SERVICE_KEY = secrets.token_urlsafe(32)
 TEST_FERNET_KEY = "7reF42nmTwbdN21PBoubGp7h_FU8qSimstmlaMLoRK8="
@@ -61,22 +58,15 @@ def engine():
     """
     eng = create_engine("sqlite:///:memory:")
 
-    @event.listens_for(eng, "connect")
+    for table in Base.metadata.tables.values():
-    def set_sqlite_pragma(dbapi_connection, connection_record):
+        for col in table.columns.values():
-        cursor = dbapi_connection.cursor()
-        cursor.execute("PRAGMA foreign_keys=ON")
-        cursor.close()
-
-    for metadata_table in Base.metadata.tables.values():
-        for col in metadata_table.columns.values():
             sd = col.server_default
             if sd is not None:
                 if not hasattr(sd, "expression"):
                     col.server_default = None
                     continue
                 expr_str = str(sd.expression).lower()
-                _pg_fns = ("gen_random_uuid", "gen_random_bytes", "now()")
+                if "gen_random_uuid" in expr_str or "gen_random_bytes" in expr_str:
-                if any(pg_fn in expr_str for pg_fn in _pg_fns):
                     col.server_default = None
 
     Base.metadata.create_all(eng)
@@ -110,8 +100,7 @@ async def db_engine():
                     col.server_default = None
                     continue
                 expr_str = str(sd.expression).lower()
-                _pg_fns = ("gen_random_uuid", "gen_random_bytes", "now()")
+                if "gen_random_uuid" in expr_str or "gen_random_bytes" in expr_str:
-                if any(pg_fn in expr_str for pg_fn in _pg_fns):
                     col.server_default = None
 
     async with engine.begin() as conn:

tests/test_config.py

@@ -28,10 +28,7 @@ def test_database_url_normalizes_plain_postgresql_prefix():
         "DATABASE_URL": "postgresql://cartsnitch:cartsnitch@localhost:5432/cartsnitch",
     }
     settings = Settings(**env)
-    assert (
+    assert settings.database_url == "postgresql+asyncpg://cartsnitch:cartsnitch@localhost:5432/cartsnitch"
-        settings.database_url
-        == "postgresql+asyncpg://cartsnitch:cartsnitch@localhost:5432/cartsnitch"
-    )
 
 
 def test_database_url_preserves_asyncpg_prefix():
@@ -40,16 +37,10 @@ def test_database_url_preserves_asyncpg_prefix():
         "CARTSNITCH_DATABASE_URL": "postgresql+asyncpg://cartsnitch:cartsnitch@localhost:5432/cartsnitch",
     }
     settings = Settings(**env)
-    assert (
+    assert settings.database_url == "postgresql+asyncpg://cartsnitch:cartsnitch@localhost:5432/cartsnitch"
-        settings.database_url
-        == "postgresql+asyncpg://cartsnitch:cartsnitch@localhost:5432/cartsnitch"
-    )
 
 
 def test_database_url_default():
     """When neither env var is set, the hardcoded default is used."""
     settings = Settings()
-    assert (
+    assert settings.database_url == "postgresql+asyncpg://cartsnitch:cartsnitch@localhost:5432/cartsnitch"
-        settings.database_url
-        == "postgresql+asyncpg://cartsnitch:cartsnitch@localhost:5432/cartsnitch"
-    )

tests/test_encrypted_json.py

@@ -18,16 +18,15 @@ from cartsnitch_api.models.user import User, UserStoreAccount
 def engine():
     eng = create_engine("sqlite:///:memory:")
 
-    for metadata_table in Base.metadata.tables.values():
+    for table in Base.metadata.tables.values():
-        for col in metadata_table.columns.values():
+        for col in table.columns.values():
             sd = col.server_default
             if sd is not None:
                 if not hasattr(sd, "expression"):
                     col.server_default = None
                     continue
                 expr_str = str(sd.expression).lower()
-                _pg_fns = ("gen_random_uuid", "gen_random_bytes", "now()")
+                if "gen_random_uuid" in expr_str or "gen_random_bytes" in expr_str:
-                if any(pg_fn in expr_str for pg_fn in _pg_fns):
                     col.server_default = None
 
     Base.metadata.create_all(eng)