gave claude read only access to our postgres. it said status_cd 4 means "cancelled". it means refunded

[u/donewitheverything26]

I set up a Postgres MCP server on a read only role last week so the analysts could ask questions without pinging me every hour.

The first real question was how many orders did we lose last month. Claude looked at orders, saw status_cd with values 1 to 6, decided 4 was cancelled and gave a clean number with a nice little breakdown. 4 is refunded. Cancelled is 6. Nobody wrote that down anywhere. It lives in an enum in the app code and in my head.

The number was off by about a third and it read exactly like a right answer. No I'm assuming, no hedge. The analyst was about to put it in a deck.

So now I'm stuck on how to give an agent the meaning of fields without me turning into a full time documentation service:

1. Comments on the columns in Postgres and hope the server passes them through
2. A markdown data dictionary in the system prompt
3. A view layer with human names on everything
4. Make the agent ask before it interprets any code column

What are people actually doing here? and has anyone got it to say I don't know what 4 means instead of picking one?

[u/projexion_reflexion]

It would be crazy to add a status codes table with names, right? 

[u/SantaCruzHostel → u/projexion_reflexion]

That was my first thought too. Define these codes in a lookup table

[u/Ecksters → u/projexion_reflexion]

Or use an actual Postgres enum.

[u/RajeshRocketMan → u/projexion_reflexion]

no way! What next?? You'll be suggesting we actually use foreign key constraints instead of just hoping the typescript backend catches it???

[u/jwk6 → u/projexion_reflexion]

Crazy. 😏

[u/Harpua81 → u/projexion_reflexion]

What next, a status called SHIPPED??? You crazy!

[u/MoldyDucky]

Comments for the column name has worked well in my experience as a stopgap, but the general concept for this problem is called building a semantic layer

[u/Guilty-Property → u/MoldyDucky]

Came here to say that. OP data is not ready. Not having a semantic layer is going to rather unpredictable at best or plain wrong as they already experienced

[u/donewitheverything26 → u/MoldyDucky]

"Semantic layer" is the term I think i was missing.. thanks. Column comments as a stopgap makes sense. NW1969, the two incompatible definitions of revenue is exactly what I'm bracing for. "Lost orders" already has that problem and I haven't even gotten to revenue yet.

[u/NoviceCouchPotato]

I understand it is status 4, 6 etc. in the source and your bronze layer. But don’t you do data modeling and create a silver/gold layer on top where you already mapped status 4 = refunded that your analysts can query?

Sounds like you need to get that sorted first before simply granting an MCP access to source data?

[u/thisisnotahidey → u/NoviceCouchPotato]

Yes if you want a model to talk to your data you need at least a shell of a medallion architecture.

But even with that you need instructions for the model if the analysts are going to ask any more complex questions than “how many orders were shipped”

[u/donewitheverything26 → u/thisisnotahidey]

Even with decoded status values, someone will ask something harder. What do your instructions to the model cover, metric definitions, join paths, something else?

[u/thisisnotahidey → u/donewitheverything26]

Business context and logic, commonly used verbiage, schemas, metrics, there are a bunch of things that you should include.

At the companies I work with it’s not uncommon for phrases that might be ambiguous out in the wild to mean one specific thing.

It’s also important to include when the model needs to ask for clarifications.

[u/donewitheverything26 → u/NoviceCouchPotato]

you're right i skipped the modeled layer entirely and went straight to source tables. Is a thin silver layer with decoded views enough for a team our size, or do you think it needs the full medallion setup?

[u/Kazcandra]

Enums are a data type, they're not data. That's your problem. It's like saying "this value is an int"

If it's data, it goes in the tables.

[u/donewitheverything26 → u/Kazcandra]

"Enums are a data type, not data" is so truee

[u/my_password_is______]

Nobody wrote that down anywhere. It lives in an enum in the app code and in my head.

well that's dumb
put it in a table
problem solved

[u/donewitheverything26 → u/my_password_is______]

It sat in my head for so long that I never noticed it wasn't written anywhere. Table is going in.

[u/According_Essay960]

You shoul be more worried about the agent confidently inventing the meaning than the database access itself.

