Enhanced Database Seeding for Preview Environments

Overview

This document outlines a comprehensive strategy for database seeding in PR preview environments. The goal is to provide rich, realistic test data that allows developers and testers to immediately explore all features of SyRF without manual setup.

Problem Statement

Current seeding creates minimal data:

• One "Seed Bot" owner (no real user can log in as)
• One project with no screening decisions
• A few studies with no annotations
• No way for logged-in users to interact with seeded data

Result: Users must create their own projects from scratch, defeating the purpose of preview environments.

Design Principles

1. Current Preview User Has Access: A previously unseen authorized preview user inherits ownership of all canonical seed projects; earlier preview users remain members
2. Valid Application States: All seed data must be achievable through normal application workflows
3. Multi-State Coverage: Seed projects at different workflow stages (screening, annotation, complete)
4. Realistic Data: Use real-looking study data, annotation questions, and screening decisions
5. Idempotent: Repeat deploys and logins do not duplicate projects or memberships, and returning earlier members cannot take ownership back

Architecture

Seeding Approach Decision

Decision: Use Application Service Layer seeding (domain methods directly) rather than API-driven or UI-driven seeding.

Alternatives Considered:

Approach	Pros	Cons
UI-Driven (Playwright)	Tests complete stack including JS validation	Extremely slow, fragile CSS selectors, complex setup, overkill for data creation
API-Driven (HTTP Client)	Tests API contract	Requires running API during seeding, auth complexity, chicken-egg problem at startup
Application Service Layer ✓	Same business logic as APIs, no HTTP overhead, runs at startup, testable	Doesn't test HTTP layer

Rationale:

1. The frontend contains view logic (form validation, category mappings), not additional business logic
2. The backend domain is the source of truth for data integrity
3. Seeding must run at startup before the API is fully available
4. Using Project.UpsertCustomAnnotationQuestion() produces identical database state as the UI path

Separate E2E Testing: A Playwright test suite should verify seeded data is accessible via the UI, but this is separate from the seeding process itself.

Seed Data Hierarchy

Seed Investigators (fake reviewers)
├── Seed Bot (system owner - transfers to first real user)
├── Seed Reviewer Alpha
└── Seed Reviewer Beta

Seed Projects
├── "Quick Start Demo" (public, ready to screen)
│   ├── 10 unscreened studies
│   └── Screening stage active
│
├── "Screening In Progress" (public, dual screening 50% done)
│   ├── 30 studies total
│   ├── 15 studies: both reviewers screened (Include/Exclude)
│   ├── 10 studies: one reviewer screened
│   ├── 5 studies: unscreened
│   └── Shows agreement metrics
│
├── "Ready for Annotation" (public, screening complete)
│   ├── 20 studies (all included after screening)
│   ├── Annotation stage active
│   ├── 10 annotation questions configured
│   └── 5 studies partially annotated
│
├── "Complete Review" (private, fully done)
│   ├── 15 studies fully annotated
│   ├── Multiple experiments/cohorts extracted
│   └── Ready for export demonstration
│
└── "Private Research" (private, requires approval)
    ├── 8 studies
    ├── AutoApproveJoinRequests = false
    └── Demonstrates join request workflow

Ownership Transfer Mechanism

The Quick Start project is the reconciliation record. In an exact preview environment, a previously unseen authorized user is added as an administrator and becomes its owner. The same user is then reconciled across the other four canonical projects. Earlier owners remain administrator members, preserving their access. If an earlier member returns after a newer user became owner, Quick Start identifies that user as already visible; missing memberships on the other canonical projects are repaired without taking ownership back.

Each reconciliation uses a fresh MongoDB read and an optimistic compare-and-replace guarded by the project's audit version and observed owner. This path deliberately bypasses the singleton repository cache. A process-local gate serializes simultaneous logins within one API process. A Mongo-backed, expiring reconciliation lease serializes the complete five-project sweep across API processes; ordinary completion releases it immediately, while expiry makes a crashed process recoverable on a later request. Each acquisition atomically advances a monotonic generation. The service renews the lease before each project, and every optimistic repository attempt verifies the active token and generation. Every successful canonical project result persists that generation, including ownership no-ops, and the generation participates in the replace filter. A stale holder therefore stops after takeover; version, owner, and generation guards fence an in-flight replacement even when the successor already owns the project. The repository rejects every project ID outside the five fixed canonical seed IDs before reading or writing it.

