🗄️ LafaekStreet Database Schema
This database is the core of the LafaekStreet platform, designed to handle citizen reports, AI-powered infrastructure analysis, and blockchain-verified transactions. It is optimized for PostgreSQL 15+ with PostGIS for geospatial data and pgvector for AI similarity search.
📊 Database Overview
The schema is organized into several functional areas to support the entire lifecycle of a road infrastructure report:
1. User & Authentication
users: Stores citizen accounts with support for both Email/Password and Google OAuth 2.0. It tracks user roles (Citizen, Admin, Moderator, Government Official) and activity statistics.user_sessions: Manages active login sessions and device information for push notifications (FCM).otp_verifications: Handles 6-digit one-time passwords for secure registration and password resets.two_factor_codes&two_factor_attempts: Provides an additional layer of security for government and administrative access.
2. Infrastructure Reporting
reports: The central table for all road issues. It stores the issue type (pothole, flood, lighting, etc.), severity, and current status (Pending, In Progress, Fixed).locations: Uses PostGIS to store precise GPS coordinates and address components (District, Municipality).report_images: Stores links to evidence photos uploaded by citizens.report_status_history: A full audit trail of every status change a report undergoes.report_comments: Enables communication between citizens and government agencies.
3. AI & Vector Search
ai_analysis_logs: Detailed logs of AI-processed reports, including damage detection, severity assessment, and repair recommendations.report_embeddings: Stores 1024-dimensional vector embeddings (optimized for Amazon Nova 2). This allows for advanced features like finding duplicate reports or searching for issues using images instead of text.
4. Governance & Blockchain
government_agencies: Registry of departments (Municipal, National, Contractors) responsible for fixing reported issues.hedera_transactions: Records of report data anchored to the Hedera Hashgraph for immutable, public verification.citizen_feedback: Collects satisfaction ratings once a report is marked as "Fixed," ensuring government accountability.
5. System & Analytics
notifications: Real-time alerts for users when their reports are updated.app_settings: Global configuration for the platform (e.g., AI thresholds, Hedera Topic IDs).activity_logs: A comprehensive audit trail of all system-wide actions.
👥 Database Users & Roles
For detailed information on how to manage different database users and their specific permissions, please refer to the user/ directory.
- Government Portal User: Scripts and documentation for managing government agency access.
- API User: Configuration for the backend API database connection.
- Public Dashboard User: Read-only access setup for public-facing analytics.
🛠️ Technical Features
- Geospatial Intelligence: Built-in functions to find "nearby reports" based on a user's current location using PostGIS.
- Automated Statistics: Database triggers automatically update user badges and municipal dashboards when reports are submitted or resolved.
- Vector Similarity: Optimized for 85% similarity thresholds to detect duplicate reports automatically using AI and
pgvector. - Search Optimized: Uses
pg_trgmandtsvectorfor fast full-text searching across titles and descriptions. - Auditability: Every status change and sensitive action is logged with metadata and timestamps.
📐 Entity-Relationship Diagram (ERD)
🚀 Getting Started
To initialize the database, simply run the schema.sql file in your PostgreSQL environment. It includes all necessary extensions, tables, views, triggers, and initial seed data to get the system running immediately.
sql
-- Example: Initialize the database
psql -d your_database -f schema.sqlKey Requirements
- PostgreSQL 15+
- PostGIS extension
- pgvector extension
- pg_trgm extension