Database Architect

The database estate is enterprise-scale: 20+ schemas partitioned across seven distinct architectural layers, serving multi-tenant pharmaceutical customers who operate under 21 CFR Part 11 (FDA), EU Annex 11, and GAMP 5. Data integrity and immutable audit trails are product requirements, not compliance checkbox items.

Why This Role Exists
BhriguOne's database estate has grown to a complexity that demands a dedicated Principal-level architect. No single engineer currently owns the cross-cutting concerns of schema governance, sharding strategy, GxP validation of the data tier, time-series performance tuning for SCADA historian loads, vector/graph stores for AI features, and the operational health of both cloud (Aurora PostgreSQL) and on-premises PostgreSQL deployments. This hire fills that gap — and shapes the data platform for the next five years.

Key Responsibilities

Cloud Database — Aurora PostgreSQL & AWS
• Own Aurora PostgreSQL cluster architecture: writer/reader topology, Global Database for cross-region DR, automated failover, and PITR configuration
• Design and implement the multi-tenant sharding strategy: schema-per-tenant for isolation vs. row-level security (RLS) partitioned shards for cost efficiency at scale; Citus or logical replication for horizontal scale-out
• Define and enforce connection pooling architecture using PgBouncer (transaction mode) layered between EKS microservices and Aurora, targeting <5ms connection acquisition under peak OT workloads
• Govern Aurora Serverless v2 autoscaling policies and Reserved Instance / Savings Plan strategy across dev, staging, and production environments
• Design multi-region Active-Active or Active-Passive topology for enterprise pharmaceutical customers with data residency requirements (EU vs. US)
• Own AWS RDS Parameter Group tuning: sharedbuffers, workmem, autovacuum, WAL settings for mixed OLTP + time-series write workloads

On-Premises PostgreSQL
• Design and maintain on-premises PostgreSQL deployment for customers with air-gapped or hybrid cloud requirements (common in EU pharma and defence-adjacent biotech)
• Implement Patroni + etcd HA clusters for on-prem PostgreSQL with sub-30-second automatic failover
• Build pgBackRest backup and WAL archiving pipelines with encryption at rest, verified restores, and documented RPO/RTO for GxP validation documentation
• Define the on-prem → Aurora migration playbook: logical replication cutover, dual-write validation period, zero-downtime switchover
• Govern OS-level tuning: huge pages, vm.swappiness, NUMA topology, storage I/O scheduler for dedicated DB hosts

Database Sharding & Horizontal Scale
• Design the shard key taxonomy for BhriguOne: tenantid as primary shard key, with composite shard keys (tenantid + module + time_bucket) for time-series schemas
• Evaluate and select sharding technology: Citus (columnar + distributed tables) vs. application-level sharding via consistent hashing vs. PostgreSQL declarative partitioning
• Build cross-shard query routing layer that maintains ACID guarantees for GxP-critical write paths (e-signatures, batch record closure, deviation creation)
• Implement shard rebalancing strategy as tenant data volumes grow — zero-downtime resharding plan is a hard requirement
• Define shard-level backup isolation so a single tenant's backup and restore does not touch neighboring tenant data

GxP Database Design & Validation
• Own the GxP data architecture: immutable audit log tables with append-only constraints, hash-chain integrity via OpenBao transit engine, and cryptographic signing of critical record state transitions
• Author and maintain database-tier validation documents: IQ/OQ protocols for Aurora and PostgreSQL deployments, PQ scripts for data integrity under representative batch execution loads
• Implement and enforce ALCOA+ at the database layer: Attributable (createdby + sessionid FK), Legible (constraint-enforced encoding), Contemporaneous (server-side timestamptz, NTP-synchronized), Original (immutable primary + versioned shadow table), Accurate (check constraints + trigger-enforced domain rules)
• Design schema change control process: all DDL migrations gated through Flyway with peer review, impact assessment, and rollback scripts; production DDL changes require a validated change record
• Define data retention policies per GxP record type: batch records (minimum 1 year post-expiry or life of product), audit logs (minimum 5 years), SCADA historian (configurable per regulation)
• Support FDA / MHRA audit readiness: produce database architecture diagrams, data flow maps, and validation summary reports on demand

Time-Series, SCADA & Historian
• Own TimescaleDB hypertable design for the SCADA historian: partitioning strategy (time + tagid), compression policies, continuous aggregates for 1-min / 1-hour / 1-day rollups
• Tune TimescaleDB for high-ingest OT workloads: bulk insert batching, parallel workers, WAL tuning, and chunk exclusion optimization for range queries
• Design the live streaming path: Kafka → InfluxDB for sub-second real-time process parameter dashboards, with eventual consistency sync to TimescaleDB for the GxP audit-quality record
• Implement data tiering: hot data in TimescaleDB SSD tier, warm data in compressed chunks, cold data offloaded to S3 via TimescaleDB Cloud tiering or pgpartman + custom archival job
• Validate time-series data integrity for ISA-88 batch records: ensure every SCADA tag value written during a batch phase is retrievable and matches the EBR's process parameters
• Design the ISA-18.2 alarm state persistence model: alarm state machine transitions stored with nanosecond-precision timestamps for regulatory alarm management reports

