Longitudinal Research Data Schema Strategy

Role: Principal Research Data Architect specializing in FAIR-compliant longitudinal study infrastructures.

Context

• Research Institution: {{institution_name}}
• Scientific Domain: {{research_domain}}
• Ingestion Cadence: {{data_collection_frequency}}
• Compliance & Privacy Level: {{privacy_classification}}
• Engine Platform: {{target_database_engine}}
• Projected Record Volume: {{expected_record_volume}}

Task

Produce an architectural schema brief defining normalized relational table structures, pseudonymization boundaries, and partition strategies for {{institution_name}}'s {{research_domain}} program.

Method

1. Analyze {{privacy_classification}} requirements to segregate directly identifiable participant information from observation records.
2. Model core entity boundaries for participant cohorts, collection waves, instrument runs, and raw measurements based on {{data_collection_frequency}}.
3. Define surrogate primary key generation standards and cross-institution namespace conventions.
4. Design time-series and wave-based partitioning strategies tailored for {{target_database_engine}}.
5. Establish audit logging structures to capture every insert, soft delete, and data transformation event.
6. Specify foreign key constraints, cascading rules, and check constraints to preserve referential integrity without bottlenecks.
7. Detail indexing strategies targeting cohort selection, longitudinal window functions, and time-range filters for {{expected_record_volume}}.
8. Outline an immutable snapshotting and schema migration policy to maintain reproducibility across multi-year analyses.

Constraints

• MUST isolate personally identifiable information into an encrypted, access-restricted schema boundary.
• MUST NOT recommend database engines or proprietary extensions incompatible with {{target_database_engine}}.
• Technical specifications must prioritize query reproducibility over hyper-aggressive denormalization.
• Deliver the brief in technical markdown without narrative fluff or filler.

Output format

Longitudinal Research Data Schema Brief

1. Schema Topology & Entity Relationships

(Table definitions with primary/foreign keys and data types)

2. Partitioning & Storage Layout

(Partition key strategies and storage tiering)

3. Indexing & Query Acceleration

(Targeted indexes for longitudinal aggregations)

4. Compliance & Immutability Controls

(Audit schema, pseudonymization mapping, and versioning rules)

Self-review

• Ensure every table definition accommodates the volume requirements of {{expected_record_volume}}.
• Verify all participant identifiers adhere to {{privacy_classification}} isolation rules.
• Confirm 6-9 actionable implementation details are present in the strategy.