Avoid full-table sorts in paginated user lists

[dr-hoseyn]

Problem

The dashboard requests /api/users with the default sort=-created_at. The endpoint builds its total count from the already-ordered, wide ORM statement:

SELECT count(*)
FROM (
    SELECT <all user columns>
    FROM users
    ORDER BY users.created_at DESC
)

There is no global index beginning with users.created_at; the existing (admin_id, created_at) index only helps admin-scoped queries. On large installations, an unfiltered dashboard request can therefore sort/scan the full users table twice: once for the count and once for the paginated rows.

The page ordering also lacks a unique tie-breaker, so offset pagination is not deterministic when users share the same sort value.

Evidence

SQLAlchemy compilation preserves the unnecessary ORDER BY for SQLite, PostgreSQL, and MySQL.

Synthetic in-memory SQLite benchmark with 500,000 users and a 50-row page:

Operation	Current	Optimized	Current plan
Total count	203.143 ms median	0.224 ms median	full scan + temporary B-tree sort
Default page	44.950 ms median	0.052 ms median	full scan + temporary B-tree sort

Adding the index alone still leaves the old count at 43.35 ms because it walks the ordered subquery. Both the count rewrite and the index are needed. This synthetic benchmark is directional, not a production latency guarantee.

Proposed change

• Rewrite the total query as a narrow count(users.id) while preserving filters and joins and explicitly removing page ordering.
• Add id as a direction-matched tie-breaker for deterministic offset pagination.
• Add a (created_at, id) index for the dashboard's default order.
• Build/drop the PostgreSQL index concurrently so upgrades keep user writes available.
• Add regression tests for generated SQL, owner filters, stable ordering, and index metadata.

Acceptance criteria

• The count query contains no ORDER BY and selects no wide user payload columns.
• Owner/admin filters still produce accurate totals.
• Default sorting is created_at DESC, id DESC.
• The schema exposes (created_at, id) in that order.
• Upgrade/downgrade works on SQLite, PostgreSQL, and MySQL/MariaDB.
• Existing user API tests remain green.