I want to add sorting feature to the repository table, mainly so I can show recently imported repository on top.
my first pass on this has a few gaps that need to be addressed from a architecture perspective.
created_at stores the GitHub creation date, not the import date
When repositories are imported from GitHub, the createdAt field is populated from GitHub's repo.created_at timestamp (src/lib/github.ts:290,351,495). This means sorting by "date added" isn't possible with the current schema.
we'd be sorting by when the repo was created on GitHub, which isn't useful for understanding when a user added it to the mirror system.
Deriving import date from the activities/jobs table is expensive
The mirrorJobs table tracks import events (jobType: "import"), so technically we could look up when a repo was first imported by querying the earliest job record. However, this would require a join or subquery per repository at render time, which is expensive — especially for users with hundreds of repos. This approach also breaks down if job history is cleaned up by the cleanup service.
Mobile table is a separate implementation on the frontend it seems, I didn't look at this too much.
Proposal:
Add a dedicated importedAt timestamp column to the repositories table in src/lib/db/schema.ts
Backfill strategy for existing data:
we can use current unixepoch() for those that don't have an exiting record.
or the migration can default to the current createdAt value.
It won't be perfectly accurate (it'll reflect the GitHub creation date), but it's a reasonable approximation for existing data.
let me know your thoughts
Originally created by @kapdon on GitHub (Mar 14, 2026).
Original GitHub issue: https://github.com/RayLabsHQ/gitea-mirror/issues/225
Originally assigned to: @arunavo4 on GitHub.
I want to add sorting feature to the repository table, mainly so I can show recently imported repository on top.
my first pass on this has a few gaps that need to be addressed from a architecture perspective.
1. created_at stores the GitHub creation date, not the import date
When repositories are imported from GitHub, the createdAt field is populated from GitHub's repo.created_at timestamp (src/lib/github.ts:290,351,495). This means sorting by "date added" isn't possible with the current schema.
we'd be sorting by when the repo was created on GitHub, which isn't useful for understanding when a user added it to the mirror system.
2. Deriving import date from the activities/jobs table is expensive
The mirrorJobs table tracks import events (jobType: "import"), so technically we could look up when a repo was first imported by querying the earliest job record. However, this would require a join or subquery per repository at render time, which is expensive — especially for users with hundreds of repos. This approach also breaks down if job history is cleaned up by the cleanup service.
3. Mobile table is a separate implementation on the frontend it seems, I didn't look at this too much.
## Proposal:
Add a dedicated importedAt timestamp column to the repositories table in src/lib/db/schema.ts
### Backfill strategy for existing data:
- we can use current unixepoch() for those that don't have an exiting record.
- or the migration can default to the current createdAt value.
It won't be perfectly accurate (it'll reflect the GitHub creation date), but it's a reasonable approximation for existing data.
let me know your thoughts
Added index: (user_id, imported_at) for fast user-scoped sorting.
Updated /api/github/repositories default ordering to importedAt DESC, then name.
Included importedAt in repository response typing.
How imported time is determined
New imports: set importedAt = now in GitHub import/sync flows.
Existing rows (migration backfill): use earliest matching mirror_jobs.timestamp where status='imported'.
Match by repository_id when present.
Fallback match by normalized repository name/full-name when repository_id is missing.
Final fallback: if no job match exists, use repositories.created_at.
Sorting outcome
Repository list now has a stable "recently imported first" default based on real import time.
This avoids drift from mutable fields and gives predictable sorting for users.
<!-- gh-comment-id:4062098509 -->
@arunavo4 commented on GitHub (Mar 15, 2026):
Implemented in #226.
### What we changed
- Added `repositories.imported_at` (`NOT NULL`, default `unixepoch()`).
- Added index: `(user_id, imported_at)` for fast user-scoped sorting.
- Updated `/api/github/repositories` default ordering to `importedAt DESC`, then name.
- Included `importedAt` in repository response typing.
### How imported time is determined
- **New imports:** set `importedAt = now` in GitHub import/sync flows.
- **Existing rows (migration backfill):** use earliest matching `mirror_jobs.timestamp` where `status='imported'`.
- Match by `repository_id` when present.
- Fallback match by normalized repository name/full-name when `repository_id` is missing.
- **Final fallback:** if no job match exists, use `repositories.created_at`.
### Sorting outcome
- Repository list now has a stable "recently imported first" default based on real import time.
- This avoids drift from mutable fields and gives predictable sorting for users.
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
Originally created by @kapdon on GitHub (Mar 14, 2026).
Original GitHub issue: https://github.com/RayLabsHQ/gitea-mirror/issues/225
Originally assigned to: @arunavo4 on GitHub.
I want to add sorting feature to the repository table, mainly so I can show recently imported repository on top.
my first pass on this has a few gaps that need to be addressed from a architecture perspective.
When repositories are imported from GitHub, the createdAt field is populated from GitHub's repo.created_at timestamp (src/lib/github.ts:290,351,495). This means sorting by "date added" isn't possible with the current schema.
we'd be sorting by when the repo was created on GitHub, which isn't useful for understanding when a user added it to the mirror system.
The mirrorJobs table tracks import events (jobType: "import"), so technically we could look up when a repo was first imported by querying the earliest job record. However, this would require a join or subquery per repository at render time, which is expensive — especially for users with hundreds of repos. This approach also breaks down if job history is cleaned up by the cleanup service.
Proposal:
Add a dedicated importedAt timestamp column to the repositories table in src/lib/db/schema.ts
Backfill strategy for existing data:
It won't be perfectly accurate (it'll reflect the GitHub creation date), but it's a reasonable approximation for existing data.
let me know your thoughts
@arunavo4 commented on GitHub (Mar 15, 2026):
Implemented in #226.
What we changed
repositories.imported_at(NOT NULL, defaultunixepoch()).(user_id, imported_at)for fast user-scoped sorting./api/github/repositoriesdefault ordering toimportedAt DESC, then name.importedAtin repository response typing.How imported time is determined
importedAt = nowin GitHub import/sync flows.mirror_jobs.timestampwherestatus='imported'.repository_idwhen present.repository_idis missing.repositories.created_at.Sorting outcome