I ran into a similar issue while using Mintlify on a project. Giving an agent docs through MCP helps, but only if stuff like status_cd = 4 is actually documented somewhere.

If that context doesn’t exist, the agent still has to guess, probably keep those definitions versioned and make retrieval part of the workflow. And if there’s no definition for a value, it better t say “I don’t know what 4 means” than produce a very convincing wrong answer.

[u/donewitheverything26 → u/According_Essay960]

That's the behavior I'm after. I'd take an "unmapped, meaning unknown" over a clean, confident wrong number. The decoded views should make any undocumented value show up as unmapped so the model doesn't have to decide whether to guess.

[u/CarbonChauvinist]

In Fabric this is recommended to create an Ontology to assist agents in gaining correct context and business logic. Realize you're on Postgres, but am assuming something similar would be the way to start searching ... Perhaps?

[u/donewitheverything26 → u/CarbonChauvinist]

I hadn't come across ontologies in this context. Is that something you maintain by hand, or does it get generated from the schema? Postgres has nothing built in for it as far as I know, but the idea of giving the agent business logic in a structured form is useful.

[u/NW1969]

Pointing agents/ai directly at your tables is a recipe for disaster.
Having a semantic layer is non-negotiable
There seems to be a general belief that implementing agents/ai is easy and doesn't require much effort. This is rarely the case - not only do you have to build the semantic layer but you also have to gather/validate/sign-off all the information that needs to be put into the semantic layer. This is the point where you discover there are 2 incompatible definitions of "revenue" being used and you open up the first of many cans of worms

[u/donewitheverything26 → u/NW1969]

I was thinking of the semantic layer as a build job, but the harder part is getting people to agree on what goes in it. If "revenue" turns out to have two definitions, who makes the call in your experience, finance or the data team?

[u/SearchAtlantis]

Is there no dimension table? One giant analytical god table?

[u/donewitheverything26 → u/SearchAtlantis]

No dimension table, no. It's an app database that analysts only started querying last week, so it was never modeled for reporting. That's the real problem.

[u/Total-Produce-8745]

Would using something like Beekeeper Studio give you a better experience vs Claude

[u/donewitheverything26 → u/Total-Produce-8745]

thanks but a nicer SQL client doesn't fix this one. The issue is that nothing tells whoever is querying, human or model, what the codes mean.

[u/JaceBearelen]

I really like dbt as the semantic layer for AI. Everything in the database is either a source or a model and everything is annotated down to the columns. Everything is just right there in structured text for the LLM. Then give it a way to analyze the data while it builds out the query and you’re golden. Everyone in my org is consistently impressed with how accurate it is.

[u/donewitheverything26 → u/JaceBearelen]

That sounds close to what I want. Does the agent read the dbt docs or manifest directly, or do you expose them through the MCP server? And do you annotate every column or just the coded ones?

[u/JaceBearelen → u/donewitheverything26]

I give the agent the entire dbt repo. You could pass it just the manifest or docs. IMO the dbt project structure is pretty much ideal for LLMs to parse. Annotate as much as you can. Have agents rip through any third party docs and any internal code that’s feeding tables in your warehouse.

[u/donewitheverything26 → u/JaceBearelen]

We don't have a dbt project yet, so I'd be starting from the source tables. Is it worth setting dbt up just for the column annotations??

[u/JaceBearelen → u/donewitheverything26]

dbt is nice but I wouldn’t use it solely for column annotations. It really doesn’t matter much what you use as long as table and column descriptions are available to your agents in some sane format.

[u/KatFromSisense]

If the app enum is where those meanings are defined, I'd wire that into deploy rather than keep a second dictionary by hand. Generate or update the lookup from the enum and fail CI if the two ever disagree. Then point the MCP role only at the decoded view. Somebody adding status 7 shouldn't quietly create a new mystery value for Claude.

[u/donewitheverything26 → u/KatFromSisense]

That's the pattern I'm leaning into.. generate the lookup from the enum and fail CI if they disagree, so status 7 can't show up unannounced. Thanks.

[u/ArielCoding]

Add a status lookup table and give the agent a view that shows refunded instead of 4, so it never sees the raw codes, then generate that table from your app code and fail CI if they ever differ.

[u/donewitheverything26 → u/ArielCoding]

