Datenbank-Schema - Nachbarschaftshilfe-App

Version: 1.0
Datum: 2026-06-05
Autor: CTO
Status: Initial

Inhaltsverzeichnis

1. Überblick
2. Technologie-Entscheidung
3. Datenmodell
4. Schema-Definition
5. Indexes & Performance
6. Constraints & Validierung
7. Migrations-Strategie
8. Backup & Recovery

Überblick

Die Nachbarschaftshilfe-App nutzt PostgreSQL 16 mit PostGIS-Extension für geo-räumliche Queries. Das Schema ist für ACID-Garantien bei Punktetransaktionen optimiert und skaliert horizontal über Read Replicas.

Kernentitäten

• Users: Nutzerprofile mit Standort und Punktestand
• Tasks: Aufgaben mit Geo-Location und Status
• PointTransactions: Immutable Transaction Log für Punkte
• Partners: Werbepartner für Rabatte
• Redemptions: Eingelöste Punkte-Rabatte

Technologie-Entscheidung

PostgreSQL 16 mit PostGIS

Vorteile:

• ACID-Transaktionen für konsistente Punkteübertragungen
• PostGIS Extension für Geo-Queries (Nachbarschaftssuche)
• JSONB für flexible Metadaten
• Hervorragende Performance mit Indexes
• Mature Ecosystem (TypeORM, Knex, etc.)

Alternative:

• MongoDB: Flexibler, aber keine ACID-Garantien für Transaktionen
• MySQL: Ähnlich wie PostgreSQL, aber schwächerer Geo-Support

Datenmodell

Entity-Relationship-Diagramm

┌──────────────┐
│    USERS     │
│              │
│ - id (PK)    │
│ - email      │
│ - location   │◄────┐
│ - points     │     │
└──────────────┘     │
       │             │
       │ creates     │ helps_with
       │             │
       ▼             │
┌──────────────┐     │
│    TASKS     │─────┘
│              │
│ - id (PK)    │
│ - creator_id │
│ - helper_id  │
│ - location   │
│ - status     │
│ - points     │
└──────────────┘
       │
       │ generates
       │
       ▼
┌──────────────────────┐
│ POINT_TRANSACTIONS   │
│                      │
│ - id (PK)           │
│ - user_id (FK)      │
│ - amount            │
│ - task_id (FK)      │
│ - redemption_id (FK)│
└──────────────────────┘
       │
       │ consumes (bei Einlösung)
       │
       ▼
┌──────────────┐         ┌──────────────┐
│ REDEMPTIONS  │─────────│   PARTNERS   │
│              │ offers  │              │
│ - id (PK)    │         │ - id (PK)    │
│ - user_id    │         │ - name       │
│ - partner_id │◄────────│ - logo_url   │
│ - points     │         └──────────────┘
└──────────────┘

Datenfluss: Task-Completion

1. Helper erstellt Task → Task.status = 'open'
2. Creator akzeptiert Helper → Task.status = 'assigned', Task.helper_id = helper.id
3. Helper markiert als fertig → Task.status = 'pending_confirmation'
4. Creator bestätigt:
   a. Task.status = 'completed'
   b. BEGIN TRANSACTION
      - INSERT INTO point_transactions (user_id, amount, type, task_id)
      - UPDATE users SET points_balance = points_balance + amount WHERE id = helper_id
   c. COMMIT TRANSACTION

Schema-Definition

1. Users Table

CREATE TABLE users (
  -- Identifikation
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255),  -- NULL für OAuth-only Users
  
  -- Profil
  name VARCHAR(255) NOT NULL,
  avatar_url VARCHAR(500),
  bio TEXT,
  phone_number VARCHAR(50),
  
  -- Standort (PostGIS)
  location GEOGRAPHY(POINT),  -- Lat/Lng als Geography
  address VARCHAR(500),       -- Human-readable Adresse
  
  -- Punktesystem
  points_balance INTEGER DEFAULT 0 CHECK (points_balance >= 0),
  
  -- OAuth
  oauth_provider VARCHAR(50),   -- 'google', 'apple', NULL
  oauth_id VARCHAR(255),
  
  -- Verifizierung & Trust
  email_verified BOOLEAN DEFAULT FALSE,
  phone_verified BOOLEAN DEFAULT FALSE,
  trust_score DECIMAL(3,2) DEFAULT 0.00,  -- 0.00 - 5.00
  
  -- Status
  is_active BOOLEAN DEFAULT TRUE,
  is_banned BOOLEAN DEFAULT FALSE,
  banned_reason TEXT,
  banned_at TIMESTAMP,
  
  -- Timestamps
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  last_login_at TIMESTAMP
);

-- Indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_location ON users USING GIST(location);
CREATE INDEX idx_users_oauth ON users(oauth_provider, oauth_id);
CREATE INDEX idx_users_active ON users(is_active) WHERE is_active = TRUE;

-- Trigger für updated_at
CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

2. Tasks Table

CREATE TABLE tasks (
  -- Identifikation
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Beziehungen
  creator_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  helper_id UUID REFERENCES users(id) ON DELETE SET NULL,
  
  -- Beschreibung
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  category VARCHAR(100),  -- 'handwerk', 'garten', 'technik', 'transport', 'sonstiges'
  
  -- Standort (PostGIS)
  location GEOGRAPHY(POINT) NOT NULL,
  address VARCHAR(500),
  
  -- Bilder
  image_urls TEXT[],  -- Array von S3 URLs
  
  -- Punktesystem
  points_reward INTEGER NOT NULL CHECK (points_reward > 0 AND points_reward <= 1000),
  
  -- Status & Lifecycle
  status VARCHAR(50) NOT NULL DEFAULT 'open',
    -- 'open', 'assigned', 'in_progress', 'pending_confirmation', 'completed', 'cancelled'
  
  -- Zeitplanung
  created_at TIMESTAMP DEFAULT NOW(),
  assigned_at TIMESTAMP,
  started_at TIMESTAMP,
  completed_at TIMESTAMP,
  expires_at TIMESTAMP,  -- Optional: Auto-close nach X Tagen
  
  -- Metadaten
  metadata JSONB,  -- Flexible zusätzliche Daten
  
  -- Timestamps
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_tasks_location ON tasks USING GIST(location);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_creator ON tasks(creator_id);
CREATE INDEX idx_tasks_helper ON tasks(helper_id);
CREATE INDEX idx_tasks_category ON tasks(category);
CREATE INDEX idx_tasks_created ON tasks(created_at DESC);
CREATE INDEX idx_tasks_status_location ON tasks(status, location) WHERE status = 'open';

-- Composite Index für häufige Query: offene Tasks in der Nähe
CREATE INDEX idx_tasks_open_nearby ON tasks 
  USING GIST(location) 
  WHERE status = 'open';

-- Trigger für updated_at
CREATE TRIGGER update_tasks_updated_at
  BEFORE UPDATE ON tasks
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

3. PointTransactions Table

CREATE TABLE point_transactions (
  -- Identifikation
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Beziehungen
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  task_id UUID REFERENCES tasks(id) ON DELETE SET NULL,
  redemption_id UUID REFERENCES redemptions(id) ON DELETE SET NULL,
  
  -- Transaktion
  amount INTEGER NOT NULL,  -- Positive = verdient, Negative = ausgegeben
  type VARCHAR(50) NOT NULL,
    -- 'task_completed', 'redemption', 'bonus', 'refund', 'admin_adjustment'
  
  -- Beschreibung
  description TEXT,
  
  -- Metadaten
  metadata JSONB,  -- z.B. Admin-Notes, Refund-Reason, etc.
  
  -- Timestamps (immutable)
  created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_point_transactions_user ON point_transactions(user_id, created_at DESC);
CREATE INDEX idx_point_transactions_task ON point_transactions(task_id);
CREATE INDEX idx_point_transactions_type ON point_transactions(type);
CREATE INDEX idx_point_transactions_created ON point_transactions(created_at DESC);

-- Partial Index für Performance: Nur positive Transaktionen (für Leaderboards)
CREATE INDEX idx_point_transactions_earnings ON point_transactions(user_id, amount)
  WHERE amount > 0;

4. Partners Table

CREATE TABLE partners (
  -- Identifikation
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Profil
  name VARCHAR(255) NOT NULL,
  logo_url VARCHAR(500),
  description TEXT,
  website_url VARCHAR(500),
  
  -- Kontakt
  contact_email VARCHAR(255),
  contact_phone VARCHAR(50),
  
  -- Status
  is_active BOOLEAN DEFAULT TRUE,
  
  -- Metadaten
  metadata JSONB,  -- API-Keys, Redemption-Logik, etc.
  
  -- Timestamps
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_partners_active ON partners(is_active) WHERE is_active = TRUE;
CREATE INDEX idx_partners_name ON partners(name);

-- Trigger für updated_at
CREATE TRIGGER update_partners_updated_at
  BEFORE UPDATE ON partners
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

5. Redemptions Table

CREATE TABLE redemptions (
  -- Identifikation
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Beziehungen
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  partner_id UUID NOT NULL REFERENCES partners(id) ON DELETE RESTRICT,
  
  -- Transaktion
  points_spent INTEGER NOT NULL CHECK (points_spent > 0),
  
  -- Rabatt-Details
  discount_code VARCHAR(100),
  discount_description TEXT,
  discount_value DECIMAL(10,2),  -- z.B. 5.00 für "5€ Rabatt"
  discount_type VARCHAR(50),     -- 'percentage', 'fixed_amount', 'free_item'
  
  -- Status
  status VARCHAR(50) DEFAULT 'active',  -- 'active', 'used', 'expired', 'cancelled'
  used_at TIMESTAMP,
  
  -- Zeitstempel
  redeemed_at TIMESTAMP DEFAULT NOW(),
  expires_at TIMESTAMP,
  
  -- Metadaten
  metadata JSONB  -- Partner-spezifische Daten
);

-- Indexes
CREATE INDEX idx_redemptions_user ON redemptions(user_id, redeemed_at DESC);
CREATE INDEX idx_redemptions_partner ON redemptions(partner_id);
CREATE INDEX idx_redemptions_status ON redemptions(status);
CREATE INDEX idx_redemptions_expires ON redemptions(expires_at) WHERE status = 'active';

6. Ratings Table (User Bewertungen)

CREATE TABLE ratings (
  -- Identifikation
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Beziehungen
  task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  rater_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,      -- Wer bewertet
  ratee_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,      -- Wer wird bewertet
  
  -- Bewertung
  score INTEGER NOT NULL CHECK (score >= 1 AND score <= 5),
  comment TEXT,
  
  -- Timestamps
  created_at TIMESTAMP DEFAULT NOW(),
  
  -- Constraints
  UNIQUE(task_id, rater_id)  -- Pro Task kann jeder User nur 1x bewerten
);

-- Indexes
CREATE INDEX idx_ratings_ratee ON ratings(ratee_id, created_at DESC);
CREATE INDEX idx_ratings_task ON ratings(task_id);
CREATE INDEX idx_ratings_score ON ratings(score);

7. Helper Functions

-- Funktion für updated_at Trigger
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Funktion für Geo-Distance Berechnung (in Metern)
CREATE OR REPLACE FUNCTION calculate_distance(
  location1 GEOGRAPHY,
  location2 GEOGRAPHY
) RETURNS NUMERIC AS $$
BEGIN
  RETURN ST_Distance(location1, location2);
END;
$$ LANGUAGE plpgsql;

-- Funktion für User Trust Score Update
CREATE OR REPLACE FUNCTION update_user_trust_score(user_uuid UUID)
RETURNS VOID AS $$
DECLARE
  avg_rating DECIMAL(3,2);
  total_ratings INTEGER;
BEGIN
  SELECT 
    COALESCE(AVG(score), 0),
    COUNT(*)
  INTO avg_rating, total_ratings
  FROM ratings
  WHERE ratee_id = user_uuid;
  
  -- Trust Score Formel: (avg_rating * log(total_ratings + 1)) / log(11)
  -- Mehr Ratings = höheres Gewicht, aber logarithmisch
  UPDATE users
  SET trust_score = LEAST(5.0, (avg_rating * ln(total_ratings + 1)) / ln(11))
  WHERE id = user_uuid;
END;
$$ LANGUAGE plpgsql;

Indexes & Performance

Strategie

1. Geo-Indexes (GIST): Für räumliche Queries (Nachbarschaftssuche)
2. B-Tree Indexes: Für Equality/Range Queries (Status, Timestamps)
3. Partial Indexes: Nur für häufige Filter (z.B. WHERE status = 'open')
4. Composite Indexes: Für Multi-Column Queries

Wichtigste Performance-Queries

1. Nearby Open Tasks

-- Query
SELECT * FROM tasks
WHERE status = 'open'
  AND ST_DWithin(
    location,
    ST_MakePoint(13.405, 52.52)::geography,
    5000  -- 5km Radius
  )
ORDER BY created_at DESC
LIMIT 20;

-- Index
CREATE INDEX idx_tasks_open_nearby ON tasks 
  USING GIST(location) 
  WHERE status = 'open';

2. User Point History

-- Query
SELECT * FROM point_transactions
WHERE user_id = 'user-uuid'
ORDER BY created_at DESC
LIMIT 50;

-- Index
CREATE INDEX idx_point_transactions_user ON point_transactions(user_id, created_at DESC);

3. Leaderboard (Top Earners)

-- Query
SELECT 
  u.id,
  u.name,
  u.points_balance,
  COUNT(pt.id) as tasks_completed
FROM users u
LEFT JOIN point_transactions pt ON pt.user_id = u.id AND pt.type = 'task_completed'
WHERE u.is_active = TRUE
GROUP BY u.id
ORDER BY u.points_balance DESC
LIMIT 100;

-- Indexes
CREATE INDEX idx_users_points ON users(points_balance DESC) WHERE is_active = TRUE;
CREATE INDEX idx_point_transactions_earnings ON point_transactions(user_id, amount)
  WHERE amount > 0;

Query-Optimierung Best Practices

1. EXPLAIN ANALYZE für jede neue Query
2. Connection Pooling: PgBouncer (100-200 Connections)
3. Prepared Statements: TypeORM/Knex nutzen Prepared Statements
4. N+1 Prevention: DataLoader oder Eager Loading
5. Slow Query Log: Aktivieren und monitoren (>100ms)

Constraints & Validierung

Database-Level Constraints

-- Punkte dürfen nicht negativ sein
ALTER TABLE users
  ADD CONSTRAINT check_points_non_negative 
  CHECK (points_balance >= 0);

-- Points Reward Limit
ALTER TABLE tasks
  ADD CONSTRAINT check_points_reward_limit
  CHECK (points_reward > 0 AND points_reward <= 1000);

-- Rating Score 1-5
ALTER TABLE ratings
  ADD CONSTRAINT check_rating_score
  CHECK (score >= 1 AND score <= 5);

-- Status Enum (PostgreSQL native)
CREATE TYPE task_status AS ENUM (
  'open', 'assigned', 'in_progress', 'pending_confirmation', 'completed', 'cancelled'
);

ALTER TABLE tasks
  ALTER COLUMN status TYPE task_status USING status::task_status;

Application-Level Validierung

// TypeORM Entity Example
@Entity('users')
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ unique: true })
  @IsEmail()
  email: string;

  @Column()
  @MinLength(2)
  @MaxLength(255)
  name: string;

  @Column({ type: 'integer', default: 0 })
  @Min(0)
  points_balance: number;

  @Column({ type: 'geography', spatialFeatureType: 'Point', srid: 4326, nullable: true })
  location: Point;
}

