refactor!: SQLModel Migration

[electron271]

This will need to be done before 0.1.0

TODO

• [x] Uninstall Prisma-related packages and install SQLModel packages.
• [x] Refactor models and DB scripts to SQLModel conventions.
• [ ] Replace all Prisma CRUD/query calls with SQLModel equivalents.
• [ ] Set up Alembic for database migrations.
• [ ] Adjust project requirements and documentation.

Packages

We would be using https://sqlmodel.tiangolo.com/ and https://github.com/sqlalchemy/alembic for migrations.

Drawbacks

• Async support uncertain

Advantages

• Fully python
• More mature project

Areas migration will affect:

• database schemas in tux/prisma will need to be re-written for SQLModel, and migrations performed on the database
• database controllers and associated logic in tux/tux/database
• Remindme cog directly pulls the Reminder DB model
• poll cog pulls in the CaseType enum
• AFK systems directly imports the AFK model
• snippets use the CaseType enum
• all moderation cogs pull in CaseType enum
• moderation cases cog
• influxdb cog
• utils/converters.py pulls in from prisma.enums
• utils/exceptions pulls in prisma.models.Case
• utils/flags pulls in the CaseType enum
• probably several more that we'll find during the process

Alternative to #850 Closes #852

Summary by Sourcery

Migrate the project’s database layer from Prisma to SQLModel, introducing SQLModel-based table models and an initial Alembic migration SQL script while removing legacy Prisma schema files.

New Features:

• Add SQLModel-based ORM models for all existing database entities
• Include an Alembic migration SQL file to create enums, tables, indexes, and foreign keys

Build:

• Add sqlmodel dependency to project configuration

Chores:

• Remove legacy Prisma schema and migration files

[sourcery-ai[bot]]

Reviewer's Guide

This PR overhauls the data layer by removing Prisma and its schema/commands, adding SQLModel and Alembic, providing an initial SQL migration, and defining new SQLModel-based schemas in a unified Python module.

Class diagram for new SQLModel-based data models

classDiagram
  class Guild {
    +int guild_id
    +datetime guild_joined_at
    +int case_count
    +GuildConfig config
    +list~Starboard~ starboards
    +list~Levels~ levels
    +list~StarboardMessage~ starboard_messages
    +list~AFKModel~ afk_models
    +list~Case~ cases
    +list~Snippet~ snippets
    +list~Reminder~ reminders
    +list~Note~ notes
  }
  class GuildConfig {
    +int guild_id
    +str prefix
    +int mod_log_id
    +int audit_log_id
    +int join_log_id
    +int private_log_id
    +int report_log_id
    +int dev_log_id
    +int jail_channel_id
    +int general_channel_id
    +int starboard_channel_id
    +int perm_level_0_role_id
    +int perm_level_1_role_id
    +int perm_level_2_role_id
    +int perm_level_3_role_id
    +int perm_level_4_role_id
    +int perm_level_5_role_id
    +int perm_level_6_role_id
    +int perm_level_7_role_id
    +int base_staff_role_id
    +int base_member_role_id
    +int jail_role_id
    +int quarantine_role_id
    +Guild guild
  }
  class Levels {
    +int member_id
    +float xp
    +int level
    +bool blacklisted
    +datetime last_message
    +int guild_id
    +Guild guild
  }
  class Starboard {
    +int guild_id
    +int starboard_channel_id
    +str starboard_emoji
    +int starboard_threshold
    +Guild guild
  }
  class StarboardMessage {
    +int message_id
    +str message_content
    +datetime message_created_at
    +datetime message_expires_at
    +int message_channel_id
    +int message_user_id
    +int message_guild_id
    +int star_count
    +int starboard_message_id
    +Guild guild
  }
  class AFKModel {
    +int member_id
    +str nickname
    +str reason
    +datetime since
    +datetime until
    +int guild_id
    +bool enforced
    +bool perm_afk
    +Guild guild
  }
  class CaseType {
    <<enum>>
    OTHER
    BAN
    UNBAN
    HACKBAN
    TEMPBAN
    KICK
    SNIPPETBAN
    TIMEOUT
    UNTIMEOUT
    WARN
    JAIL
    UNJAIL
    SNIPPETUNBAN
    UNTEMPBAN
    POLLBAN
    POLLUNBAN
  }
  class Case {
    +int case_id
    +bool case_status
    +CaseType case_type
    +str case_reason
    +int case_moderator_id
    +int case_user_id
    +list~int~ case_user_roles
    +int case_number
    +datetime case_created_at
    +datetime case_expires_at
    +bool case_tempban_expired
    +int guild_id
    +Guild guild
  }
  class Snippet {
    +int snippet_id
    +str snippet_name
    +str snippet_content
    +int snippet_user_id
    +datetime snippet_created_at
    +int guild_id
    +int uses
    +bool locked
    +str alias
    +Guild guild
  }
  class Reminder {
    +int reminder_id
    +str reminder_content
    +datetime reminder_created_at
    +datetime reminder_expires_at
    +int reminder_channel_id
    +int reminder_user_id
    +bool reminder_sent
    +int guild_id
    +Guild guild
  }
  class Note {
    +int note_id
    +int note_user_id
    +int note_moderator_id
    +str note_content
    +datetime note_created_at
    +int note_number
    +int guild_id
    +Guild guild
  }

  GuildConfig --|> Guild
  Levels --|> Guild
  Starboard --|> Guild
  StarboardMessage --|> Guild
  AFKModel --|> Guild
  Case --|> Guild
  Snippet --|> Guild
  Reminder --|> Guild
  Note --|> Guild
  Case o-- CaseType

