Extending Database & API Schemas

Relevant source files

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

• packages/db/src/schema/data.ts
• packages/db/src/migrations/meta/0005_snapshot.json
• packages/schemas/package.json
• package.json
• README.md
• apps/server/src/agents/prompts/system.ts

Extending the database and API schemas in the Poneglyph project involves a coordinated effort between the database layer, which uses Drizzle ORM and PostgreSQL, and the API validation layer, which utilizes Zod. This architecture ensures type safety across the entire stack, from the database persistent storage to the frontend data consumption.

The project structure centralizes schema definitions within the packages/db and packages/schemas directories, allowing various applications like the server (Hono) and the web client (Next.js) to share consistent data models. This modularity is managed via Turborepo, ensuring that changes to the core schemas propagate through the monorepo during the build process.

Sources: README.md, package.json:7-20, packages/schemas/package.json

Database Schema Architecture

The database layer is built on PostgreSQL using Drizzle ORM for schema definition and migration management. The schema is divided into logical entities including users, organisations, volunteers, and datasets.

Core Data Models

The primary focus of the data schema is the management of humanitarian datasets and their associated metadata. This includes tracking the source of the data, categorical tags, and the status of synchronization tasks.

The diagram above illustrates the relationships between core entities in the Poneglyph system.

Sources: packages/db/src/schema/data.ts:25-144, packages/db/src/migrations/meta/0005_snapshot.json

Key Database Entities

Table	Description	Key Fields
user	Core authentication entity.	id, email, name
datasets	Primary storage for dataset metadata and file references.	id, source_id, title, s3_keys, embedding
sources	Defines where data originates (upload, API, URL).	id, user_id, source_type, is_verified
tags	Classification labels for datasets and volunteers.	id, name, slug
sync_logs	Audit trail for automated data ingestion tasks.	id, source_id, sync_status, error

Sources: packages/db/src/schema/data.ts:25-115, packages/db/src/migrations/meta/0005_snapshot.json

Vector & AI Integration

The Poneglyph database schema includes support for vector embeddings to enable advanced search capabilities within the humanitarian intelligence analyst agent.

Vector Search Support

The datasets table includes an embedding column defined as a vector(768). This column is indexed using the hnsw method with vector_cosine_ops to facilitate high-performance similarity searches. This is utilized by the searchDatabase tool within the AI orchestrator to find relevant field reports and documents.

// packages/db/src/schema/data.ts:60-93
export const datasets = pgTable(
  "datasets",
  {
    // ... other fields
    embedding: vector("embedding", { dimensions: 768 }),
    // ...
  },
  (table) => [
    index("datasets_embedding_idx").using("hnsw", table.embedding.op("vector_cosine_ops")),
  ],
);

Sources: packages/db/src/schema/data.ts:84, 95, apps/server/src/agents/prompts/system.ts:9-12

API Schema Validation

API schemas are managed in a dedicated package @Poneglyph/schemas, which exports Zod objects used for request validation and response typing.

Schema Modules

The project organizes schemas by functional domain:

• Common: Shared utility schemas.
• Chat: Schemas for AI agent interactions.
• Upload: Definitions for dataset file ingestion.
• Dataset: Core dataset CRUD and search schemas.
• Volunteer: Management of volunteer profiles and interests.

Sources: packages/schemas/package.json:4-10

Data Flow and Synchronization

When data is ingested from external sources (like CKAN) or user uploads, it follows a specific pipeline from the worker/client to the database.

The flow shows how raw data is validated via Zod before being persisted through Drizzle and eventually indexed for AI retrieval.

Sources: apps/server/src/agents/prompts/system.ts:9-13, packages/db/src/schema/data.ts:58-97

Extending the Schema

To extend the system with new data requirements, developers must follow a multi-step process to ensure consistency:

1. Define DB Table: Add or modify the table definition in packages/db/src/schema/.
2. Generate Migration: Run bun run db:generate to create a new SQL migration.
3. Apply Migration: Use bun run db:push or bun run db:migrate to update the local/staging database.
4. Update Zod Schemas: Correspondingly update the Zod objects in packages/schemas/src/.
5. Rebuild Package: Ensure Turborepo rebuilds the shared packages so the API and Web apps receive the updated types.

Sources: README.md:21-40, package.json:23-26

Conclusion

The Poneglyph schema architecture provides a robust, type-safe foundation for humanitarian data analysis. By leveraging Drizzle for relational and vector data alongside Zod for API boundaries, the system ensures that complex datasets are handled consistently across the backend, automated workers, and AI search interfaces. This structure allows for rapid extension of data models while maintaining high standards for data integrity and searchability.