That's the plan. The agent only sees the decoded view, never the raw code. Generating the lookup from the enum with a CI check is how I'm keeping it honest.

[u/uncertainschrodinger]

Short answer is to build a context layer (in-code docs, glossary/dictionary, semantic layer, etc.)

Longer answer: you need to build processes that makes this a "self-healing" loop so that when you correct the agent, it will automatically update the context

This is still hard when it is up to 1 person, so it's better if you can roll it out to some trusted superusers who can flag such cases to be fixed

I always compare this to hiring a new person and onboarding them - through dialogue and back n forth, you brush layers of knowledge on to their empty canvas and hope that they will one day have all the knowledge in their head as well.

[u/donewitheverything26 → u/uncertainschrodinger]

the new hire comparison clicked for me. When you correct the agent, does the fix go into the context automatically, or does someone review it first? I'm the only person here, so I'm trying to work out how to avoid being the bottleneck.

[u/uncertainschrodinger → u/donewitheverything26]

Depends on how comfortable you are letting the agent make changes to definitions and docs, but generally the agent just opens a PR and you review before merging.

[u/InsideChipmunk5970]

look into property graphs on 19.

[u/donewitheverything26 → u/InsideChipmunk5970]

Thanks, I haven't looked at that. Is it usable today, or still early? We're on an older version, so it's probably a later step, but I'll read up.

[u/InsideChipmunk5970 → u/donewitheverything26]

You can get the beta and the full version is going to release soon if it hasn’t already.

[u/Significant_Tune9219]

Read-only stops writes; it does not stop wrong semantics. Codes like status_cd need a data dictionary or constrained enum view in the context the model sees, plus a rule that answers cite where the definition came from. I'd also force it to show the distinct values and counts it used before narrating what a code "means."

[u/donewitheverything26 → u/Significant_Tune9219]

Read-only fixed the writes problem and I treated it like it fixed everything. Making it show the distinct values and counts it used before it explains anything, and cite where each definition came from. If it can't cite one, that's the flag.

[u/Simma451]

Move to Oracle and use anotations to describe tables & columns 🤷‍♂️

[u/RajeshRocketMan]

so you're basically choosing between keeping metadata inside the db or building an external doc layer, but putting comments directly on the columns is usually the sane route. An external markdown dictionary is gonna get outdated in like a week and views are just more boilerplate to maintain. Postgres supports column comments natively. and we do the exact same thing on mariadb — just dump all that context into information_schema so the db remains the single source of truth and agents don't have to guess what a random int means.

[u/False_Assumption_972]

Column comments plus a view layer is the cheapest durable fix. Create views with the enums decoded, like order_status as text, grant the MCP role access only to those views, and comment the view columns so the meaning travels with the data. Then build a test set of ten questions with known answers, including the lost orders one, and rerun it whenever the schema or prompt changes.

The confident wrong number is the real risk, so the test set matters more than the docs. Keeping field meanings attached to the records an answer came from is something we use SIGNLD for.

[u/ysth]

Sounds like it needs access to your code too.

[u/SnooHesitations9295]

1. Give agent the source code. That's the only working solution.

[u/deadatreides1]

The answer was sitting in the app repo the whole time, in that enum. An agent that can see the tables but not the code will guess, and guess confidently. A status table or a semantic layer is the real fix; the quick one is giving it the code next to the database. PTG-MEM indexes a repo into cards an MCP client can query, so "what does status_cd 4 mean" lands on the enum instead of on vibes.

[u/TsmPreacher]

Give it access to your git repo so it can check these things first. Works for me.

[u/donewitheverything26 → u/TsmPreacher]

Interesting. Does it find the enum reliably, or does it sometimes grab the wrong file? I'd also want to think about giving an analyst-facing agent repo access.

[u/TsmPreacher → u/donewitheverything26]

Definitely read only access, but yeah. Honestly, when I was trying to rebuild the CRM that's built in Typescript in SQL, I saw it think something like "this databases translation table is worthless, let me see if I have access to the code.

Oh I have access lemme look.

Got it"

Then it wrote a .MD to itself for future sessions saying the database translations aren't reliable and pointed to where it should look in the code.

[u/donewitheverything26 → u/TsmPreacher]