Migrations-Strategie

Tools & Workflow

Tool: TypeORM Migrations

# Migration erstellen
npm run migration:create -- -n AddRatingsTable

# Migration generieren (aus Entity-Changes)
npm run migration:generate -- -n UpdateUserSchema

# Migrations ausführen
npm run migration:run

# Rollback (letzte Migration)
npm run migration:revert

Migration Best Practices

1. Reihenfolge

1. CREATE TABLE (neue Tabellen)
2. ALTER TABLE ADD COLUMN (neue Spalten mit NULL oder DEFAULT)
3. Daten-Migration (Populate neue Spalten)
4. ALTER TABLE ALTER COLUMN (NOT NULL Constraints hinzufügen)
5. CREATE INDEX (Indexes hinzufügen)
6. ALTER TABLE ADD CONSTRAINT (Foreign Keys, Checks)

2. Zero-Downtime Migrations

Problem: Tabellen-Locks während Migrations können Downtime verursachen.

Lösung: Rolling Deployment mit kompatiblen Migrations

Phase 1: Backwards-Compatible Schema Change

-- Migration 001: Spalte hinzufügen (nullable)
ALTER TABLE users ADD COLUMN phone_number VARCHAR(50);
CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone_number);

Deploye neue App-Version, die phone_number schreibt.

