ReSell_Assignment_03 (1)

1.

R
ReSell.
ASTANA IT
UNIVERSITY
ASSIGNMENT 03 / 2026
ESCROW SAFE
ReSell
DATABASE SYSTEMS
& CYBERSECURITY
Give Your Product a Real Data Layer
AI CHECK ✓
AI legit check • Secure escrow • Trusted resale
Prepared by / Arseniy Kan · DTNP2604
23 OCT 2026

2.

ReSell.
DATA L AY E R / 0 3
From Prototype to Real Infrastructure
Assignment 02 introduced the UI. Now its key flows become persistent data and services.
02 / PERSISTENT SERVICES
AI Engine
Escrow
Database
Legit score
& check results
Safe payment
state machine
Users, items
& order history
Every verified item gains a persistent record and a secure transaction trail.
01 / UI PROTOTYPE
ReSell · Database Systems & Cybersecurity
02 / 08

3.

ReSell.
DATA L AY E R / 0 3
Why PostgreSQL?
ReSell needs consistent payment states, reliable relationships, and powerful SQL queries.
✓ SELECTED
MongoDB
PostgreSQL
InfluxDB
Document model
Relational · ACID · SQL
Time-series model
Flexible schema, but less aligned
with ReSell relational workflows.
ACID for escrow transactions,
PK/FK constraints and SQL JOINs.
Excellent for time-series data,
not orders or escrow payments.
DATA INTEGRITY
PERFORMANCE
SCALABILITY
ACID + foreign keys
Indexes + efficient JOIN
Replication + backups
Target: PostgreSQL · Query demonstration: SQLite
ReSell · Database Systems & Cybersecurity
03 / 08

4.

ReSell.
DATA L AY E R / 0 3
Database Architecture
Four entities connect users, listed items, AI verification and secure transactions.
TA B L E S C H E M A
users
user_id BIGINT PK
email VARCHAR(255)
rating NUMERIC(2,1)
items
E N T I T Y R E L AT I O N S H I P D I AG R A M
users
items
user_id
1:N
PK
verifications
item_id
1:N
PK
email
UNIQUE
seller_id
rating
NUMERIC
price
FK
ai_confidence
orders
verification_id BIGINT PK
item_id BIGINT FK
ai_confidence NUMERIC
BUYER FK
orders
ReSell · Database Systems & Cybersecurity
FK
1:N
verifications
order_id BIGINT PK
buyer_id BIGINT FK
escrow_state VARCHAR
item_id
NUMERIC
item_id BIGINT PK
seller_id BIGINT FK
price NUMERIC(12,2)
verification_id PK
order_id
PK
item_id
FK
buyer_id
FK
escrow_state
PK = primary key
FK = foreign key
1:N = one-to-many
04 / 08

5.

ReSell.
DATA L AY E R / 0 3
SQL in Action: SELECT Query
A working SQLite demo with sample data. The logical model is designed for PostgreSQL.
SQLite Online
/
DATABASE EXPLORER
▾
resell_demo.sqlite
user_id · PK
full_name · TEXT
items
item_id · PK
seller_id · FK
verifications
verification_id · PK
item_id · FK
Executed · 3 rows
query_01.sql
01
SELECT i.item_id, i.title, i.brand,
i.price, v.ai_confidence
02
users
DEMO · 4 TABLES
ReSell demo workspace
03
FROM items AS i
04
JOIN verifications AS v ON v.item_id = i.item_id
05
WHERE v.result = 'Verified'
06
AND v.ai_confidence >= 90
07
ORDER BY v.ai_confidence DESC;
RESULT SET
3 rows · 0 errors
ITEM ID
TITLE
BRAND
PRICE (₸)
AI SCORE
2001
Air Jordan 1 Retro
Nike
87,500
98.6%
2002
New Balance 2002R
New Balance
72,000
97.1%
2003
Yeezy Boost 350
adidas
85,000
95.4%
orders
order_id · PK
item_id · FK
ReSell · Database Systems & Cybersecurity
05 / 08

6.

ReSell.
DATA L AY E R / 0 3
SQL in Action: JOIN + UPDATE
Combining seller trust data and safely updating a transaction’s escrow status.
seller_trust.sql
02 / JOIN
01
SELECT i.title, u.full_name AS seller,
02
u.rating, v.ai_confidence
03
FROM items i JOIN users u ON u.user_id = i.seller_id
04
JOIN verifications v ON v.item_id = i.item_id
05
WHERE v.result = 'Verified'
06
ORDER BY v.ai_confidence DESC;
JOIN RESULT · 3 rows
ITEM
SELLER
RATING
AI
Air Jordan 1 Retro
Alina Karimova
4.9
98.6%
New Balance 2002R
Daniyar Tulegenov
4.8
97.1%
Yeezy Boost 350
Amina Serikova
5.0
95.4%
JOIN brings item details, seller ratings and AI confidence into one result.
03 / UPDATE
release_escrow.sql
01
UPDATE orders
02
SET escrow_state = 'Released_To_Seller',
03
ORDER STATE AFTER UPDATE
ORDER ID
STATUS
ESCROW STATE
4001
Completed
Released_To_Seller
status = 'Completed'
04
WHERE order_id = 4001 AND status = 'Delivered';
05
-- 1 row updated
1 row affected · escrow released after delivery
UPDATE sets the order to Completed and escrow to Released_To_Seller.
ReSell · Database Systems & Cybersecurity
06 / 08

7.

ReSell.
DATA L AY E R / 0 3
ReSell Cybersecurity
Payments, personal data and verification outcomes require defense in depth.
AT TACK SURFACE
SQL Injection
DEFENSE IN DEPTH
Input Validation
Data types, allowlists, limits
Manipulation of query logic
Parameterized SQL Queries
No SQL string concatenation
Unauthorized Access
Exposing accounts and orders
Data Leakage
Authentication and RBAC
Least-privilege access
Patching and Auditing
Fixes, logs and monitoring
PII and payment history exposed
Data Encryption
TLS in transit; at-rest encryption
Security by design: verify permissions and protect every critical operation at every layer.
ReSell · Database Systems & Cybersecurity
07 / 08

8.

ReSell.
DATA L AY E R / 0 3
SQL Injection: Unsafe vs. Safe Code
The same search operation, with two very different approaches to user input.
UNSAFE
/
01
const input = "' OR '1'='1";
02
const sql = "SELECT * FROM items WHERE title = '" + input
+ "' AND status = 'Active'";
03
04
db.query(sql);
SAFE
01
NOT SAFE
string_concat.js
/
// untrusted input changes SQL logic
PROTECTED
parameterized_query.js
01
const sql = "SELECT * FROM items WHERE title = $1 AND status = $2";
02
await db.query(sql, [input, "Active"]);
03
// user data is separated from the SQL statement
Relational Data Model
ReSell · Database Systems & Cybersecurity
02
Executed SQL Queries
03
Security by Design
08 / 08