Ownership transfer enforces membership uniqueness at its persistence boundary. It fails closed if persisted data already contains duplicate memberships, duplicate pending join requests, or an owner without a membership. It does not silently select one duplicate or rewrite corrupt data. This seed-specific guard must not change the normal invitation lifecycle, which may create a replacement membership for an inactive former member. Preview environments with pre-existing seed corruption must be repaired through the audited preview reseed mechanism; staging and production data require a separately reviewed data-repair plan.

After all seed projects converge, a per-user process-local terminal state removes the database read from that user's later authenticated requests. A changed preview user still reconciles. Missing projects, write conflicts, and persistence exceptions leave the check retryable, including while an explicit reseed rebuilds deterministic documents. Normal deployments intentionally preserve the preview's seed identity and database, so reconciliation does not depend on a new seeding job. Teardown drops the PR-scoped database unless lock-db explicitly preserves it; no reconciliation state is stored outside that database and the API process.

┌─────────────────────────────────────────────────────────────────┐
│                    Application Startup                          │
│                           │                                     │
│                    DatabaseSeeder.Execute()                     │
│                           │                                     │
│          ┌────────────────┴────────────────┐                   │
│          │                                 │                    │
│    First startup?                   Already seeded?             │
│    Seed all data                    Skip seeding                │
│          │                                 │                    │
│          └────────────────┬────────────────┘                   │
└───────────────────────────┼─────────────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────────────┐
│                Authorized Preview User Logs In                  │
│                           │                                     │
│              UserHasRegisteredHandler.HandleAsync()             │
│                           │                                     │
│       SeedDataOwnershipTransfer.TransferToFirstRealUserAsync()   │
│                           │                                     │
│          ┌────────────────┴────────────────┐                   │
│          │                                 │                    │
│  Quick Start reconciles user:       Remaining seed projects:    │
│  - new user becomes owner           - same canonical user scope │
│  - prior members keep access        - retry-safe/idempotent     │
│  - returning users cannot reclaim   - continue after one error │
│          │                                 │                    │
│          └────────────────┬────────────────┘                   │
└───────────────────────────┼─────────────────────────────────────┘

Component Design

1. SeedDataConstants.cs (Enhanced)

public static class SeedDataConstants
{
    // Seed Investigators
    public static readonly Guid SeedBotId = new("00000000-0000-0000-0000-000000000001");
    public static readonly Guid SeedReviewerAlphaId = new("00000000-0000-0000-0000-000000000010");
    public static readonly Guid SeedReviewerBetaId = new("00000000-0000-0000-0000-000000000011");

    // Seed Projects
    public static readonly Guid QuickStartProjectId = new("00000000-0000-0000-0000-000000000100");
    public static readonly Guid ScreeningInProgressProjectId = new("00000000-0000-0000-0000-000000000101");
    public static readonly Guid ReadyForAnnotationProjectId = new("00000000-0000-0000-0000-000000000102");
    public static readonly Guid CompleteReviewProjectId = new("00000000-0000-0000-0000-000000000103");
    public static readonly Guid PrivateResearchProjectId = new("00000000-0000-0000-0000-000000000104");

    public static readonly IReadOnlyCollection<Guid> AllSeedProjectIds = new[]
    {
        QuickStartProjectId,
        ScreeningInProgressProjectId,
        ReadyForAnnotationProjectId,
        CompleteReviewProjectId,
        PrivateResearchProjectId
    };
}

2. DatabaseSeeder.cs (Enhanced Structure)

public class DatabaseSeeder : IRunAtInit
{
    public void Execute()
    {
        _unitOfWork.CreateMappings();
        CleanupCorruptedSeedDataIfNeeded();

        if (!IsSeedingEnabled() || DatabaseHasData()) return;

        _logger.LogInformation("Starting enhanced database seeding");

        // Phase 1: Create seed investigators
        SeedInvestigators();

        // Phase 2: Create projects with different configurations
        SeedQuickStartProject();
        SeedScreeningInProgressProject();
        SeedReadyForAnnotationProject();
        SeedCompleteReviewProject();
        SeedPrivateResearchProject();

        _logger.LogInformation("Database seeding completed");
    }
}

