A Cryptocurrency Exchange in PostgreSQL
A relational schema and REST API for a cryptocurrency exchange — 22 tables
covering users, custody, markets, and three distinct trade paths (order book,
OTC, and peer-to-peer). Built for Database Design (40384) at Sharif University
of Technology, spring 2024.
The data model
The design separates custody from trading, which is what makes the
schema more than a CRUD exercise:
- Identity & custody —
Users, BankAccounts, RialWallets, EWallets.
Fiat and crypto balances are held in separate wallet tables because they
settle through completely different rails.
- Assets —
Currency generalises over CryptoCurrencies and
FiatCurrencies; BlockchainNetwork and CryptoBlockchain model the fact
that one asset can exist on several chains.
- Trading —
Markets defines tradable pairs. Orders feeds the
OrderBook; OTCOrders and the P2P tables are separate paths with different
matching semantics. Trades records fills.
- Money movement —
Transactions with BankTransactions as the fiat
specialisation.
Three indexes in Postgresql/1_2_Indexing.sql target the hot paths (order-book
lookups and per-user order history).
Layout
Postgresql/
1_Creat_Tables.sql # schema: 22 tables, keys, constraints
1_2_Indexing.sql # indexes on the hot query paths
2_Seed_Tables.sql # seeding entry point
seed.sql # full synthetic dataset
seed_shorter_version.sql # smaller dataset for quick loads
seed_generator_faker.py # generates the seed data
3_Query1.sql .. 3_Query7.sql # analytical queries (+ .csv results)
_init.sql # bootstrap
API/
api.py # Flask REST API over the schema
*.sql # the queries backing individual endpoints
schema.png / schema.svg # ER diagram
Analytical queries
Postgresql/3_Query*.sql are the reporting workload the schema was designed
for, with the returned rows committed alongside as .csv. The two substantial
ones are 3_Query1.sql (150 lines) and 3_Query5.sql (376 lines), which build
up trading activity through several layers of CTEs.
Setup
createdb crypto
psql -d crypto -f Postgresql/1_Creat_Tables.sql
psql -d crypto -f Postgresql/1_2_Indexing.sql
psql -d crypto -f Postgresql/seed_shorter_version.sql # or seed.sql for the full set
Then the API:
pip install flask psycopg2-binary
export DB_NAME=crypto DB_USER=postgres DB_PASS=yourpassword DB_PORT=5432
python API/api.py
Connection settings are read from the environment (DB_HOST, DB_PORT,
DB_NAME, DB_USER, DB_PASS) and fall back to local defaults.
API
12 endpoints over the schema:
| Method |
Path |
Purpose |
| GET |
/api/markets |
tradable pairs |
| GET |
/api/otc/prices |
current OTC prices |
| GET · POST · DELETE |
/api/otc/orders |
OTC order lifecycle |
| GET · POST · DELETE |
/api/p2p/orders |
P2P order lifecycle, with status filtering |
| GET |
/api/wallets |
balances per user |
| GET |
/api/transactions/history |
transaction history |
| POST |
/api/transactions |
record a transaction |
| GET |
/health |
liveness |
Seed data
All seed data is synthetic, generated by seed_generator_faker.py using
Faker. Names, emails, national codes, phone
numbers and addresses are fabricated — example.org addresses and placeholder
passwords throughout. No real personal data is present.
Credits
Built by @erfnzdeh and
@Amirjaz. Per-file authorship is in the git
history.
License
MIT.
评论