Phase 2: Daten-Migration

-- Migration 002: Daten migrieren
UPDATE users SET phone_number = extract_phone_from_bio(bio) WHERE phone_number IS NULL;

Phase 3: Constraint hinzufügen

-- Migration 003: NOT NULL Constraint
ALTER TABLE users ALTER COLUMN phone_number SET NOT NULL;

3. Große Tabellen Migration

Problem: ALTER TABLE auf Millionen Rows ist langsam.

Lösung: Batched Updates

-- Statt:
UPDATE tasks SET new_column = compute_value(old_column);

-- Besser:
DO $$
DECLARE
  batch_size INTEGER := 10000;
  offset_val INTEGER := 0;
  updated INTEGER;
BEGIN
  LOOP
    UPDATE tasks
    SET new_column = compute_value(old_column)
    WHERE id IN (
      SELECT id FROM tasks 
      WHERE new_column IS NULL 
      LIMIT batch_size
    );
    
    GET DIAGNOSTICS updated = ROW_COUNT;
    EXIT WHEN updated = 0;
    
    RAISE NOTICE 'Processed % rows', batch_size;
    PERFORM pg_sleep(0.1);  -- Kurze Pause zwischen Batches
  END LOOP;
END $$;

4. Index ohne Lock

-- Statt:
CREATE INDEX idx_users_email ON users(email);

