WL.
← Back to Home

Engineering a Theological NLP Platform: System Architecture, Data Pipelines, and Schema Design

Engineering a Theological NLP Platform: System Architecture, Data Pipelines, and Schema Design

When building data platforms for complex domain-specific text, standard AI summarization tools often fall short. Analyzing theological discourse—specifically spoken and written sermons—presents a unique software engineering challenge: transforming highly unstructured, nuanced, and non-linear audio/text into structured, queryable data models without losing domain fidelity.

To solve this, I designed and built a two-tier architecture comprising a backend NLP ingestion engine (sermon_analysis_engine) and a high-performance web platform (sermon_analysis) built with Astro, Drizzle ORM, and PostgreSQL. Here is a breakdown of the key system design decisions, data modeling strategies, and automation pipelines behind the project.

1. Decoupled Architecture & System Design

A primary architectural goal was separating computation-heavy NLP ingestion from real-time web delivery:

  • Backend Ingestion Engine (sermon_analysis_engine): An automated processing pipeline responsible for ingesting sermon transcripts/audio, performing structured LLM extraction, normalizing metadata, and formatting outputs into validated JSON schemas.
  • Frontend Delivery Platform (sermon_analysis): A modern Jamstack application built with Astro 7, Node.js (v22+), Drizzle ORM, and PostgreSQL. It delivers statically generated pages (SSG) with near-zero JavaScript runtime overhead, achieving top-tier Lighthouse performance.

Decoupling the processing engine from the web application allows the pipeline to process long-form audio/text asynchronously while maintaining lightning-fast page response times for end users.

2. Flexible Data Modeling: PostgreSQL + Drizzle ORM JSONB

One of the most critical schema decisions was handling the polymorphic nature of sermon structures. Traditional strictly relational tables require rigid columns that break when a sermon includes optional theological commentaries, varied outline depths, or custom scripture references.

Instead of over-normalizing into dozens of join tables or using unstructured text blobs, I designed a hybrid relational-document model using Drizzle ORM and PostgreSQL JSONB columns:

export const sermons = appSchema.table('sermons', {
  id: serial('id').primaryKey().notNull(),
  fileName: text('file_name'),
  title: text('title'),
  speaker: text('speaker'),
  church: text('church'),
  date: date('date'),
  theme: jsonb('theme').array(),
  sections: jsonb('sections'),
  pubDate: timestamp('pub_date', { withTimezone: true }).defaultNow(),
});

By storing sections as structured JSONB arrays (e.g., [{'type': 'outline', 'content': '...'}, {'type': 'theology', 'content': '...'}]), the system gains schema flexibility while maintaining strong TypeScript type safety across the application.

3. Multi-Environment Database Branching

To support smooth developer workflows and zero-downtime deployments, I implemented environment-based schema isolation within PostgreSQL at the ORM level:

const getEnv = () => process.env.ENVIRONMENT || 'staging';
const schemaName = getEnv() === 'production' ? 'production' : 'staging';
export const appSchema = pgSchema(schemaName);

This approach allows staging and production environments to co-exist cleanly in PostgreSQL using isolated schema namespaces ('staging' vs 'production'), managed seamlessly via Drizzle Kit migration scripts ('drizzle-kit generate' and 'drizzle-kit push').

4. Automated Data Pipeline & Sync Engine

Automation is baked into the platform's data lifecycle:

  • Automated Ingestion: A dedicated seeding script (seed.ts) parses MDX files and frontmatter using gray-matter, extracts normalized metadata (speaker, church, themes, dates), and hydrates PostgreSQL tables with idempotent upsert operations (onConflictDoNothing()).
  • Data Sanitization & Formatting: Raw text outputs from LLM pipelines often contain escaping artifacts (e.g., literal '\n' or missing list linebreaks). I built dynamic text sanitization layers that format markdown on the fly before passing it to Marked.js for HTML rendering.
  • RSS & Metadata Normalization: Built automated RSS feed generation (@astrojs/rss) with resilient date handling that gracefully fallback between post creation timestamps and sermon delivery dates.

5. Frontend Component Engineering: Polymorphic UI Rendering

To turn structured data into an intuitive reading experience, I engineered reusable UI components in Astro that map directly to the JSONB section types:

  • <TheologySection />: A collapsible, accessible UI card designed for deep doctrinal commentary. It dynamically filters sections where type === 'theology', parses markdown content, and provides accessible keyboard/toggle controls.
  • <ScriptureVisualizer />: An inline UI badge component that renders scripture references with custom SVG iconography and dark/light thematic styling.

Technical Summary & Stack Snapshot

  • Framework & SSG: Astro 7, Node.js 22+
  • Database & ORM: PostgreSQL, Drizzle ORM, Drizzle Kit
  • Data Ingestion: Python NLP / LLM processing pipeline, Node.js automated seeder, gray-matter
  • Styling & UI: Component-driven styling, accessible ARIA patterns, Atkinson local font optimization
  • DevOps & Quality: Environment-isolated DB schemas, automated RSS/sitemap feeds, zero-runtime JS optimization

This project demonstrates how thoughtful software architecture, flexible data modeling, and automated data pipelines can turn unstructured domain text into a robust, enterprise-grade software product.