SSH write pipeline:

cat /Users/bee/farm_update.sql | ssh -o StrictHostKeyChecking=no \
  -o UserKnownHostsFile=/dev/null root@192.168.8.221 \
  'pct exec 100 -- docker exec -i postgresql psql -U farmuser -d farmdb'

Direct psql access (from CT100):

ssh root@192.168.8.221 'pct exec 100 -- docker exec -it postgresql psql -U farmuser -d farmdb'

Postico 2 connection (works anywhere via NetBird):

34 tables organized around a central species catalog with category-specific extension tables.

Core Tables:

Operational Tables:

The species_images table tracks photos of species and individual plantings over time.

Image storage: /nvmepool/images/farm/ with subdirectories seed-packets/, plantings/, species/. Accessible via SMB share “Farm Images” on hpve.

Plant ID pipeline: The webhook at plantid.edmd.me (port 8902 on CT100) accepts plant photos, identifies them via Plant.id API, and automatically links or creates species in the database. iOS Shortcut available for phone camera → identify → database workflow.

The species table has 44 columns. Fields are populated via web research from multiple authoritative sources (NC State Extension, Ohio State Extension, Prairie Moon Nursery, Xerces Society, and others).

Identification: common_name, scientific_name, variety, category, description, native_status, hardiness_zone

Growing Conditions: sun_requirement, water_requirement, drought_tolerance, wet_feet_tolerance, deer_resistance, invasiveness_risk

Size & Spacing: ecological_layer, mature_height_ft, mature_spread_ft, spacing_inches, lifespan, growth_rate

Bloom & Pollinator: bloom_color, bloom_start_month, bloom_end_month, pollinator_value, pollinator_groups, host_plant_for, wildlife_value

Uses & Properties: edible, medicinal, nitrogen_fixer, primary_uses

Propagation & Care: propagation_methods, seed_collection_notes, cold_stratification_days, self_sows, fertilization_notes, pruning_notes, common_diseases, planting_guide

Reference: notes, sources (Ohio State Extension URLs), web_links (NC State Plant Toolbox URLs)

Planting Status Lifecycle: researching → planned → ordered → in_inventory → planted → established → propagating → dormant → dead → removed

Enum Types:

The farm-data MCP server exposes 19 tools for querying and managing the database from Claude Desktop and Claude Code.

Server location: ~/.mcp-servers/farm-data/server.py

Config in Claude Desktop: Add to mcpServers in claude_desktop_config.json:

"farm-data": {
  "command": "python3",
  "args": ["/Users/bee/.mcp-servers/farm-data/server.py"]
}

Note: farm_sql is read-only (SELECT only). All writes must go through the SSH pipeline or use specific farm_add_* / farm_log_* tools.

Automated plant identification via the Plant.id API, running as a systemd service on CT100.

Endpoints:

iOS Shortcut: “Identify Plant” — take photo → POST to webhook → shows result. Requires NetBird or home LAN.

Workflow: Photo → Plant.id API → species match against farmdb → auto-link image if match found, auto-create species if confidence >70%, flag for review if low confidence.

Photos saved to: /nvmepool/images/farm/plantings/

When adding new species or filling in missing data, the species-enrichment skill (~/skills/species-enrichment/SKILL.md) defines the full workflow:

1. Web research — search authoritative sources (NC State Extension, Ohio State Extension, Prairie Moon, GrowItBuildIt, Xerces Society, etc.). Never populate from a single source or training data alone.
2. Build SQL — generate UPDATE/INSERT statements with correct enum values.
3. Write to Mac — save SQL to /Users/bee/farm_update.sql via Filesystem:write_file.
4. Pipe via SSH — execute through the SSH pipeline to the PostgreSQL container.
5. Verify — spot-check with farm_get_species.

Each species gets:

• web_links — NC State Plant Toolbox URL (pattern: plants.ces.ncsu.edu/plants/{genus}-{species}/)
• sources — Ohio State Extension (Ohioline) fact sheet URL when available

A browser-based species catalog at farm.edmd.me — search, filter, and view full species detail pages.

Features: full-text search by common/scientific name, filter by category and native status, species detail pages with growing conditions, bloom timing, pollinator groups, wildlife value, and collection membership. Links out to NC State Plant Toolbox and Ohio State Extension.

Tana integration: Each species node in Tana’s Brownsville workspace has a web_url field linking to its detail page on farm.edmd.me. Collections are mirrored as Tana nodes with species as children.

The farm database and Tana Brownsville workspace are bidirectionally linked:

PostgreSQL → Tana (collections sync):

1. Query collections and their species from farmdb via farm_list_collections + farm_collection_species
2. Create/update collection nodes in Tana with species as children (via tana-local:import_tana_paste)
3. Each species child node in Tana gets the common name, scientific name, and a link to the web app

PostgreSQL → Tana (species web URLs):

• Species nodes in Tana’s Brownsville workspace have a web_url field
• After the web app deployed, all species nodes were updated with farm.edmd.me/species/{id} links
• Enables one-click from Tana research notes to the full species profile

Tana → PostgreSQL (research → enrichment):

• New species are researched in Tana (notes, sources, ecological context)
• Once ready, the species-enrichment skill creates SQL from the research
• SQL is piped via SSH to CT100’s PostgreSQL container
• The tana_node_id column on the species table links back to the Tana node

Key fields for the link:

• species.tana_node_id — Tana node UUID for the species
• species.web_links — NC State Plant Toolbox URL
• Tana species node web_url field — farm.edmd.me detail page URL

All 200 species are fully enriched with web-researched data across 15+ fields including pollinator groups, wildlife value, host plants, diseases, planting guides, and NC State Plant Toolbox links.