-- Besser (PostgreSQL 11+):
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Wichtig: CONCURRENTLY kann nicht in Transaktionen verwendet werden.

Migration-Rollback-Strategie

// Migration mit Up & Down
export class AddRatingsTable1712345678901 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      CREATE TABLE ratings (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        task_id UUID NOT NULL REFERENCES tasks(id),
        rater_id UUID NOT NULL REFERENCES users(id),
        ratee_id UUID NOT NULL REFERENCES users(id),
        score INTEGER NOT NULL CHECK (score >= 1 AND score <= 5),
        comment TEXT,
        created_at TIMESTAMP DEFAULT NOW(),
        UNIQUE(task_id, rater_id)
      )
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP TABLE ratings CASCADE`);
  }
}

Migration-Testing

# Lokal testen
npm run migration:run
npm run test:e2e
npm run migration:revert
npm run test:e2e

# Staging testen
npm run migration:run -- --env=staging
# Smoke Tests
npm run migration:revert -- --env=staging

Backup & Recovery

Backup-Strategie

1. Automated Backups (AWS RDS)

- Point-in-Time Recovery: 7 Tage
- Automated Snapshots: Täglich um 03:00 UTC
- Snapshot Retention: 30 Tage
- Multi-AZ: Ja (Automatic Failover)

2. Manual Snapshots

# Vor großen Migrations
aws rds create-db-snapshot \
  --db-instance-identifier neighbor-prod \
  --db-snapshot-identifier neighbor-prod-pre-migration-2026-06-05

3. Logical Backups

# pg_dump für Datenexport
pg_dump -h $DB_HOST -U $DB_USER -d neighbor_prod \
  --format=custom \
  --file=neighbor-prod-$(date +%Y%m%d).dump

# Upload zu S3
aws s3 cp neighbor-prod-*.dump s3://backups/postgres/

Recovery-Szenarien

Szenario 1: Falsche Migration

# Rollback letzte Migration
npm run migration:revert

# Oder: Point-in-Time Recovery (AWS RDS Console)
# Restore zu Zeitpunkt vor Migration

Szenario 2: Daten-Korruption

# Restore aus Snapshot
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier neighbor-prod-restored \
  --db-snapshot-identifier neighbor-prod-pre-migration-2026-06-05

# Verify Data
# Switch DNS to restored instance

Szenario 3: Vollständiger Datenverlust

# Restore aus Snapshot + Apply Transaction Logs
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier neighbor-prod \
  --target-db-instance-identifier neighbor-prod-recovered \
  --restore-time 2026-06-05T14:30:00Z

Recovery-Time-Objective (RTO) & Recovery-Point-Objective (RPO)

Umgebung	RTO (Wiederherstellungszeit)	RPO (Datenverlust)
Development	1 Stunde	24 Stunden
Staging	30 Minuten	1 Stunde
Production	15 Minuten	5 Minuten

Monitoring & Maintenance

Monitoring-Queries

-- 1. Slow Queries (PostgreSQL Log)
SELECT 
  query,
  mean_exec_time,
  calls,
  total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- 2. Table Bloat
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 3. Index Usage
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 4. Connection Count
SELECT 
  datname,
  count(*) as connections
FROM pg_stat_activity
GROUP BY datname
ORDER BY connections DESC;

Maintenance Tasks

-- 1. VACUUM (regelmäßig, automatisch in AWS RDS)
VACUUM ANALYZE tasks;

-- 2. REINDEX (bei Index-Corruption)
REINDEX TABLE CONCURRENTLY tasks;

-- 3. Statistiken aktualisieren
ANALYZE tasks;

-- 4. Alte Daten archivieren (Soft-Delete)
UPDATE tasks
SET status = 'archived'
WHERE completed_at < NOW() - INTERVAL '1 year';

CloudWatch Alarms

Alarms:
  - Name: HighDatabaseCPU
    Metric: CPUUtilization
    Threshold: 80%
    Duration: 5 minutes
    Action: Scale up instance
  
  - Name: LowDatabaseStorage
    Metric: FreeStorageSpace
    Threshold: < 10 GB
    Action: Alert + Auto-scale storage
  
  - Name: HighConnectionCount
    Metric: DatabaseConnections
    Threshold: > 80% of max_connections
    Action: Alert + Check for connection leaks

Appendix

A. Beispiel-Queries

Nearby Tasks mit Distance

SELECT 
  t.id,
  t.title,
  t.points_reward,
  t.category,
  ST_Distance(
    t.location,
    ST_MakePoint(13.405, 52.52)::geography
  ) as distance_meters
FROM tasks t
WHERE 
  t.status = 'open'
  AND ST_DWithin(
    t.location,
    ST_MakePoint(13.405, 52.52)::geography,
    5000
  )
ORDER BY distance_meters ASC
LIMIT 20;

User Statistics

SELECT 
  u.id,
  u.name,
  u.points_balance,
  COUNT(DISTINCT t1.id) as tasks_created,
  COUNT(DISTINCT t2.id) as tasks_completed,
  COALESCE(AVG(r.score), 0) as avg_rating,
  COUNT(DISTINCT r.id) as rating_count
FROM users u
LEFT JOIN tasks t1 ON t1.creator_id = u.id
LEFT JOIN tasks t2 ON t2.helper_id = u.id AND t2.status = 'completed'
LEFT JOIN ratings r ON r.ratee_id = u.id
WHERE u.id = 'user-uuid'
GROUP BY u.id, u.name, u.points_balance;

Monthly Statistics

SELECT 
  DATE_TRUNC('month', created_at) as month,
  COUNT(*) as total_tasks,
  COUNT(*) FILTER (WHERE status = 'completed') as completed_tasks,
  SUM(points_reward) FILTER (WHERE status = 'completed') as total_points_distributed
FROM tasks
WHERE created_at >= NOW() - INTERVAL '12 months'
GROUP BY month
ORDER BY month DESC;

B. Schema-Version-History

Version	Datum	Änderungen
1.0	2026-06-05	Initial Schema (Users, Tasks, Points, Partners, Redemptions, Ratings)

C. Weitere Ressourcen

• PostgreSQL 16 Dokumentation
• PostGIS Dokumentation
• TypeORM Migrations Guide
• AWS RDS Best Practices

---

Nächste Schritte:

1. ✅ Schema dokumentiert
2. ⏭️ TypeORM Entities erstellen
3. ⏭️ Initial Migration generieren
4. ⏭️ Seed-Data für Development
5. ⏭️ Staging-Datenbank aufsetzen