localgreenchain/docs/DATABASE.md

# LocalGreenChain Database Integration

This document describes the PostgreSQL database integration for LocalGreenChain, implemented as part of Agent 2's deployment.

## Overview

The database layer provides persistent storage for all LocalGreenChain entities using PostgreSQL and Prisma ORM. This replaces the previous file-based JSON storage with a robust, scalable database solution.

## Quick Start

### 1. Install Dependencies

```bash
bun install
```

### 2. Configure Database

Copy the environment template and configure your database connection:

```bash
cp .env.example .env
```

Edit `.env` and set your PostgreSQL connection string:

```env
DATABASE_URL="postgresql://user:password@localhost:5432/localgreenchain?schema=public"
```

### 3. Generate Prisma Client

```bash
bun run db:generate
```

### 4. Run Migrations

For development:
```bash
bun run db:migrate
```

For production:
```bash
bun run db:migrate:prod
```

### 5. Seed Database (Optional)

```bash
bun run db:seed
```

## Database Schema

The schema includes the following main entities:

### Core Entities

| Entity | Description |
|--------|-------------|
| `User` | User accounts with authentication and profiles |
| `Plant` | Plant records with lineage tracking |
| `TransportEvent` | Supply chain transport events |
| `SeedBatch` | Seed batch tracking |
| `HarvestBatch` | Harvest batch records |

### Vertical Farming

| Entity | Description |
|--------|-------------|
| `VerticalFarm` | Vertical farm facilities |
| `GrowingZone` | Individual growing zones |
| `CropBatch` | Active crop batches |
| `GrowingRecipe` | Growing recipes/protocols |
| `ResourceUsage` | Energy and resource tracking |
| `FarmAnalytics` | Farm performance analytics |

### Demand & Market

| Entity | Description |
|--------|-------------|
| `ConsumerPreference` | Consumer food preferences |
| `DemandSignal` | Aggregated demand signals |
| `SupplyCommitment` | Grower supply commitments |
| `MarketMatch` | Matched supply and demand |
| `SeasonalPlan` | Grower seasonal plans |
| `DemandForecast` | Demand predictions |
| `PlantingRecommendation` | Planting recommendations |

### Audit & Blockchain

| Entity | Description |
|--------|-------------|
| `AuditLog` | System audit trail |
| `BlockchainBlock` | Blockchain block storage |

## Usage

### Importing the Database Layer

```typescript
import * as db from '@/lib/db';
// or import specific functions
import { createPlant, getPlantById, getPlantLineage } from '@/lib/db';
```

### Common Operations

#### Users

```typescript
// Create a user
const user = await db.createUser({
  email: 'grower@example.com',
  name: 'John Farmer',
  userType: 'GROWER',
  city: 'San Francisco',
  country: 'USA',
});

// Get user by email
const user = await db.getUserByEmail('grower@example.com');

// Get user with their plants
const userWithPlants = await db.getUserWithPlants(userId);
```

#### Plants

```typescript
// Create a plant
const plant = await db.createPlant({
  commonName: 'Cherry Tomato',
  scientificName: 'Solanum lycopersicum',
  plantedDate: new Date(),
  latitude: 37.7749,
  longitude: -122.4194,
  ownerId: userId,
});

// Clone a plant
const clone = await db.clonePlant(parentId, newOwnerId, 'CLONE');

// Get plant lineage
const lineage = await db.getPlantLineage(plantId);
// Returns: { plant, ancestors, descendants, siblings }

// Find nearby plants
const nearby = await db.getNearbyPlants({
  latitude: 37.7749,
  longitude: -122.4194,
  radiusKm: 10,
});
```

#### Transport Events

```typescript
// Create a transport event
const event = await db.createTransportEvent({
  eventType: 'DISTRIBUTION',
  fromLatitude: 37.8044,
  fromLongitude: -122.2712,
  fromLocationType: 'VERTICAL_FARM',
  toLatitude: 37.7849,
  toLongitude: -122.4094,
  toLocationType: 'MARKET',
  durationMinutes: 25,
  transportMethod: 'ELECTRIC_TRUCK',
  senderId: farmerId,
  receiverId: distributorId,
});

// Get plant journey
const journey = await db.getPlantJourney(plantId);

// Get environmental impact
const impact = await db.getEnvironmentalImpact({ userId });
```

#### Vertical Farms