File-Level Changes

Change	Details	Files
Update project dependencies to SQLModel and Alembic	Removed Prisma package entries Added sqlmodel to pyproject.toml Added Alembic as a migration tool Updated poetry.lock accordingly	pyproject.toml poetry.lock
Add initial database migration SQL	Created CaseType enum in SQL Generated CREATE TABLE statements for all models Added indexes and foreign key constraints	prisma/migrations/20250405050252_/migration.sql prisma/migrations/migration_lock.toml
Define new SQLModel-based schemas	Implemented model classes with fields, relationships, and constraints Reproduced all entities (Guild, Config, Levels, etc.) using SQLModel conventions Included type annotations and default factories for timestamps	tux/database/schemas.py
Remove legacy Prisma schema and command definitions	Deleted all .prisma files under prisma/schema/commands Deleted all .prisma files under prisma/schema/guild	prisma/schema/commands/afk.prisma prisma/schema/commands/moderation.prisma prisma/schema/commands/reminder.prisma prisma/schema/commands/snippets.prisma prisma/schema/guild/config.prisma prisma/schema/guild/guild.prisma prisma/schema/guild/levels.prisma prisma/schema/guild/starboard.prisma

Assessment against linked issues

Issue	Objective	Addressed	Explanation
#852	Uninstall Prisma-related packages and install SQLModel packages.	✅
#852	Refactor models and DB scripts to SQLModel conventions.	✅
#852	Replace all Prisma CRUD/query calls with SQLModel equivalents.	❌	The PR does not replace Prisma CRUD/query calls with SQLModel equivalents.

Possibly linked issues

• #853: The PR implements the SQLModel migration by removing Prisma, adding SQLModel/Alembic, and defining new database schemas.
• #852: The PR implements a database ORM migration from Prisma to SQLModel, closing the issue which proposed migration to Tortoise.

---

Tips and commands

Interacting with Sourcery

• Trigger a new review: Comment @sourcery-ai review on the pull request.
• Continue discussions: Reply directly to Sourcery's review comments.
• Generate a GitHub issue from a review comment: Ask Sourcery to create an issue from a review comment by replying to it. You can also reply to a review comment with @sourcery-ai issue to create an issue from it.
• Generate a pull request title: Write @sourcery-ai anywhere in the pull request title to generate a title at any time. You can also comment @sourcery-ai title on the pull request to (re-)generate the title at any time.
• Generate a pull request summary: Write @sourcery-ai summary anywhere in the pull request body to generate a PR summary at any time exactly where you want it. You can also comment @sourcery-ai summary on the pull request to (re-)generate the summary at any time.
• Generate reviewer's guide: Comment @sourcery-ai guide on the pull request to (re-)generate the reviewer's guide at any time.
• Resolve all Sourcery comments: Comment @sourcery-ai resolve on the pull request to resolve all Sourcery comments. Useful if you've already addressed all the comments and don't want to see them anymore.
• Dismiss all Sourcery reviews: Comment @sourcery-ai dismiss on the pull request to dismiss all existing Sourcery reviews. Especially useful if you want to start fresh with a new review - don't forget to comment @sourcery-ai review to trigger a new review!

