TradeTalent: Designing for Data Integrity

TradeTalent ERD Schema

Context

I worked as the database engineer on TradeTalent, a platform where students trade skills. Think of it as a barter economy for knowledge — "I'll teach you React if you teach me Graphic Design." The idea came from a real problem: students have skills they don't know are valuable, and they want to learn skills they can't afford to pay for.

My job was to design the relational database that would hold all of it together.

Problem / Product Goal

TradeTalent isn't a simple marketplace. On Amazon, you buy a product with money. The transaction is straightforward — payment goes through, product ships, done. TradeTalent handles direct value exchange. No currency, no standardized pricing. Just two people agreeing that their skills are worth roughly the same thing.

This creates two hard database problems:

The skill graph. A user can have many skills, and a skill can be offered by many users. That's a many-to-many relationship, but it's not flat — skills can be nested (React is a subset of Frontend Development, which is a subset of Web Development), and the hierarchy matters for discovery.

The trade state machine. Trades aren't a single event. They're proposed, negotiated, accepted, completed. Sometimes they're disputed. Sometimes one party drops out. Each state has different rules about what data can change, who can change it, and what cascading effects it triggers (reputation scores, skill endorsements, availability blocking).

If either of these systems fails — if the skill graph returns bad results, or a trade transitions to an invalid state — the platform loses trust immediately.

Thought Process

I started with the obvious approach: handle business logic in the application layer. Let the API server enforce the rules, validate state transitions, manage the skill hierarchy. That's how most modern applications work, and for good reason — it's flexible and easy to change.

But I kept coming back to a concern: what happens when two API servers process a trade update at the same time? Or when a race condition lets a trade skip a required state? Application-level enforcement is vulnerable to concurrency bugs, and in a marketplace, those bugs mean real disputes.

I realized the database needed to be an active participant in the business logic, not just a passive storage layer. If the schema itself enforced the rules — with constraints, triggers, and stored procedures — then no amount of application-level bugs could corrupt the data. The database becomes the source of truth in the truest sense.

This was the shift in thinking. Instead of "how do I model this data?", the question became "what invariants must never be violated?" The answers drove the schema design:

A user must never trade a skill with themselves.
A trade must follow a defined state machine — you can't go from "proposed" to "completed" without "accepted."
Reputation scores must be derived from completed trades, not manually set.
A skill offered in an active trade must be marked as unavailable for other trades.

Every constraint, trigger, and stored procedure I wrote was an answer to one of those invariants.

Solution

The database design for TradeTalent leans heavily into the relational model. Business rules are encoded in the schema itself.

Key schema decisions:

Complex SQL constraints prevent invalid states at the database level. A CHECK constraint ensures a user can't propose a trade with themselves. A UNIQUE constraint on (skill_id, user_id, status) prevents double-booking a skill that's already in an active trade.
Stored procedures automate state transitions. Moving a trade from "proposed" to "accepted" isn't just an UPDATE statement — it triggers availability recalculations, notification insertions, and timeline events. Wrapping that in a procedure guarantees atomicity.
Optimized views serve the discovery layer. A "Skill Discovery View" joins users, skills, and availability into a single query-friendly structure. Even as the dataset grows, the frontend gets fast reads without complex application-level joins.
The skill hierarchy uses an adjacency list with recursive CTEs for traversal. Querying "all sub-skills of Frontend Development" is a single recursive query, not an application-level loop.

The result: the API layer is thin. It validates inputs and handles auth, then delegates complex operations to the database. The data stays consistent even under concurrent access, because the invariants are baked into the schema.

Takeaways

Database design is product design. How your data relates determines how your users interact. If the skill graph is messy, discovery is broken. If the trade state machine is leaky, trust evaporates. The ORM-and-forget approach works for CRUD apps, but marketplaces demand schema-level rigor.

Constraints as code. Every business rule I could encode in the schema saved me from a debugging session later. Application-level enforcement is necessary, but it's not sufficient. The database should never allow an invalid state, regardless of what the application does.

Recursive CTEs are underrated. The skill hierarchy traversal is elegant and fast. Most developers reach for an application-level tree or a nested set model. Recursive CTEs are simpler, more maintainable, and just as performant for tree depths of 3-5 levels (which covers most skill hierarchies).

What I'd do differently: I'd add more auditing upfront. The trade state machine would benefit from an event-sourced log of every state transition — who changed what, when, and from which state to which. The current design stores only the current state, which makes debugging race conditions harder than it needs to be.