Database Schema Design
Relevant source files
The following files were used as context for generating this wiki page:
The Poneglyph database is built on PostgreSQL using Drizzle ORM to manage a multi-tenant humanitarian intelligence platform. The schema is designed to support core functionalities including user authentication (via Better-Auth), dataset management, organizational profiles, and volunteer tracking. It utilizes advanced PostgreSQL features such as custom enumerated types (Enums) for status tracking and pgvector for high-dimensional embedding storage to enable AI-powered semantic search.
Sources: README.md:9-11, packages/db/src/schema/data.ts:24-118
Core Data Architecture
The architecture is centered around the relationship between users, their organizational or volunteer roles, and the humanitarian datasets they provide or interact with. Data is categorized into three primary modules: Identity/Authentication, Dataset Management, and Community/Profiles.
Entity Relationship Diagram
The following diagram illustrates the primary entities and their relationships within the Poneglyph ecosystem.
The schema establishes strict referential integrity with cascading deletes for user-related and source-related data.
Sources: packages/db/src/schema/data.ts:133-176, packages/db/src/migrations/meta/0005_snapshot.json:455-520
Dataset Management System
Datasets are the core assets of the platform. The system tracks the origin of data through a sources table and maintains metadata, file references, and vector embeddings for each dataset.
Dataset Schema Details
The datasets table includes fields for both administrative tracking and AI integration.
| Field | Type | Description |
|---|---|---|
id | UUID | Primary key, generated randomly. |
external_id | Text | Unique ID from external sources (e.g., CKAN) for deduplication. |
source_id | UUID | Foreign key referencing the sources table. |
title | Varchar(500) | The descriptive title of the dataset. |
s3_keys | Text[] | Array of keys for attachments stored in S3. |
file_types | Enum[] | Array of supported file types (PDF, CSV, JSON, etc.). |
embedding | Vector(768) | 768-dimension vector for semantic search. |
dataset_status | Enum | Current state: pending, approved, rejected, archived. |
Sources: packages/db/src/schema/data.ts:50-89, packages/db/src/migrations/meta/0005_snapshot.json:302-401
Data Ingestion and Synchronization
The system tracks the health of data ingestion through sources and sync_logs.
User and Community Profiles
The schema supports two distinct user types beyond the standard auth profile: Organisations and Volunteers.
Profiles and Tagging
- Organisations: Store business-level metadata including country, website, and social links.
- Volunteers: Track personal bios, availability (
is_open_to_work), and past works. - Tagging System: A unified
tagstable is used for both datasets and volunteer skills, linked via junction tablesdataset_tagsandvolunteer_tags.
Sources: packages/db/src/migrations/meta/0005_snapshot.json:11-125, packages/db/src/migrations/0005_certain_mentor.sql:1-12
Authentication (Better-Auth)
Authentication is handled via a standardized set of tables including user, account, session, and verification.
The user table serves as the central anchor for all other profile types through user_id foreign key references with ON DELETE cascade.
Sources: packages/db/src/migrations/meta/0004_snapshot.json:11-255, packages/db/src/migrations/0000_funny_kitty_pryde.sql:6-66
Technical Implementation Details
Custom Enumerated Types
The schema defines several PostgreSQL Enums to enforce data consistency:
dataset_status:pending,approved,rejected,archivedfile_type:pdf,csv,xlsx,xls,json,docx,othersource_type:upload,external_url,apisync_status:running,completed,failed
Sources: packages/db/src/schema/data.ts:12-22, packages/db/src/migrations/meta/0005_snapshot.json:638-688
Performance Optimization
To support efficient querying, the schema defines several indexes:
- B-Tree Indexes: Applied to foreign keys (
source_id,user_id,tag_id) and frequently filtered fields likevolunteer.city. - HNSW Index: Specifically applied to the
embeddingcolumn in thedatasetstable usingvector_cosine_opsto enable fast nearest-neighbor searches.
Sources: packages/db/src/schema/data.ts:85-88, packages/db/src/migrations/meta/0005_snapshot.json:370-394, packages/db/src/migrations/0005_certain_mentor.sql:19-20
Summary
The Database Schema Design for Poneglyph provides a robust foundation for humanitarian data analysis. By combining traditional relational structures for user management with specialized vector storage for AI capabilities, the system efficiently manages the lifecycle of a dataset from ingestion (Source/Sync) to discovery (Semantic Search) and community engagement (Organisation/Volunteer profiles).