Adds the five auth_ tables and one DDL-only Alembic revision. Nothing reads
or writes them yet; the data layer and endpoints follow in later commits.
All five tables are created now, not just the two phase 1 needs. The reason
is migrations/env.py's `target_metadata = Base.metadata`: if the database
carried auth_api_key while models.py did not, the next autogenerated
revision anyone produced would emit op.drop_table for it. Models and schema
have to move together, so the phase 2/5 tables land as models now and gain
their business logic later.
Design notes, all following existing conventions in this schema:
- Tables are prefixed auth_ because `user` and `session` are reserved words
in PostgreSQL and MySQL, and quoting them at every call site is a footgun.
- No relationship() on any auth table. Auth lookups are single-row queries
on an indexed column, so there is nothing to eager-load; a `sessions`
collection on AuthUser would fetch every session row on every login, and
lazy-loading one under asyncio raises MissingGreenlet.
- Enum-valued columns (level, actor_kind) are String, matching
multi_color_direction. There is not one sa.Enum in this schema, and adding
one would create a native PostgreSQL type and a MySQL ENUM(...) with three
different ALTER dialects.
- Uniqueness is enforced with unique indexes rather than named UNIQUE
constraints. That is the portable form across all four dialects, and it
keeps the multi-tenancy seam cheap: auth_user.username can later become a
composite (tenant_id, username) index without dropping a constraint whose
generated name differs per dialect.
- The revision is pure DDL with no DML, which is what makes it safe as a
single revision on CockroachDB -- the aafcd7fb0e84 -> 304a32906234 split
was needed only because the second half wrote data.
The revision body is alembic's own autogenerate output against a database at
415a8f855e14, so it matches the conventions of the existing revisions rather
than being hand-approximated.
Verified on SQLite: upgrade from scratch creates all five tables with the
four unique indexes; downgrade -1 drops them all; upgrade replays cleanly.
Critically, running autogenerate again after upgrade emits an empty
migration, proving the models and the schema agree and that no future
autogenerate will try to drop the phase 2/5 tables.
The declared floor was ">=3.10", but nothing enforced it: the Docker image
has pinned python:3.14-slim-bookworm for amd64/arm64/armv7 for some time,
and scripts/install.sh runs "uv sync" without UV_PYTHON_DOWNLOADS=0, so uv
fetches a managed interpreter when the system one is too old. Every
dependency declares a floor of <=3.10 with no upper cap.
Raising it unlocks enum.StrEnum, datetime.UTC and typing.Self for the
authentication work that follows.
Consequences handled here:
- uv.lock regenerated. Four pure-backport packages drop out
(async-timeout, backports-asyncio-runner, exceptiongroup, tomli).
- ruff target-version follows to py314, which switches on PEP 649 deferred
annotations and with them a batch of previously-dormant rules. The safe
automatic fixes are applied: datetime.timezone.utc -> datetime.UTC
(UP017) and redundant quotes dropped from annotations (UP037).
- TC001/TC002/TC003 are ignored rather than applied. They want type-only
imports moved into "if TYPE_CHECKING:" blocks, which is wrong here:
FastAPI resolves Annotated[AsyncSession, Depends(...)] and websocket
parameters at runtime, Pydantic resolves field types at runtime, and
SQLAlchemy resolves Mapped[...] at mapper-configuration time. A
TYPE_CHECKING-only import is absent from the runtime namespace, so
applying them breaks dependency injection. Ruff classifies all 50 as
unsafe fixes for that reason.
Verified: ruff check/format clean, uv lock --check clean, alembic head
resolves, SQLAlchemy mappers configure with all relationships intact and
lazy="selectin" preserved on the extra-field collections.
The spool listing already joins spool -> filament -> vendor, so joined
eager loading of all three `extra` collections multiplied their rows
together. With ~12 spool, ~9 filament and ~9 vendor extra fields, a
single GET /spool?limit=30 made the database materialize ~35k wide rows
(one per field combination) and then spent most of its time in
SQLAlchemy de-duplicating them back into 30 objects.
Switch the three `extra` relationships to lazy="selectin" so each
collection is fetched in its own WHERE id IN (...) query and nothing
multiplies. Measured on a 4911-spool SQLite database, GET /spool?limit=30
drops from 431 ms to 12 ms and limit=100 from 1358 ms to 23 ms, with
byte-identical responses across the spool/filament/vendor/group/search
endpoints and all 371 integration tests green on SQLite, PostgreSQL,
MariaDB and CockroachDB.
Claude-Session: https://claude.ai/code/session_014Cwk5Yyze9yPvkYkUtMAP4
The used filament weight is now kept track of instead of remaining weight. This adds robustness to measurement errors which could accumulate and then the filament usage wouldn't get tracked anymore once the weight hit 0. Now it will just keep going.
Donkie