Customizing Your Experience

Access your dashboard to:

• Enable or disable review features such as the Sourcery-generated pull request summary, the reviewer's guide, and others.
• Change the review language.
• Add, remove or edit custom review instructions.
• Adjust other review settings.

Getting Help

• Contact our support team for questions or feedback.
• Visit our documentation for detailed guides and information.
• Keep in touch with the Sourcery team by following us on X/Twitter, LinkedIn or GitHub.

[cloudflare-workers-and-pages[bot]]

Deploying tux with    Cloudflare Pages

Latest commit:	e9ff44e
Status:	✅  Deploy successful!
Preview URL:	https://2ba7fdc8.tux-afh.pages.dev
Branch Preview URL:	https://sqlmodel.tux-afh.pages.dev

View logs

[trunk-io[bot]]

⏱️ 2m total CI duration on this PR

Job	Cumulative Duration	Recent Runs
Analyze (python)	1m	🟩
Analyze (actions)	48s	🟩

_{settings ⋅ feedback ⋅ docs ⋅ learn more about trunk.io}

[github-actions[bot]]

Dependency Review

The following issues were found:

• ✅ 0 vulnerable package(s)
• ✅ 0 package(s) with incompatible licenses
• ✅ 0 package(s) with invalid SPDX license definitions
• ⚠️ 3 package(s) with unknown licenses.

See the Details below.

License Issues

poetry.lock

Package	Version	License	Issue Type
jaraco-functools	4.2.1	Null	Unknown License
greenlet	3.2.3	Null	Unknown License
pymdown-extensions	10.16	Null	Unknown License

OpenSSF Scorecard

Scorecard details