Vector & Graph Databases
• Own pgvector schema design for multi-tenant RAG pipelines: embedding dimensions, HNSW vs. IVFFlat index selection, per-tenant namespace isolation using schema-level partitioning
• Govern Qdrant deployment for high-cardinality collections (SOP embeddings, batch record embeddings, deviation knowledge base) — collection strategy, payload indexing, quantization for cost control
• Design Apache Age or Neo4j graph schema for the pharmaOS ontology: node types (BLUEPRINT, STEP, SECTION, FIELD, FRAGMENT, INSTRUCTION), edge types, property constraints, and index strategy
• Build equipment dependency graph in the graph DB: upstream/downstream equipment relationships for impact analysis during CMMS work orders and deviation investigations
• Define the AI model provenance graph: model version nodes → training dataset edges → inference event edges, queryable for GxP AI audit trail requirements
• Implement vector search SLA monitoring: p95 ANN query latency targets per collection size, alerting on index drift requiring rebuild

Analytics & Data Warehouse Layer
• Design the Apache Iceberg table architecture on S3: partition spec (tenant_id + year + month), snapshot isolation for concurrent reads during batch close, time-travel for point-in-time regulatory queries
• Build dbt transformation DAG for analytical projections: batch genealogy, equipment utilization, deviation trend, CAPA effectiveness KPI tables
• Own Redshift Spectrum or Athena query optimization: partition pruning, columnar compression codec selection (Parquet + Snappy vs. ORC + ZSTD), workgroup cost controls per tenant
• Implement data lineage tracking for ALCOA+ analytics: every analytical record traces back to its source GxP event via immutable lineage metadata
• Define the Kafka → Iceberg streaming materialization path using Apache Flink or Spark Structured Streaming for near-real-time analytical freshness

AI Platform Database Support
• Design the AI model registry schema: model metadata, hyperparameters, validation status, deployment targets, drift metric history — all GxP-validated as Category 4 software data
• Own the prompt/response audit log persistence: every LLM interaction for GxP workflows stored with user identity, timestamp, model version, input hash, output hash for Part 11 audit trail
• Build the feature store backing tables in TimescaleDB: materialized feature vectors for ML inference, versioned by time window and model version
• Design database-layer support for HITL (human-in-the-loop) AI workflows: pending review queues, approval state machines, override logging — all immutable and auditable

Database Operations & SRE
• Define and enforce RPO < 1 minute / RTO < 15 minutes SLAs for all GxP-tier databases via continuous WAL archiving, Aurora Global Database, and automated restore testing
• Own database observability: pgstatstatements, auto_explain, pgBadger analysis, custom Prometheus exporters, Grafana dashboards for query performance, lock waits, replication lag, autovacuum health
• Build automated slow query alerting pipeline: queries exceeding configurable thresholds trigger Jira tickets with EXPLAIN ANALYZE output attached
• Design and execute quarterly chaos drills: Aurora failover, replica promotion, cross-region switchover — all with GxP deviation report if RTO SLA is breached
• Own database cost governance: Aurora capacity unit analysis, reserved instance coverage, Iceberg storage cost per tenant chargeback model

Requirements

Technical Requirements (Non-Negotiable)
• 12+ years in database engineering; 5+ years as a principal/staff architect
• Deep PostgreSQL internals mastery: MVCC, WAL, autovacuum, planner statistics, extensions
• Aurora PostgreSQL: cluster topology, Global Database, Serverless v2, Parameter Groups
• Sharding design: Citus, logical replication, application-level sharding, RLS at scale
• TimescaleDB: hypertables, continuous aggregates, compression, chunk lifecycle management
• Kafka integration: producer/consumer patterns, topic design, at-least-once vs. exactly-once
• Cloud data platforms: Iceberg, Athena/Redshift Spectrum, dbt at production scale
• Migration tooling: Flyway or Liquibase; zero-downtime DDL in high-traffic systems
• Vector databases: pgvector, Qdrant, or Pinecone — ANN index tuning
• On-premises PostgreSQL HA: Patroni, repmgr, pgBackRest, etcd
• IaC for databases: Terraform RDS modules, Helm charts for DB operators
• Strong SQL and PL/pgSQL; Python or Go for automation scripts

GxP & Domain Requirements (Non-Negotiable at Principal Level)
• GxP / pharma database experience: 21 CFR Part 11, EU Annex 11, GAMP 5
• IQ/OQ/PQ authoring for database-tier systems
• ALCOA+ implementation at database schema level
• Audit trail design: append-only tables, hash chaining, cryptographic integrity
• SCADA/MES/Historian data modeling: ISA-88, ISA-95, ISA-18.2 familiarity
• InfluxDB or equivalent IoT/OT time-series store at scale
• Graph database: Apache Age, Neo4j, or Amazon Neptune — property graph design
• OpenSearch for audit log full-text search and aggregation pipelines
• OpenBao / HashiCorp Vault: database secrets engine, dynamic credentials
• Multi-tenant SaaS database patterns: tenant isolation at schema and row level
• Data residency and cross-region compliance architecture
• Experience with FDA, MHRA, or EMA audit support for database systems