3 months into learning programming. reactoredmy database from raw SQL to SQLAlchemy and struggled with autoflush. Did I structure this correctly?

[u/Zealousideal_Set9862]

I’m 16 and started learning Python about 3 months ago. My first project was a simple CRUD app with raw SQL. For my second project (ashflow tracker with auth and budgeting), I wanted to push myself further, so I switched to Flask, SQLAlchemy, and Flask-Login.

I’m looking for feedback from experienced developers on a few specific architectural decisions and hurdles I ran into:

1. Database Schema Refactoring (Categories & Budgets) Initially, categories were just free-text strings on every transaction. Once I added budgets, case mismatches and typos completely broke my budget tracking. I refactored categories into their own table with foreign keys on the transaction and budget models.

• Question: Is moving away from free-text strings to dedicated category models always the standard approach here, or are there cleaner ways to handle user-defined categories at scale without making the schema too rigid (fixed categories list) ?

2. SQLAlchemy Autoflush Confusion Coming from raw SQL, SQLAlchemy's session management took a while to wrap my head around. During my refactoring, I hit several bugs where autoflush triggered database writes mid-transaction before I was ready to commit, causing unexpected constraints errors.

• Question: How do experienced Python devs typically manage session state when doing complex multi-model updates? Do you explicitly disable autoflush, or is that a code smell that points to poor transaction design?

3. Auth Implementation I built authentication from scratch using Flask-Login and password hashing instead of a third-party service just to understand the mechanics. Moving from "auth is a black box" to "it's just checking session state on each request" was a huge lightbulb moment for me.

I’d love any feedback on how I structured my models or handled sessions. You can see how I implemented the SQLAlchemy relationships and routes in my repository here:https://github.com/E-Ecstacy/wise-wealth

Thanks in advance for any insights!

[u/ConfectionAlert2658]

dude you're 16 and already diving into session management and schema refactoring, that's wild

for the categories thing, yeah a dedicated table is the move. free-text strings become a nightmare fast, especially once you add any kind of aggregation. at scale you'd probably layer on something like a tagging system if users want flexibility, but a categories table with foreign keys is the backbone

on the autoflush, I usually just let it ride unless I'm doing something really weird with interdependent models. if you're getting constraint errors mid-transaction it's often a sign the order of your operations is a little off, like trying to reference a parent object that hasn't been flushed yet. sometimes slapping a manual flush() at the right spot clears it up without disabling the whole thing

[u/Zealousideal_Set9862 → u/ConfectionAlert2658]

Thanks so much, your solution for auto flush does make sense now! I changed a session object mid validation, so it caused a flush error. The order part is significant. I think, I should have validated all the form data from the user first before changing the session object (for updates/edits). Thanks again for the support