OrchidApp is a self-hosted web application for managing an orchid collection with database-enforced lifecycle integrity.
It is a production-grade web application backed by a rigorously designed, migration-controlled and operationally validated MariaDB database schema.
The system is deliberately opinionated. Invariants live in the database. Behaviour lives in the application. Enforcement lives in automation.
- ASP.NET Core Razor Pages (.NET LTS)
- EF Core for atomic entities
- Stored procedures for structural entities
- MariaDB (Linux) authoritative environment
- Deterministic migration system with checksum enforcement
- Automated nightly encrypted backups (database + uploads)
- Restore process validated
- Mobile-first UI design
- Deterministic deployment model (systemd + environment file)
OrchidApp is deployed and operational on Raspberry Pi (Linux).
The system is layered intentionally:
- Database layer --- enforces invariants and lifecycle rules
- Application layer --- orchestrates valid workflows
- Automation layer --- enforces reproducibility and drift detection
- Operations layer --- backup, restore and deployment discipline
No layer may weaken the guarantees of another.
Environment Platform Configuration
Development Windows PC appsettings.Development.json
Production Raspberry Pi systemd + /etc/orchidapp/orchidapp.env
Production never depends on Development configuration.
Secrets are never committed to Git.
The production connection string is supplied via a systemd
EnvironmentFile, not via JSON configuration.
MariaDB running on Linux is the authoritative validator for:
- Identifier casing
- Collation behaviour
- Stored procedure parsing
Windows MySQL behaviour must not be relied upon.
- The schema is treated as source code.
- Every committed version can be rebuilt from scratch.
- Drift between environments is detected automatically.
- Structural invariants are enforced in the database, not the application.
All structural schema changes are implemented via deterministic migration files:
database/migrations/
Each migration file:
- Follows naming convention
YYYYMMDDHHMM_Name.sql - Is applied exactly once
- Is recorded in the
schemaversiontable - Has its SHA256 checksum stored and enforced
The system prevents:
- Out-of-order migrations
- Duplicate timestamps
- Silent modification of historical migrations
- Schema drift prior to migration execution
Migrations are applied by piping directly into mysql, mirroring
production behaviour.
The production database must never be modified outside the migration system.
Schema files under:
database/schema/
are generated artefacts.
They:
- Represent the full assembled schema
- Must never be edited manually
- Are regenerated deterministically
- Are validated in CI
Pre-commit hooks enforce export and validation. Bypassing hooks is not permitted.
A plant has a single immutable lifecycle:
startDateTime → endDateTime
Rules:
- A split ends a lifecycle and creates new plants
- A plant may be split at most once
- Propagation creates new plants without ending the parent
- A plant may have at most one propagation record
- Location history enforces strict temporal adjacency
- Structural lifecycle changes are executed exclusively via stored procedures
These invariants are database-enforced and non-negotiable.
- Photos are attached only via Observation events
- Photos are stored in
plantphoto - Each photo belongs to exactly one Observation
- Hero image selection is explicit (
isHeroflag) - At most one active hero image per plant
- No automatic "latest photo" inference
Heavy photo loading is isolated to dedicated pages.
- Atomic entities (e.g.
plantevent) may be written via EF Core - Temporal or structural entities (e.g.
plantlocationhistory,plantsplit, propagation) must be written via stored procedures
Stored procedure invocation must follow the project execution contract defined in:
docs/architecture.md
This defines the permitted EF Core invocation patterns, asynchronous execution requirements and error-handling boundaries for database operations.
- Triggers may enforce absolute invariants but must not implement domain behaviour
- ASP.NET Core Razor Pages
- Mobile-first UI patterns
- Explicit startup validation for required configuration
- Environment-based configuration (Development / Production)
- Stored procedures invoked where structural invariants are required
- Database treated as authoritative
The application must not reinterpret or override database rules.
Deployment is deterministic and consists only of:
git pull
dotnet publish -c Release -o ./publish
sudo systemctl restart orchidapp
If additional manual steps are required, the deployment model is broken and must be corrected.
Full installation and upgrade instructions are defined in:
docs/installation-upgrade.md
Nightly automated backup system:
- MariaDB snapshot
(
mysqldump --single-transaction --routines --triggers) - gzip compression
- Encrypted via rclone crypt
- Uploaded to OneDrive
- 14-day retention (database)
- Encrypted mirror for uploads folder
- Quarterly restore validation required
Backups are only considered valid if restore tests succeed.
After restore:
SELECT scriptName FROM schemaversion ORDER BY appliedAt;
Migration history must match repository state.
Application binaries are rebuilt from Git. Only database and uploads are stateful.
After cloning:
pwsh scripts/setup.ps1
This configures tooling and installs enforced Git hooks.
Local CI validation:
pwsh scripts/ci-local.ps1
If it fails locally, it will fail in CI.
- A strict, reproducible, production-ready system
- A learning and reference implementation
- Designed for correctness over convenience
- A rapid prototyping sandbox
- Tolerant of undocumented manual changes
- Flexible about bypassing enforcement
Invariants live in the database.
Behaviour lives in the application.
Enforcement lives in automation.
Everything else follows from that.