Package	Version	Score	Details
pip/alembic	1.16.2	:green_circle: 6.3	Details Check Score Reason Packaging :warning: -1 packaging workflow not detected Dangerous-Workflow :green_circle: 10 no dangerous workflow patterns detected Security-Policy :green_circle: 10 security policy file detected Binary-Artifacts :green_circle: 10 no binaries found in the repo Maintained :green_circle: 10 30 commit(s) and 21 issue activity found in the last 90 days -- score normalized to 10 Code-Review :warning: 0 Found 1/30 approved changesets -- score normalized to 0 Token-Permissions :green_circle: 10 GitHub workflow tokens follow principle of least privilege Pinned-Dependencies :warning: 0 dependency not pinned by hash detected -- score normalized to 0 CII-Best-Practices :warning: 0 no effort to earn an OpenSSF best practices badge detected License :green_circle: 10 license file detected Fuzzing :warning: 0 project is not fuzzed Signed-Releases :warning: -1 no releases found Branch-Protection :warning: -1 internal error: error during branchesHandler.setup: internal error: githubv4.Query: Resource not accessible by integration Vulnerabilities :green_circle: 8 2 existing vulnerabilities detected SAST :warning: 0 SAST tool is not run on all commits -- score normalized to 0
pip/asyncpg	0.30.0	:green_circle: 5	Details Check Score Reason Dangerous-Workflow :green_circle: 10 no dangerous workflow patterns detected Code-Review :green_circle: 6 Found 18/29 approved changesets -- score normalized to 6 Maintained :warning: 2 2 commit(s) and 1 issue activity found in the last 90 days -- score normalized to 2 Binary-Artifacts :green_circle: 10 no binaries found in the repo Token-Permissions :warning: 0 detected GitHub workflow tokens with excessive permissions Pinned-Dependencies :warning: 0 dependency not pinned by hash detected -- score normalized to 0 CII-Best-Practices :warning: 0 no effort to earn an OpenSSF best practices badge detected Security-Policy :warning: 0 security policy file not detected Fuzzing :warning: 0 project is not fuzzed License :green_circle: 10 license file detected Vulnerabilities :green_circle: 10 0 existing vulnerabilities detected Packaging :green_circle: 10 packaging workflow detected Signed-Releases :warning: -1 no releases found Branch-Protection :warning: -1 internal error: error during branchesHandler.setup: internal error: githubv4.Query: Resource not accessible by integration SAST :warning: 0 SAST tool is not run on all commits -- score normalized to 0
pip/backrefs	5.9	:green_circle: 4.8	Details Check Score Reason Maintained :warning: 0 1 commit(s) and 0 issue activity found in the last 90 days -- score normalized to 0 Code-Review :warning: 0 Found 0/29 approved changesets -- score normalized to 0 Security-Policy :green_circle: 10 security policy file detected Dangerous-Workflow :green_circle: 10 no dangerous workflow patterns detected Binary-Artifacts :green_circle: 10 no binaries found in the repo Token-Permissions :warning: 0 detected GitHub workflow tokens with excessive permissions CII-Best-Practices :warning: 0 no effort to earn an OpenSSF best practices badge detected Pinned-Dependencies :warning: 0 dependency not pinned by hash detected -- score normalized to 0 Fuzzing :warning: 0 project is not fuzzed License :green_circle: 10 license file detected Vulnerabilities :green_circle: 10 0 existing vulnerabilities detected Packaging :green_circle: 10 packaging workflow detected Signed-Releases :warning: -1 no releases found Branch-Protection :warning: -1 internal error: error during branchesHandler.setup: internal error: githubv4.Query: Resource not accessible by integration SAST :warning: 0 SAST tool is not run on all commits -- score normalized to 0
pip/greenlet	3.2.3	:green_circle: 4.8	Details Check Score Reason Token-Permissions :warning: 0 detected GitHub workflow tokens with excessive permissions Dangerous-Workflow :green_circle: 10 no dangerous workflow patterns detected Code-Review :warning: 0 Found 2/22 approved changesets -- score normalized to 0 Maintained :green_circle: 10 30 commit(s) and 0 issue activity found in the last 90 days -- score normalized to 10 Binary-Artifacts :green_circle: 10 no binaries found in the repo CII-Best-Practices :warning: 0 no effort to earn an OpenSSF best practices badge detected Pinned-Dependencies :warning: 0 dependency not pinned by hash detected -- score normalized to 0 Security-Policy :warning: 0 security policy file not detected Fuzzing :warning: 0 project is not fuzzed License :green_circle: 9 license file detected Signed-Releases :warning: -1 no releases found Branch-Protection :warning: 0 branch protection not enabled on development/release branches Packaging :green_circle: 10 packaging workflow detected Vulnerabilities :green_circle: 5 5 existing vulnerabilities detected SAST :green_circle: 10 SAST tool is run on all commits
pip/jaraco-functools	4.2.1	:green_circle: 5.6	Details Check Score Reason Code-Review :warning: 0 Found 1/28 approved changesets -- score normalized to 0 Binary-Artifacts :green_circle: 10 no binaries found in the repo Dangerous-Workflow :green_circle: 10 no dangerous workflow patterns detected Security-Policy :green_circle: 10 security policy file detected Maintained :green_circle: 10 13 commit(s) and 1 issue activity found in the last 90 days -- score normalized to 10 Token-Permissions :green_circle: 10 GitHub workflow tokens follow principle of least privilege Packaging :warning: -1 packaging workflow not detected Pinned-Dependencies :warning: 0 dependency not pinned by hash detected -- score normalized to 0 CII-Best-Practices :warning: 0 no effort to earn an OpenSSF best practices badge detected Fuzzing :warning: 0 project is not fuzzed Vulnerabilities :green_circle: 10 0 existing vulnerabilities detected License :warning: 0 license file not detected Signed-Releases :warning: -1 no releases found Branch-Protection :warning: 0 branch protection not enabled on development/release branches SAST :warning: 0 SAST tool is not run on all commits -- score normalized to 0
pip/mako	1.3.10	:green_circle: 4.5	Details Check Score Reason Dangerous-Workflow :green_circle: 10 no dangerous workflow patterns detected Code-Review :warning: 0 Found 0/30 approved changesets -- score normalized to 0 Maintained :green_circle: 5 3 commit(s) and 4 issue activity found in the last 90 days -- score normalized to 5 Binary-Artifacts :green_circle: 10 no binaries found in the repo SAST :warning: 0 no SAST tool detected Packaging :warning: -1 packaging workflow not detected Token-Permissions :warning: 0 detected GitHub workflow tokens with excessive permissions Pinned-Dependencies :warning: 0 dependency not pinned by hash detected -- score normalized to 0 CII-Best-Practices :warning: 0 no effort to earn an OpenSSF best practices badge detected Security-Policy :warning: 0 security policy file not detected Vulnerabilities :green_circle: 10 0 existing vulnerabilities detected License :green_circle: 10 license file detected Fuzzing :green_circle: 10 project is fuzzed Signed-Releases :warning: -1 no releases found Branch-Protection :warning: 0 branch protection not enabled on development/release branches
pip/markdown	3.8.2	:green_circle: 5.7	Details Check Score Reason Maintained :green_circle: 10 12 commit(s) and 10 issue activity found in the last 90 days -- score normalized to 10 Code-Review :green_circle: 7 Found 19/27 approved changesets -- score normalized to 7 Dangerous-Workflow :green_circle: 10 no dangerous workflow patterns detected CII-Best-Practices :warning: 0 no effort to earn an OpenSSF best practices badge detected Token-Permissions :warning: 0 detected GitHub workflow tokens with excessive permissions Binary-Artifacts :green_circle: 10 no binaries found in the repo Security-Policy :warning: 0 security policy file not detected Vulnerabilities :green_circle: 10 0 existing vulnerabilities detected Pinned-Dependencies :warning: 0 dependency not pinned by hash detected -- score normalized to 0 Fuzzing :green_circle: 10 project is fuzzed License :green_circle: 10 license file detected Packaging :warning: -1 packaging workflow not detected Signed-Releases :warning: 0 Project has not signed or included provenance with any releases. Branch-Protection :warning: -1 internal error: error during branchesHandler.setup: internal error: githubv4.Query: Resource not accessible by integration SAST :warning: 0 SAST tool is not run on all commits -- score normalized to 0
pip/pygments	2.19.2	:green_circle: 5.5	Details Check Score Reason Maintained :green_circle: 3 3 commit(s) and 1 issue activity found in the last 90 days -- score normalized to 3 Code-Review :warning: 2 Found 7/26 approved changesets -- score normalized to 2 Dangerous-Workflow :green_circle: 10 no dangerous workflow patterns detected CII-Best-Practices :warning: 0 no effort to earn an OpenSSF best practices badge detected Token-Permissions :green_circle: 10 GitHub workflow tokens follow principle of least privilege Packaging :warning: -1 packaging workflow not detected Security-Policy :warning: 0 security policy file not detected License :green_circle: 10 license file detected Signed-Releases :warning: -1 no releases found Branch-Protection :warning: 0 branch protection not enabled on development/release branches Vulnerabilities :green_circle: 10 0 existing vulnerabilities detected Fuzzing :green_circle: 10 project is fuzzed Binary-Artifacts :green_circle: 10 no binaries found in the repo Pinned-Dependencies :warning: 0 dependency not pinned by hash detected -- score normalized to 0 SAST :warning: 0 SAST tool is not run on all commits -- score normalized to 0
pip/pymdown-extensions	10.16	:green_circle: 4.9	Details Check Score Reason Code-Review :warning: 0 Found 2/30 approved changesets -- score normalized to 0 Dangerous-Workflow :green_circle: 10 no dangerous workflow patterns detected Security-Policy :green_circle: 10 security policy file detected Maintained :green_circle: 10 19 commit(s) and 6 issue activity found in the last 90 days -- score normalized to 10 Token-Permissions :warning: 0 detected GitHub workflow tokens with excessive permissions CII-Best-Practices :warning: 0 no effort to earn an OpenSSF best practices badge detected Binary-Artifacts :green_circle: 10 no binaries found in the repo License :green_circle: 9 license file detected Signed-Releases :warning: -1 no releases found Branch-Protection :warning: -1 internal error: error during branchesHandler.setup: internal error: githubv4.Query: Resource not accessible by integration Fuzzing :warning: 0 project is not fuzzed Pinned-Dependencies :warning: 0 dependency not pinned by hash detected -- score normalized to 0 Packaging :green_circle: 10 packaging workflow detected SAST :warning: 0 SAST tool is not run on all commits -- score normalized to 0 Vulnerabilities :warning: 1 9 existing vulnerabilities detected
pip/sqlalchemy	2.0.41	:green_circle: 5.6	Details Check Score Reason Code-Review :warning: 0 Found 2/29 approved changesets -- score normalized to 0 Security-Policy :green_circle: 10 security policy file detected Maintained :green_circle: 10 30 commit(s) and 18 issue activity found in the last 90 days -- score normalized to 10 Token-Permissions :warning: 0 detected GitHub workflow tokens with excessive permissions Dangerous-Workflow :green_circle: 10 no dangerous workflow patterns detected CII-Best-Practices :warning: 0 no effort to earn an OpenSSF best practices badge detected Packaging :warning: -1 packaging workflow not detected License :green_circle: 10 license file detected Vulnerabilities :green_circle: 10 0 existing vulnerabilities detected Binary-Artifacts :green_circle: 10 no binaries found in the repo Branch-Protection :warning: -1 internal error: error during branchesHandler.setup: internal error: githubv4.Query: Resource not accessible by integration Pinned-Dependencies :warning: 0 dependency not pinned by hash detected -- score normalized to 0 Fuzzing :green_circle: 10 project is fuzzed SAST :warning: 0 SAST tool is not run on all commits -- score normalized to 0 Signed-Releases :warning: 0 Project has not signed or included provenance with any releases.
pip/sqlmodel	0.0.24	Unknown	Unknown
pip/alembic	^ 1.16.2	:green_circle: 6.3	Details Check Score Reason Packaging :warning: -1 packaging workflow not detected Dangerous-Workflow :green_circle: 10 no dangerous workflow patterns detected Security-Policy :green_circle: 10 security policy file detected Binary-Artifacts :green_circle: 10 no binaries found in the repo Maintained :green_circle: 10 30 commit(s) and 21 issue activity found in the last 90 days -- score normalized to 10 Code-Review :warning: 0 Found 1/30 approved changesets -- score normalized to 0 Token-Permissions :green_circle: 10 GitHub workflow tokens follow principle of least privilege Pinned-Dependencies :warning: 0 dependency not pinned by hash detected -- score normalized to 0 CII-Best-Practices :warning: 0 no effort to earn an OpenSSF best practices badge detected License :green_circle: 10 license file detected Fuzzing :warning: 0 project is not fuzzed Signed-Releases :warning: -1 no releases found Branch-Protection :warning: -1 internal error: error during branchesHandler.setup: internal error: githubv4.Query: Resource not accessible by integration Vulnerabilities :green_circle: 8 2 existing vulnerabilities detected SAST :warning: 0 SAST tool is not run on all commits -- score normalized to 0
pip/asyncpg	^ 0.30.0	:green_circle: 5	Details Check Score Reason Dangerous-Workflow :green_circle: 10 no dangerous workflow patterns detected Code-Review :green_circle: 6 Found 18/29 approved changesets -- score normalized to 6 Maintained :warning: 2 2 commit(s) and 1 issue activity found in the last 90 days -- score normalized to 2 Binary-Artifacts :green_circle: 10 no binaries found in the repo Token-Permissions :warning: 0 detected GitHub workflow tokens with excessive permissions Pinned-Dependencies :warning: 0 dependency not pinned by hash detected -- score normalized to 0 CII-Best-Practices :warning: 0 no effort to earn an OpenSSF best practices badge detected Security-Policy :warning: 0 security policy file not detected Fuzzing :warning: 0 project is not fuzzed License :green_circle: 10 license file detected Vulnerabilities :green_circle: 10 0 existing vulnerabilities detected Packaging :green_circle: 10 packaging workflow detected Signed-Releases :warning: -1 no releases found Branch-Protection :warning: -1 internal error: error during branchesHandler.setup: internal error: githubv4.Query: Resource not accessible by integration SAST :warning: 0 SAST tool is not run on all commits -- score normalized to 0
pip/sqlmodel	^ 0.0.24	Unknown	Unknown

Scanned Files

• poetry.lock
• pyproject.toml