Database Schema

14Build uses a well-structured database schema to manage key entities such as users, projects, tenders, SOQs, and escrows. The database is designed to ensure data integrity, scalability, and seamless interaction with the on-chain Stellar blockchain.

1. Overview of Database Tables

The following are the key tables in the 14Build database:

Table Name	Purpose
`users`	Stores user information and role-based data.
`projects`	Holds details about projects and their lifecycle.
`tenders`	Manages tenders, RFPs, RTS submissions, and bids.
`soqs`	Tracks the Schedule of Quantities (SOQ).
`escrows`	Handles escrow details and payment workflows.

Each table is designed with relationships to ensure consistency and efficiency.

2. Users Table

The users table manages all user-related data, including decentralized identities and roles.

Schema

Column	Type	Description
`user_id`	UUID (Primary Key)	Unique identifier for the user.
`did`	VARCHAR(255)	Decentralized Identity (on-chain DID).
`name`	VARCHAR(255)	Full name of the user.
`email`	VARCHAR(255)	User email for contact and authentication.
`role`	ENUM	User role (e.g., Client, Contractor, Engineer).
`kyc_status`	ENUM	KYC verification status (`Verified` / `Pending`).
`created_at`	TIMESTAMP	Account creation date.
`updated_at`	TIMESTAMP	Last update timestamp.

Relationships

Linked to projects table through user_id (project creator).
Referenced in the tenders and escrows for role-specific actions.

3. Projects Table

The projects table holds all information about created projects, including links to tenders, SOQs, and escrows.

Schema

Column	Type	Description
`project_id`	UUID (Primary Key)	Unique identifier for the project.
`created_by`	UUID (Foreign Key)	References the `user_id` of the project creator.
`name`	VARCHAR(255)	Project name.
`description`	TEXT	Detailed description of the project.
`status`	ENUM	Project status (`Draft`, `Tendering`, `Completed`).
`budget`	DECIMAL	Project budget.
`start_date`	DATE	Start date of the project.
`end_date`	DATE	End date of the project.
`created_at`	TIMESTAMP	Project creation date.

Relationships

Linked to the users table (created_by).
Linked to the tenders table through project_id.
Linked to the soqs and escrows tables for project-specific details.

4. Tenders Table

The tenders table manages the tendering workflow, including RFPs, RTS submissions, and bid evaluations.

Schema

Column	Type	Description
`tender_id`	UUID (Primary Key)	Unique identifier for the tender.
`project_id`	UUID (Foreign Key)	References the associated `project_id`.
`created_by`	UUID (Foreign Key)	References the `user_id` of the tender creator.
`status`	ENUM	Tender status (`Open`, `Closed`, `Awarded`).
`rfp_details`	TEXT	Scope, requirements, and deadlines.
`rts_submissions`	JSONB	Array of bid submissions with amounts and details.
`awarded_bid`	JSONB	Selected bid details (e.g., contractor, cost).
`created_at`	TIMESTAMP	Tender creation date.

Relationships

Linked to the projects table (project_id).
Linked to the users table (created_by for tender creator).
Linked to the escrows table when bids are awarded.

5. SOQs Table

The soqs table tracks Schedule of Quantities (SOQ) for each project.

Schema

Column	Type	Description
`soq_id`	UUID (Primary Key)	Unique identifier for the SOQ.
`project_id`	UUID (Foreign Key)	References the associated `project_id`.
`categories`	JSONB	List of SOQ categories (e.g., materials, labor).
`jobs`	JSONB	Detailed jobs with quantities, unit costs, and labor.
`variations`	JSONB	Change requests or updates to the SOQ.
`total_cost`	DECIMAL	Sum of all costs in the SOQ.
`created_at`	TIMESTAMP	SOQ creation date.

Relationships

Linked to the projects table (project_id).
Referenced in the tenders table when generating RFPs.

6. Escrows Table

The escrows table handles all escrow transactions tied to projects and tenders.

Schema

Column	Type	Description
`escrow_id`	UUID (Primary Key)	Unique identifier for the escrow.
`tender_id`	UUID (Foreign Key)	References the awarded `tender_id`.
`milestone_id`	UUID	References project milestone (if applicable).
`client_id`	UUID (Foreign Key)	References the `user_id` of the client.
`contractor_id`	UUID (Foreign Key)	References the `user_id` of the contractor.
`amount`	DECIMAL	Amount locked in the escrow.
`status`	ENUM	Escrow status (`Pending`, `In Progress`, `Released`).
`proof_of_work`	JSONB	Uploaded milestone proofs (off-chain hashes).
`released_at`	TIMESTAMP	Date when funds were released.

Relationships

Linked to the tenders table (tender_id).
Linked to the users table (client_id and contractor_id).

7. Relationships Diagram

Users
  |-- Projects (created_by)
        |-- Tenders (project_id)
              |-- Escrows (tender_id, client_id, contractor_id)
        |-- SOQs (project_id)

8. Summary

The 14Build database schema ensures:

Data Integrity: Relationships and constraints maintain consistency across entities.
Scalability: JSONB and efficient indexing allow for large datasets (SOQs, RTS).
Security: Sensitive data like user identities and escrow details are encrypted.
On-Chain Integration: Critical records link to on-chain entities for transparency.