3. SeedDataOwnershipTransfer.cs

/// <summary>
/// Reconciles canonical seed projects with the current authorized preview user.
/// </summary>
public class SeedDataOwnershipTransfer
{
    public async Task TransferToFirstRealUserAsync(Guid realUserId)
    {
        if (!_environment.IsPreview || _state.IsComplete(realUserId)) return;

        await _state.EnterAsync();
        var leaseToken = Guid.NewGuid();
        long? leaseGeneration = null;
        try
        {
            leaseGeneration = await TryAcquireLeaseAsync(leaseToken, realUserId);
            if (!leaseGeneration.HasValue) return;
            if (!await TryRenewLeaseAsync(
                    leaseToken, leaseGeneration.Value, realUserId)) return;
            var quickStart = await _unitOfWork.Projects.EnsureSeedProjectOwnershipAsync(
                SeedDataConstants.QuickStartProjectId,
                realUserId,
                SeedProjectOwnershipTransferPolicy.ElectPreviouslyUnseenOwner,
                leaseToken,
                leaseGeneration.Value);
            if (!IsSuccessful(quickStart.Status)) return;

            // Returning members regain missing access without taking ownership
            // back. New users and the current owner converge ownership.
            var policy = quickStart.OwnerId == realUserId
                    ? SeedProjectOwnershipTransferPolicy.ConvergeOwner
                    : SeedProjectOwnershipTransferPolicy.AccessOnly;
            var allSucceeded = true;
            foreach (var projectId in SeedDataConstants.AllSeedProjectIds
                         .Where(id => id != SeedDataConstants.QuickStartProjectId))
            {
                if (!await TryRenewLeaseAsync(
                        leaseToken, leaseGeneration.Value, realUserId)) return;
                var result = await _unitOfWork.Projects.EnsureSeedProjectOwnershipAsync(
                    projectId,
                    realUserId,
                    policy,
                    leaseToken,
                    leaseGeneration.Value);
                allSucceeded &= IsSuccessful(result.Status);
            }

            if (allSucceeded) _state.MarkComplete(realUserId);
        }
        finally
        {
            if (leaseGeneration.HasValue)
                await TryReleaseLeaseAsync(
                    leaseToken, leaseGeneration.Value, realUserId);
            _state.Exit();
        }
    }
}

EnsureSeedProjectOwnershipAsync(projectId, requestedOwnerId, policy, leaseToken, leaseGeneration) returns a typed result rather than hiding a conflict. ElectPreviouslyUnseenOwner is used only for Quick Start, ConvergeOwner aligns the other canonical projects, and AccessOnly repairs memberships for returning users. Transferred, AccessGranted, AlreadyOwned, and AlreadyAccessible are successful/idempotent outcomes. NotFound remains retryable while reseeding; NotCanonicalSeedProject, DuplicateMemberships, DuplicatePendingJoinRequests, OwnerMissingMembership, LeaseLost, and exhausted WriteConflict retries are logged and left unchanged for explicit repair. When ownership transfer is requested, the aggregate elects only a previously unseen member. That membership check, membership creation, and ownership change are persisted in one optimistic replacement, so a returning member cannot reclaim ownership after a process restart and a crash cannot split a new-user election across two writes.

Seed Data Specifications

Seed Investigators

ID	Name	Email	Purpose
...0001	SyRF Seed Bot	seedbot@syrf.org.uk	Project owner (transfers)
...0010	Dr. Alpha Reviewer	alpha@syrf-seed.local	Screening decisions
...0011	Dr. Beta Reviewer	beta@syrf-seed.local	Screening decisions

Project Configurations

Quick Start Demo

• Visibility: Public
• Auto-approve: Yes
• Agreement Mode: Single screening
• Stages: Screening (active), Annotation (inactive)
• Studies: 10 unscreened
• Purpose: Immediate hands-on screening

Screening In Progress

• Visibility: Public
• Auto-approve: Yes
• Agreement Mode: Automated dual screening (33% threshold)
• Stages: Screening (active)
• Studies: 30 total
• 15 fully screened (both reviewers)
• 10 partially screened (one reviewer)
• 5 unscreened
• Decisions:
• 10 included (agreement)
• 3 excluded (agreement)
• 2 disagreement (needs reconciliation)
• Purpose: Show screening progress, agreement metrics