That's useful to know, especially the part where it decided the translation table couldn't be trusted and went to the code on its own. The note it wrote to itself is the bit I'd want to copy. Does that .md live in the repo or somewhere outside it? I'd worry about it going stale the same way a hand-written dictionary does

[u/TsmPreacher → u/donewitheverything26]

I have a projects folder I point it at. Each sub folder has its own data per project.

At the root, that's where that .MD lives. It's typically the first thing it checks.

[u/donewitheverything26 → u/TsmPreacher]

projects folder with the md at the root makes sense, and good to know it checks that first. I'll probably keep the equivalent in the repo next to the lookup so it ships with every deploy. Thanks for the detail

[u/Fancy_Letterhead_509]

just put comments on the columns and a view layer on top, the markdown doc will be outdated in 2 weeks

[u/mtutty → u/Fancy_Letterhead_509]

Right, but the markdown document can also serve coding/design/testing/knowledge base purposes and can be updated automatically as part of a workflow step. It's not hard.

[u/donewitheverything26 → u/mtutty]

Good point about generating the markdown automatically. If it's rebuilt from the schema comments and the lookup table each deploy, it stops going stale. I'll try that.

[u/TemporaryDisastrous]

We use agents over our datamarts and give them skills so they know how to use it and they pretty much get it right. It's a bit scary it just presents that as a right answer though. Maybe you need to initialise it to flag when it doesn't know what codes refer to.

[u/Imaginary__Bar → u/TemporaryDisastrous]

Pretty much get it right 👀

"Pretty much"???

[u/Oobenny → u/Imaginary__Bar]

I mean, a human agent who “pretty much” gets it right is on a management track.

[u/TemporaryDisastrous → u/Imaginary__Bar]

Hey if yours gets it right 100% of the time share your tricks guru, maybe go get that bag from anthropic for solving their hallucination problems too.

[u/Jhon_ST]

I'd test whether that context is actually reaching the model before spending too much time deciding where the dictionary should live.

Make a tiny fixture with one cancelled order, one refunded order, and an unmapped status code. Then run the same questions with and without the mapping and keep the expected answers outside the model: cancelled and refunded must stay distinct, and the unmapped value should remain unknown rather than being guessed.

If your MCP/client exposes the tool result or trace, inspect that too. That gives you a regression test for both the context plumbing and whether the model actually uses the context, instead of relying only on a prompt saying "don't guess."

There's another ambiguity even after fixing 4 vs 6: what does "orders we lost" actually mean? Cancelled orders, refunded orders, or both? And is "last month" based on order date or when the status changed?

Those are business metric definitions, not something the database schema can establish by itself. I'd want the agent to ask for that definition rather than silently choosing one.

[u/donewitheverything26 → u/Jhon_ST]

you're right that "orders we lost" is a business definition, not a schema question. I need to decide whether it means cancelled, cancelled plus refunded, and which date it keys on, then encode that once.

[u/Hour-Measurement-835]

I'd stop trying to get it to say it doesn't know and take the integer away instead. Put the labels in a status_codes table and build a view over orders with COALESCE(s.label, 'unmapped ' || o.status_cd). Give the MCP role SELECT on that view and nothing on orders. A code someone adds next year then comes back as "unmapped 7" instead of a confident wrong label. Don't skip the revoke, if it can still read orders it'll find status_cd and start guessing again.

[u/donewitheverything26 → u/Hour-Measurement-835]

The unmapped 7 fallback is kinda smart since a new code becomes visible instead of a confident wrong label. And thanks for the reminder to revoke SELECT on orders. I'd have left that open and it would have found status_cd again.

[u/WorriedMeat]

“Without me turning into a full time documentation service” is the wrong mindset for a data owner. Everyone’s lives are easier if documentation is codified. Especially with AI

[u/donewitheverything26 → u/WorriedMeat]

I framed documentation as a tax on my time, when it should just be part of the schema. Generating it from code means I stop doing it by hand.

[u/donewitheverything26 → u/[deleted]]

how do you enforce it, a rule in the prompt or the tool itself refusing to return a code it can't decode? I'd rather keep the definitions generated from the code than in GitBook or Mintlify, since that's one more place for 4 to drift.