```typescript
// Create a farm
const farm = await db.createVerticalFarm({
  name: 'Urban Greens',
  ownerId: userId,
  latitude: 37.8044,
  longitude: -122.2712,
  address: '123 Farm St',
  city: 'Oakland',
  country: 'USA',
  specs: { totalAreaSqm: 500, numberOfLevels: 5 },
});

// Create a growing zone
const zone = await db.createGrowingZone({
  name: 'Zone A',
  farmId: farm.id,
  level: 1,
  areaSqm: 80,
  growingMethod: 'NFT',
  plantPositions: 400,
});

// Create a crop batch
const batch = await db.createCropBatch({
  farmId: farm.id,
  zoneId: zone.id,
  cropType: 'Lettuce',
  plantCount: 400,
  plantingDate: new Date(),
  expectedHarvestDate: new Date(Date.now() + 28 * 24 * 60 * 60 * 1000),
  expectedYieldKg: 60,
});
```

#### Demand & Market

```typescript
// Set consumer preferences
await db.upsertConsumerPreference({
  consumerId: userId,
  latitude: 37.7849,
  longitude: -122.4094,
  preferredCategories: ['leafy_greens', 'herbs'],
  certificationPreferences: ['organic', 'local'],
});

// Create supply commitment
const commitment = await db.createSupplyCommitment({
  growerId: farmerId,
  produceType: 'Butterhead Lettuce',
  committedQuantityKg: 60,
  availableFrom: new Date(),
  availableUntil: new Date(Date.now() + 30 * 24 * 60 * 60 * 1000),
  pricePerKg: 8.5,
  deliveryRadiusKm: 25,
  deliveryMethods: ['grower_delivery', 'customer_pickup'],
});

// Find matching supply
const matches = await db.findMatchingSupply(
  'Lettuce',
  { latitude: 37.7849, longitude: -122.4094, radiusKm: 20 },
  new Date()
);
```

### Using the Database-Backed Blockchain

The database layer includes a `PlantChainDB` class that provides blockchain functionality with PostgreSQL persistence:

```typescript
import { getBlockchainDB } from '@/lib/blockchain/manager';

// Get the database-backed blockchain
const chain = await getBlockchainDB();

// Register a plant (creates both DB record and blockchain block)
const { plant, block } = await chain.registerPlant({
  commonName: 'Tomato',
  latitude: 37.7749,
  longitude: -122.4194,
  ownerId: userId,
});

// Clone a plant
const { plant: clone, block: cloneBlock } = await chain.clonePlant(
  parentId,
  newOwnerId,
  'CLONE'
);

// Verify blockchain integrity
const isValid = await chain.isChainValid();
```

## NPM Scripts

| Script | Description |
|--------|-------------|
| `bun run db:generate` | Generate Prisma client |
| `bun run db:push` | Push schema to database (dev) |
| `bun run db:migrate` | Create and run migration (dev) |
| `bun run db:migrate:prod` | Run migrations (production) |
| `bun run db:seed` | Seed database with test data |
| `bun run db:studio` | Open Prisma Studio GUI |

## Architecture

```
lib/db/
├── prisma.ts      # Prisma client singleton
├── types.ts       # Type definitions and utilities
├── users.ts       # User CRUD operations
├── plants.ts      # Plant operations with lineage
├── transport.ts   # Transport event operations
├── farms.ts       # Vertical farm operations
├── demand.ts      # Demand and market operations
├── audit.ts       # Audit logging and blockchain
└── index.ts       # Central exports

prisma/
├── schema.prisma  # Database schema
└── seed.ts        # Seed script
```

## Migration from File Storage

If you have existing data in JSON files, you can migrate to the database:

1. Ensure database is configured and migrations are run
2. Load existing JSON data
3. Use the database service layer to insert records
4. Verify data integrity
5. Remove old JSON files

## Performance Considerations

- The schema includes strategic indexes on frequently queried fields
- Pagination is supported for large result sets
- Location-based queries use in-memory filtering (consider PostGIS for large scale)
- Blockchain integrity verification scans all blocks (cache results for performance)

## Troubleshooting

### Connection Issues

```bash
# Test database connection
bunx prisma db pull
```

### Migration Issues

```bash
# Reset database (WARNING: deletes all data)
bunx prisma migrate reset

# Generate new migration
bunx prisma migrate dev --name your_migration_name
```

### Type Issues

```bash
# Regenerate Prisma client
bun run db:generate
```

## Security Notes

- Never commit `.env` files with real credentials
- Use environment variables for all sensitive configuration
- Database user should have minimal required permissions
- Enable SSL for production database connections

---

*Implemented by Agent 2 - Database Integration*