Ready for Annotation

• Visibility: Public
• Auto-approve: Yes
• Agreement Mode: Completed
• Stages: Annotation (active)
• Studies: 20 (all passed screening)
• Annotation Questions: 10 configured
• 3 study-level questions
• 4 experiment-level questions
• 3 outcome questions
• Annotations: 5 studies partially annotated
• Purpose: Data extraction workflow

Complete Review

• Visibility: Private
• Auto-approve: Yes
• Agreement Mode: Completed
• Stages: All complete
• Studies: 15 fully annotated
• Annotations: Complete for all studies
• Purpose: Export demonstration, completed project view

Private Research

• Visibility: Private
• Auto-approve: No
• Agreement Mode: Single screening
• Studies: 8
• Purpose: Join request workflow demonstration

Annotation Questions

[
  {
    "id": "...0200",
    "question": "What species was used?",
    "type": "dropdown",
    "category": "Study",
    "options": ["Mouse", "Rat", "Other rodent", "Non-rodent"]
  },
  {
    "id": "...0201",
    "question": "Was randomization performed?",
    "type": "boolean",
    "category": "RiskOfBias"
  },
  {
    "id": "...0202",
    "question": "Sample size per group",
    "type": "integer",
    "category": "Experiment"
  },
  {
    "id": "...0203",
    "question": "Intervention description",
    "type": "textbox",
    "category": "Treatment"
  },
  {
    "id": "...0204",
    "question": "Primary outcome measure",
    "type": "string",
    "category": "OutcomeAssessment"
  }
]

Sample Studies (Enhanced)

Studies should have realistic titles/abstracts from preclinical research. Use embedded JSON resource with 50+ studies covering:

• Different disease models (stroke, Parkinson's, Alzheimer's, spinal cord injury)
• Various interventions (pharmacological, cell therapy, exercise)
• Multiple outcome types (behavioral, histological, molecular)
• Range of publication years (2015-2024)

Implementation Plan

Phase 1: Infrastructure (Low Risk)

1. Add new GUIDs to SeedDataConstants.cs
2. Create SeedDataOwnershipTransfer.cs class
3. Add TransferOwnership and AddDirectMembership methods to Project
4. Integrate with UserHasRegisteredHandler

Phase 2: Multi-Project Seeding (Medium Risk)

1. Expand DatabaseSeeder with project creation methods
2. Create seed investigators (Alpha, Beta reviewers)
3. Add project memberships for seed reviewers
4. Configure stages and agreement modes

Phase 3: Screening Decisions (Medium Risk)

1. Create screening decisions for Screening In Progress project
2. Calculate and store agreement metrics
3. Mark studies with appropriate ScreeningInfo states

Phase 4: Annotation Data (Higher Complexity)

1. Add annotation questions to projects
2. Create annotation sessions
3. Populate extraction data for Complete Review project

Phase 5: Extended Sample Data (Content)

1. Expand SampleStudies.json to 50+ studies
2. Ensure variety in study characteristics
3. Add realistic abstracts

Testing Strategy

1. Unit Tests: Test ownership transfer logic in isolation
2. Integration Tests: Verify seeding creates valid database state
3. E2E Tests: Confirm first user can access seeded projects
4. Manual Testing: Verify all workflow states are reachable

Rollback Plan

If seeding causes issues:

1. Set SYRF_SEED_DATA_ENABLED=false in environment
2. Delete seed data using cleanup methods
3. Revert to previous seeder version

Success Criteria

1. First user immediately sees 5 projects in their dashboard
2. User can screen studies in "Quick Start Demo"
3. User can view progress in "Screening In Progress"
4. User can annotate studies in "Ready for Annotation"
5. User can export data from "Complete Review"
6. User must request access to "Private Research"

Related Documentation

• MongoDB Serialization Architecture
• MongoDB Testing Strategy
• Database Seeding Best Practices

Appendix: Research References

Best practices consulted:

• Cypress Database Seeding
• Neon: Database Testing with Fixtures
• Uffizzi: Database Seeding for Preview Environments
• Coherence: Test Environment Management