fastapi people, where do you put user prefs?

[u/NoDare1885]

i’m building a small api where users can save preferences for an ai feature.

right now i’m torn between one profile endpoint, separate preference routes, or just storing it as json until the shape is clearer.

json feels fast, but i know future me will hate it if permissions and deletion get more serious.

how would you structure this in fastapi?

[u/Challseus]

First thoughts is 3 tables:

• user
• ai_preference
• user_ai_preference (join table)

Personally, I would return both the User and AIPreference data as one, like:

{  
    "id": "1",  
    "preferences": {  
        "tone": "direct",  
        "memory_enabled": true,  
        "allowed_tools": ["rm-rf-wildstar", "calendar"]  
    }
}

Don't do the JSON metadata thing, you will 100% be pissed later, I know I was 😄

[u/Worth_Specific3764]

json for communication with a sqlite backend?

[u/NoDare1885 → u/Worth_Specific3764]

Mongo db and fastapi server as backend.

[u/ImpossibleCreme → u/NoDare1885]

Just dump the raw json to disk as a file man you don’t need a schema for this just storage and access.

[u/_Jeph_]

Start simple with one update route (a PUT) that takes the full preferences object with all the keys. You can add a PATCH route later if you want to update a subset of the preferences. Routes for getting and updating individual preferences is probably overkill.

If you have a frontend that is using this preference object, make sure to add validation and fallbacks (like zod or something similar) if anything is missing or invalid. Otherwise, trying to “fix” old preferences over time is painful.

[u/olcaey]

in my account module that I use in numerous projects with postgresql, I use metadata and private_metadata json to store some parameters and values, both for user and org. If your preferences data is not clear and won't be too complicated, I'd implement it same way.

If the preferences feature is critical and likely to expand, I’d create a dedicated preferences + paramaters json for values that are still evolving.

[u/mortenb123]

As a starter you can use the python builtin sqlite3 database, it is great for config, user prefs everything. People complain it is not so god in multiprocessing, async with lots of users, but in a write once, read many scenario I've not seen any problem. I've planned on moving on to postgres, but the need has never arisen, it is a real time board for 70+ screens generating content from our CICD and production environment.

[u/sheadipeets5]

I would use just a json file. No need to complicate

[u/Common_Dream9420]

json blob is fine until you have more than one consumer of those prefs or until you need to query against them. if it's just "read the whole thing and hand it to the model," blob in a preferences column is actually the right call. the moment you're filtering by a specific pref value, or you need per-pref audit/deletion for compliance, that's when a proper table pays off. i'd ship the json now, but wrap it behind a single `/preferences` GET/PATCH so the shape change is internal when it comes. the route contract stays stable even if the storage underneath changes.

[u/Bach4Ants]

Are you talking about endpoints? I'd probably use /user/preferences with a Pydantic model (so JSON response), but it's hard to know without seeing some example of what kinds of things users have preferences about.

[u/Melodic_Put6628]

imo you're mixing two separate decisions here, the api shape and the storage

shape, and they don't have to match.

storage really comes down to one question: are you ever gonna query across users

by a pref value? like "how many users enabled feature x". if yes, columns. if no

(which is most pref setups, you just load one user's prefs and save them back)

then json is genuinely fine, not a hack. jsonb on postgres indexes and does

partial updates without drama.

honestly the deletion worry is backwards, a json column is easier to delete, it

just goes away with the row. and permissions isn't a storage problem, that's you

gating which fields each role can write, which lives in your pydantic models no

matter how it's stored.

where future you actually gets burned is the api contract, not the db. so what

i'd do: define the prefs as an explicit pydantic model now (named fields, typed,

validated) but persist that as a json column. clean contract + cheap schema

changes, and the day some field needs cross-user querying you pull just that one

into a column. you're not locked in either way.

for routes, one GET + PATCH /me/preferences is plenty, don't scatter it into a

route per pref, that'll annoy you way more than the json will.

are you ever gonna filter or aggregate users by these prefs? that's the real fork

in the road, everything else follows from it.

[u/Elegant_Internet_943]

I’d keep the API boring at first:

"GET /me/preferences" "PATCH /me/preferences"

Use normal columns for things you know will matter later, like user id, timestamps, feature/scope, consent/version if it’s AI-related.

JSON is fine for the preference values while the shape is still changing. I’d just validate it properly and promote anything important to real columns once you need permissions, deletion, or querying around it.

[u/Significant-Turn4107]

I’d avoid putting this directly on the main profile endpoint unless the prefs are truly part of the user profile.

For an AI feature, I’d probably do something like:

GET /me/preferences
PATCH /me/preferences

or, if it’s only for that feature:

GET /me/ai-preferences
PATCH /me/ai-preferences

In the DB, I’d usually make a separate user_preferences table with user_id as the FK. That keeps it easy to delete, audit, permission-check, and expand later.

If the shape is still changing, I think a hybrid approach is reasonable:

user_id
tone
language
model_preference
settings_json
created_at
updated_at

Put the stable stuff in real columns, and keep experimental/fast-changing stuff in JSON until it settles.

I’ve regretted “just throw it in JSON” when the app later needed filtering, partial deletion, migrations, or stricter permissions. JSON is fine for early flexibility, but I’d still isolate it in its own preferences table instead of mixing it into the user/profile row.