Database Schema

Relevant source files

The following files were used as context for generating this wiki page:

• drizzle/schema.ts
• drizzle/relations.ts
• server/db.ts
• drizzle/meta/0001_snapshot.json
• drizzle/0001_sturdy_santa_claus.sql
• README.md

The database schema for the Driveway Estimator Pro project is designed to support a robust workflow for driveway measurement, material estimation, and contractor collaboration. It utilizes a MySQL/TiDB backend managed through the Drizzle ORM, ensuring type safety and structured data migrations.

The schema is centered around four primary entities: users, projects, project shares, and material prices. This structure enables the application to persist user profile information, store complex driveway measurement data (including coordinate arrays and S3 image references), manage public sharing of estimates via unique tokens, and cache regional material costs to optimize performance.

Sources: README.md:38-40, drizzle/schema.ts:1-12

Data Model Architecture

The database follows a relational model with foreign key constraints to maintain referential integrity, particularly between users and their associated projects, and between projects and their shared links.

Entity Relationship Diagram

The following diagram illustrates the relationships between the core tables, highlighting how user data connects to project measurements and how those projects are extended into the sharing system.

Core Tables

Users Table

The users table manages authentication and authorization. It utilizes a unique openId derived from the Manus OAuth flow to identify users across sessions.

Field	Type	Constraints	Description
id	int	PK, Auto-increment	Unique internal identifier.
openId	varchar(64)	Unique, Not Null	OAuth identifier from callback.
name	text	Nullable	User's display name.
email	varchar(320)	Nullable	User's email address.
role	enum('user','admin')	Default 'user'	Access control level.
lastSignedIn	timestamp	Default Now	Tracked for security and activity.

Sources: drizzle/schema.ts:16-29, server/db.ts:32-55

Projects Table

This is the primary data store for driveway estimates. It contains references to images stored in S3 and serializes geometric data (corner points) for the measurement UI.

Field	Type	Description
userId	int	Foreign key to the owner in the users table.
photoUrl	text	S3 storage path for the original driveway photo.
photoKey	text	Unique S3 key for image management.
squareFeet	int	Calculated area of the driveway surface.
cornerPoints	text	JSON-serialized array of {x, y} coordinate objects.
selectedMaterial	varchar(50)	Material type (e.g., hotmix, millings, gravel).
totalCost	varchar(50)	Final calculated estimate string.
previewImageUrl	text	AI-generated visualization of the new material.

Sources: drizzle/schema.ts:37-62, drizzle/meta/0001_snapshot.json:151-240

Project Shares Table

Facilitates the "Contractor Sharing" feature by mapping unique, non-guessable tokens to specific projects.

export const projectShares = mysqlTable("projectShares", {
  id: int("id").autoincrement().primaryKey(),
  projectId: int("projectId")
    .notNull()
    .references(() => projects.id),
  shareToken: varchar("shareToken", { length: 64 }).notNull().unique(),
  contractorEmail: varchar("contractorEmail", { length: 320 }),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  expiresAt: timestamp("expiresAt"),
  viewCount: int("viewCount").default(0),
});

Sources: drizzle/schema.ts:70-81, server/db.ts:127-135

Material Prices Table

This table acts as a regional cache for material pricing data, indexed by ZIP code. It reduces the need for expensive real-time API lookups for every estimate.

Field	Type	Description
zipCode	varchar(10)	Regional identifier for localized pricing.
material	varchar(50)	The specific asphalt or gravel type.
pricePerTon	varchar(50)	Current market price per ton in that region.
pricePerSquareFoot	varchar(50)	Converted unit price for area-based estimates.
lastUpdated	timestamp	Tracking for cache invalidation.

Sources: drizzle/schema.ts:89-100, server/db.ts:153-172

Database Operations and Logic

The application logic for interacting with these tables is centralized in server/db.ts, which utilizes Drizzle's query builder to perform type-safe operations.

User Upsert Flow

When a user logs in, the system performs an "upsert" operation to either create a new record or update the existing one based on the openId.

Pricing Cache Logic

The system prioritizes cached data from the materialPrices table before falling back to mock data or external suppliers.

1. Lookup: The service queries materialPrices using zipCode and material type.
2. Validation: It checks if expiresAt is in the future.
3. Update: If data is missing or expired, it fetches new pricing and uses onDuplicateKeyUpdate to refresh the cache.

Sources: server/db.ts:153-172, server/services/pricing.ts:40-75

Summary

The Database Schema provides a specialized structure for handling geospatial data (corners), binary assets (S3 keys), and localized financial data. By separating core project data from sharing tokens and pricing caches, the architecture remains scalable and performant for both homeowners and contractors.

Sources: README.md:126-172, drizzle/schema.ts:1-106