# Requirements: Field-Locking Architecture Test

## Projektziel

Mini-Testprojekt zur Validierung der Architektur für **feldbasiertes Locking** in einer Stammdaten-Anwendung mit mehreren gleichzeitigen Benutzern.

## Kontext

Die Anwendung verwaltet Klienten-Stammdaten in minimal normalisierten Tabellen (Stammdaten_1, etc.). Mehrere Benutzer arbeiten gleichzeitig an denselben Datensätzen. Es soll ein **feldbasiertes Locking** implementiert werden:

- Benutzer klickt in ein Feld → Feld wird für andere Benutzer gesperrt (ausgegraut)
- Benutzer verlässt Feld → Update in DB + Lock wird freigegeben
- Alle anderen Benutzer sehen das Update + freies Feld in Echtzeit

## Architektur-Entscheidungen

### Backend Stack
- **PostGraphile** für CRUD-Operationen
- **Custom PostGraphile Extensions** für Field-Lock Logik
- **PostgreSQL** für persistente Daten + LISTEN/NOTIFY für Broadcasts
- **Redis** für ephemere Field-Locks (in-memory, TTL)
- **BullMQ + Worker** (bereits vorhanden, für async Tasks)

### Frontend Stack
- **Vue.js 3** (Composition API)
- **Apollo Client** für GraphQL + Subscriptions (über WebSocket)
- **Eine WebSocket-Verbindung** managed alle Events (Daten-Updates + Lock-Events)

### Datenfluss

```
User Action (Focus Field)
    ↓
Apollo Mutation: lockField()
    ↓
PostGraphile Extension
    ↓
Redis SET (lock:table:id:field, userId, EX 300, NX)
    ↓
PostgreSQL pg_notify('field_lock_changed', {...})
    ↓
PostGraphile Subscription: fieldLockChanged
    ↓
Apollo Client (alle Clients mit aktivem Subscription)
    ↓
UI Update (Feld ausgegraut für andere User)
```

```
User Action (Blur Field + Value Changed)
    ↓
Apollo Mutation: updateFieldAndUnlock()
    ↓
PostGraphile Extension
    ↓
1. PostgreSQL UPDATE
    ↓
2. Redis DEL (lock:table:id:field)
    ↓
3. PostgreSQL pg_notify('stammdaten_updated', {...})
    ↓
4. PostgreSQL pg_notify('field_lock_changed', {...locked: false})
    ↓
PostGraphile Subscriptions (beide)
    ↓
Apollo Client Cache Update
    ↓
UI Update (neue Daten + Feld wieder editierbar)
```

## Technische Anforderungen

### 1. Datenbank-Schema

Erweitere die existierende PostgreSQL-Datenbank um:

```sql
-- Minimale Test-Tabelle für Stammdaten
CREATE TABLE stammdaten (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  adresse TEXT,
  telefon VARCHAR(50),
  email VARCHAR(255),
  notizen TEXT,
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  updated_by INTEGER
);

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

CREATE TRIGGER update_stammdaten_updated_at
  BEFORE UPDATE ON stammdaten
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- Test-Daten
INSERT INTO stammdaten (name, adresse, telefon, email, notizen) VALUES
  ('Max Mustermann', 'Musterstraße 1, 12345 Berlin', '030-12345678', 'max@example.com', 'Stammkunde seit 2020'),
  ('Erika Musterfrau', 'Beispielweg 42, 54321 Hamburg', '040-98765432', 'erika@example.com', 'Bevorzugt E-Mail-Kontakt'),
  ('Hans Schmidt', 'Testplatz 7, 10115 München', '089-11223344', 'hans.schmidt@example.com', 'Wichtiger VIP-Kunde');
```

**Wichtig:** Keine separate `field_locks` Tabelle! Locks nur in Redis.

### 2. PostGraphile Setup

Erweitere die bestehende PostGraphile-Konfiguration:

#### GraphQL Schema Extensions

```typescript
// postgraphile/plugins/fieldLockPlugin.ts

import { makeExtendSchemaPlugin, gql } from 'graphile-utils';
import Redis from 'ioredis';

const redis = new Redis({
  host: process.env.REDIS_HOST || 'localhost',
  port: parseInt(process.env.REDIS_PORT || '6379'),
});

// GraphQL Type Definitions
const typeDefs = gql`
  type FieldLockResult {
    success: Boolean!
    lockedBy: Int
    message: String
  }

  type FieldLockEvent {
    tableName: String!
    recordId: Int!
    fieldName: String!
    locked: Boolean!
    lockedBy: Int
    timestamp: String!
  }

  extend type Mutation {
    lockField(
      tableName: String!
      recordId: Int!
      fieldName: String!
    ): FieldLockResult

    updateFieldAndUnlock(
      id: Int!
      fieldName: String!
      value: String!
    ): Stammdatum
  }

  extend type Subscription {
    fieldLockChanged(
      tableName: String!
      recordId: Int!
    ): FieldLockEvent

    stammdatenUpdated(id: Int!): Stammdatum
  }
`;

// Resolver Implementations
const resolvers = {
  Mutation: {
    lockField: async (_, args, context) => {
      const { tableName, recordId, fieldName } = args;
      const userId = context.jwtClaims?.user_id || context.userId || 1; // Fallback für Test
      const lockKey = `lock:${tableName}:${recordId}:${fieldName}`;
      
      try {
        // Redis SET NX (only if not exists) mit 5 Min TTL
        const result = await redis.set(lockKey, userId, 'EX', 300, 'NX');
        
        if (result === 'OK') {
          // PostgreSQL NOTIFY
          await context.pgClient.query(
            "SELECT pg_notify('field_lock_changed', $1)",
            [JSON.stringify({
              tableName,
              recordId,
              fieldName,
              locked: true,
              lockedBy: userId,
              timestamp: new Date().toISOString()
            })]
          );
          
          return { success: true, lockedBy: userId, message: 'Field locked successfully' };
        } else {
          // Feld bereits gesperrt
          const currentOwner = await redis.get(lockKey);
          return { 
            success: false, 
            lockedBy: parseInt(currentOwner || '0'), 
            message: 'Field already locked by another user' 
          };
        }
      } catch (error) {
        console.error('Lock error:', error);
        return { success: false, message: 'Lock operation failed' };
      }
    },

    updateFieldAndUnlock: async (_, args, context) => {
      const { id, fieldName, value } = args;
      const lockKey = `lock:stammdaten:${id}:${fieldName}`;
      const userId = context.jwtClaims?.user_id || context.userId || 1;
      
      try {
        // 1. Verify lock ownership (optional, aber gut für Security)
        const lockOwner = await redis.get(lockKey);
        if (lockOwner && parseInt(lockOwner) !== userId) {
          throw new Error('Cannot update: field locked by another user');
        }
        
        // 2. DB Update (sanitize fieldName!)
        const allowedFields = ['name', 'adresse', 'telefon', 'email', 'notizen'];
        if (!allowedFields.includes(fieldName)) {
          throw new Error('Invalid field name');
        }
        
        const result = await context.pgClient.query(
          `UPDATE stammdaten 
           SET ${fieldName} = $1, updated_by = $2 
           WHERE id = $3 
           RETURNING *`,
          [value, userId, id]
        );
        
        // 3. Remove Lock
        await redis.del(lockKey);
        
        // 4. Notify: Data Update
        await context.pgClient.query(
          "SELECT pg_notify('stammdaten_updated', $1)",
          [JSON.stringify({ id, updatedFields: [fieldName] })]
        );
        
        // 5. Notify: Lock Released
        await context.pgClient.query(
          "SELECT pg_notify('field_lock_changed', $1)",
          [JSON.stringify({
            tableName: 'stammdaten',
            recordId: id,
            fieldName,
            locked: false,
            timestamp: new Date().toISOString()
          })]
        );
        
        return result.rows[0];
      } catch (error) {
        console.error('Update error:', error);
        throw error;
      }
    }
  },

  Subscription: {
    fieldLockChanged: {
      subscribe: async (_, args, context) => {
        const { tableName, recordId } = args;
        const channel = 'field_lock_changed';
        
        await context.pgClient.query(`LISTEN ${channel}`);
        
        return {
          [Symbol.asyncIterator]() {
            return {
              async next() {
                return new Promise((resolve) => {
                  context.pgClient.once('notification', (msg) => {
                    if (msg.channel === channel) {
                      const data = JSON.parse(msg.payload);
                      
                      // Filter by table + record
                      if (data.tableName === tableName && data.recordId === recordId) {
                        resolve({ value: { fieldLockChanged: data }, done: false });
                      } else {
                        // Keep listening
                        resolve(this.next());
                      }
                    }
                  });
                });
              },
              async return() {
                await context.pgClient.query(`UNLISTEN ${channel}`);
                return { done: true };
              }
            };
          }
        };
      }
    },

    stammdatenUpdated: {
      subscribe: async (_, args, context) => {
        const { id } = args;
        const channel = 'stammdaten_updated';
        
        await context.pgClient.query(`LISTEN ${channel}`);
        
        return {
          [Symbol.asyncIterator]() {
            return {
              async next() {
                return new Promise(async (resolve) => {
                  context.pgClient.once('notification', async (msg) => {
                    if (msg.channel === channel) {
                      const data = JSON.parse(msg.payload);
                      
                      if (data.id === id) {
                        // Fetch updated record
                        const result = await context.pgClient.query(
                          'SELECT * FROM stammdaten WHERE id = $1',
                          [id]
                        );
                        resolve({ 
                          value: { stammdatenUpdated: result.rows[0] }, 
                          done: false 
                        });
                      } else {
                        resolve(this.next());
                      }
                    }
                  });
                });
              },
              async return() {
                await context.pgClient.query(`UNLISTEN ${channel}`);
                return { done: true };
              }
            };
          }
        };
      }
    }
  }
};

export const fieldLockPlugin = makeExtendSchemaPlugin({
  typeDefs,
  resolvers
});
```

#### PostGraphile Server Config

```typescript
// server.ts (erweitern)

import { postgraphile } from 'postgraphile';
import { fieldLockPlugin } from './plugins/fieldLockPlugin';

const postgraphileMiddleware = postgraphile(
  process.env.DATABASE_URL || 'postgres://localhost:5432/mydb',
  'public',
  {
    // Bestehende Config beibehalten...
    appendPlugins: [
      fieldLockPlugin,
      // ... andere Plugins
    ],
    
    // WebSocket für Subscriptions
    subscriptions: true,
    simpleSubscriptions: true,
    
    // Development
    watchPg: true,
    graphiql: true,
    enhanceGraphiql: true,
    
    // Context mit User-Info
    pgSettings: (req) => ({
      'jwt.claims.user_id': req.user?.id || '1', // Für Test: Default User 1
    }),
  }
);
```

### 3. Frontend: Vue.js + Apollo Client

#### Apollo Client Setup

```typescript
// src/apollo/client.ts

import { ApolloClient, InMemoryCache, HttpLink, split } from '@apollo/client/core';
import { GraphQLWsLink } from '@apollo/client/link/subscriptions';
import { getMainDefinition } from '@apollo/client/utilities';
import { createClient } from 'graphql-ws';

// HTTP Link für Queries/Mutations
const httpLink = new HttpLink({
  uri: 'http://localhost:5000/graphql',
  credentials: 'include', // Cookies für Auth
});

// WebSocket Link für Subscriptions
const wsLink = new GraphQLWsLink(
  createClient({
    url: 'ws://localhost:5000/graphql',
    connectionParams: {
      // Auth token falls nötig
      // authToken: localStorage.getItem('token'),
    },
  })
);

// Split: HTTP für Queries/Mutations, WS für Subscriptions
const splitLink = split(
  ({ query }) => {
    const definition = getMainDefinition(query);
    return (
      definition.kind === 'OperationDefinition' &&
      definition.operation === 'subscription'
    );
  },
  wsLink,
  httpLink
);

export const apolloClient = new ApolloClient({
  link: splitLink,
  cache: new InMemoryCache(),
  defaultOptions: {
    watchQuery: {
      fetchPolicy: 'cache-and-network',
    },
  },
});
```

#### Vue Component

```vue
<!-- src/components/StammdatenForm.vue -->

<template>
  <div class="stammdaten-form">
    <h2>Stammdaten: {{ stammdaten?.name || 'Loading...' }}</h2>
    
    <div v-if="loading">Loading...</div>
    <div v-else-if="error">Error: {{ error.message }}</div>
    
    <form v-else @submit.prevent>
      <div v-for="field in editableFields" :key="field.key" class="form-field">
        <label :for="field.key">{{ field.label }}</label>
        <input
          :id="field.key"
          v-model="localData[field.key]"
          :disabled="isFieldLocked(field.key)"
          :class="{ 
            locked: isFieldLocked(field.key),
            'my-lock': isLockedByMe(field.key)
          }"
          @focus="handleFieldFocus(field.key)"
          @blur="handleFieldBlur(field.key)"
          type="text"
        />
        <span v-if="isFieldLocked(field.key)" class="lock-indicator">
          🔒 {{ getLockOwnerName(field.key) }}
        </span>
      </div>
    </form>
  </div>
</template>

<script setup lang="ts">
import { ref, reactive, computed, watch } from 'vue';
import { useQuery, useMutation, useSubscription } from '@vue/apollo-composable';
import gql from 'graphql-tag';

const props = defineProps<{
  stammdatenId: number;
  currentUserId: number;
}>();

// State
const fieldLocks = ref<Record<string, number>>({}); // { fieldName: userId }
const localData = reactive<Record<string, string>>({});
const originalValues = ref<Record<string, string>>({});

const editableFields = [
  { key: 'name', label: 'Name' },
  { key: 'adresse', label: 'Adresse' },
  { key: 'telefon', label: 'Telefon' },
  { key: 'email', label: 'E-Mail' },
  { key: 'notizen', label: 'Notizen' },
];

// Query: Stammdaten laden
const { result, loading, error } = useQuery(
  gql`
    query GetStammdaten($id: Int!) {
      stammdatumById(id: $id) {
        id
        name
        adresse
        telefon
        email
        notizen
      }
    }
  `,
  { id: props.stammdatenId }
);

const stammdaten = computed(() => result.value?.stammdatumById);

// Initialize local data
watch(stammdaten, (newData) => {
  if (newData) {
    Object.assign(localData, newData);
    originalValues.value = { ...newData };
  }
}, { immediate: true });

// Subscription: Field Lock Changes
useSubscription(
  gql`
    subscription OnFieldLockChanged($recordId: Int!) {
      fieldLockChanged(tableName: "stammdaten", recordId: $recordId) {
        fieldName
        locked
        lockedBy
      }
    }
  `,
  { recordId: props.stammdatenId },
  {
    onResult: (result) => {
      const event = result.data?.fieldLockChanged;
      if (event) {
        if (event.locked) {
          fieldLocks.value[event.fieldName] = event.lockedBy;
        } else {
          delete fieldLocks.value[event.fieldName];
        }
      }
    },
  }
);

// Subscription: Data Updates
useSubscription(
  gql`
    subscription OnStammdatenUpdate($id: Int!) {
      stammdatenUpdated(id: $id) {
        id
        name
        adresse
        telefon
        email
        notizen
      }
    }
  `,
  { id: props.stammdatenId },
  {
    onResult: (result) => {
      const updated = result.data?.stammdatenUpdated;
      if (updated) {
        Object.assign(localData, updated);
        originalValues.value = { ...updated };
      }
    },
  }
);

// Mutation: Lock Field
const { mutate: lockField } = useMutation(gql`
  mutation LockField($tableName: String!, $recordId: Int!, $fieldName: String!) {
    lockField(tableName: $tableName, recordId: $recordId, fieldName: $fieldName) {
      success
      lockedBy
      message
    }
  }
`);

// Mutation: Update + Unlock
const { mutate: updateField } = useMutation(gql`
  mutation UpdateFieldAndUnlock($id: Int!, $fieldName: String!, $value: String!) {
    updateFieldAndUnlock(id: $id, fieldName: $fieldName, value: $value) {
      id
      name
      adresse
      telefon
      email
      notizen
    }
  }
`);

// Event Handlers
const handleFieldFocus = async (fieldName: string) => {
  if (isFieldLocked(fieldName)) return;
  
  const result = await lockField({
    tableName: 'stammdaten',
    recordId: props.stammdatenId,
    fieldName,
  });
  
  if (!result?.data?.lockField?.success) {
    console.warn('Failed to lock field:', result?.data?.lockField?.message);
  }
};

const handleFieldBlur = async (fieldName: string) => {
  // Nur updaten wenn Wert geändert wurde
  if (localData[fieldName] === originalValues.value[fieldName]) {
    // Kein Update nötig, aber Lock trotzdem freigeben
    // (Lock läuft nach TTL aus, oder wir könnten separates unlockField implementieren)
    return;
  }
  
  if (!isLockedByMe(fieldName)) {
    console.warn('Cannot update: field not locked by current user');
    return;
  }
  
  await updateField({
    id: props.stammdatenId,
    fieldName,
    value: localData[fieldName],
  });
};

// Helpers
const isFieldLocked = (fieldName: string): boolean => {
  return fieldName in fieldLocks.value;
};

const isLockedByMe = (fieldName: string): boolean => {
  return fieldLocks.value[fieldName] === props.currentUserId;
};

const getLockOwnerName = (fieldName: string): string => {
  const ownerId = fieldLocks.value[fieldName];
  return ownerId === props.currentUserId ? 'You' : `User ${ownerId}`;
};
</script>

<style scoped>
.stammdaten-form {
  max-width: 600px;
  margin: 2rem auto;
  padding: 2rem;
  background: #f9f9f9;
  border-radius: 8px;
}

.form-field {
  margin-bottom: 1rem;
}

label {
  display: block;
  margin-bottom: 0.25rem;
  font-weight: 600;
}

input {
  width: 100%;
  padding: 0.5rem;
  border: 2px solid #ddd;
  border-radius: 4px;
  transition: all 0.2s;
}

input:focus {
  outline: none;
  border-color: #4a90e2;
}

input.locked {
  background-color: #f0f0f0;
  color: #999;
  cursor: not-allowed;
}

input.my-lock {
  border-color: #4a90e2;
  background-color: #e3f2fd;
}

.lock-indicator {
  display: inline-block;
  margin-left: 0.5rem;
  font-size: 0.875rem;
  color: #666;
}
</style>
```

### 4. Test-Setup

#### Multi-User Test Page

```vue
<!-- src/views/TestView.vue -->

<template>
  <div class="test-container">
    <h1>Field Locking Test - Multi-User Simulation</h1>
    
    <div class="user-panels">
      <div v-for="userId in [1, 2, 3]" :key="userId" class="user-panel">
        <h3>User {{ userId }}</h3>
        <StammdatenForm 
          :stammdaten-id="1" 
          :current-user-id="userId"
        />
      </div>
    </div>
  </div>
</template>

<script setup lang="ts">
import StammdatenForm from '@/components/StammdatenForm.vue';
</script>

<style scoped>
.test-container {
  padding: 2rem;
}

.user-panels {
  display: grid;
  grid-template-columns: repeat(auto-fit, minmax(400px, 1fr));
  gap: 2rem;
  margin-top: 2rem;
}

.user-panel {
  border: 2px solid #ddd;
  border-radius: 8px;
  padding: 1rem;
}
</style>
```

## Implementierungs-Schritte

### Phase 1: Backend Setup
1. ✅ PostgreSQL Schema erstellen (`stammdaten` Tabelle)
2. ✅ Redis Connection konfigurieren
3. ✅ PostGraphile Field-Lock Plugin implementieren
4. ✅ Server mit Subscription-Support starten
5. ✅ GraphiQL testen (http://localhost:5000/graphiql)

### Phase 2: Frontend Setup
1. ✅ Apollo Client mit WebSocket konfigurieren
2. ✅ StammdatenForm Component implementieren
3. ✅ Test-View mit Multi-User Panels erstellen
4. ✅ Styling & UX-Feedback

### Phase 3: Testing
1. ✅ Öffne 3 Browser-Tabs (simuliert 3 User)
2. ✅ User 1 fokussiert Feld "Name"
   - Erwartet: User 2 + 3 sehen Feld ausgegraut
3. ✅ User 1 ändert Wert + verlässt Feld
   - Erwartet: Alle User sehen neuen Wert + Feld wieder frei
4. ✅ User 2 versucht gesperrtes Feld zu fokussieren
   - Erwartet: Lock schlägt fehl (bereits gesperrt)
5. ✅ Lock-Timeout nach 5 Min
   - Erwartet: Feld wird automatisch freigegeben

## Environment Variables

```env
# .env
DATABASE_URL=postgres://username:password@localhost:5432/testdb
REDIS_HOST=localhost
REDIS_PORT=6379
PORT=5000
NODE_ENV=development
```

## Dependencies

### Backend
```json
{
  "dependencies": {
    "postgraphile": "^4.13.0",
    "graphile-utils": "^4.13.0",
    "ioredis": "^5.3.2",
    "pg": "^8.11.0",
    "express": "^4.18.2",
    "cors": "^2.8.5",
    "graphql": "^16.8.1",
    "graphql-ws": "^5.14.3",
    "ws": "^8.16.0"
  }
}
```

### Frontend
```json
{
  "dependencies": {
    "vue": "^3.4.0",
    "@vue/apollo-composable": "^4.0.0",
    "@apollo/client": "^3.8.0",
    "graphql": "^16.8.1",
    "graphql-ws": "^5.14.3"
  }
}
```

## Success Criteria

✅ **Architektur validiert**, wenn:
1. Field-Locks funktionieren in Echtzeit über WebSocket
2. Keine Race Conditions bei gleichzeitigem Lock-Versuch
3. Updates propagieren zu allen Clients (< 1 Sekunde)
4. Redis TTL räumt "vergessene" Locks automatisch auf
5. PostGraphile + Apollo Client Integration stabil
6. Code ist wartbar und erweiterbar für Produktiv-System

## Nächste Schritte nach erfolgreicher Studie

- [ ] User-Authentifizierung (JWT)
- [ ] Erweiterte Field-Lock-Info (Timestamp, User-Name)
- [ ] Optimistic UI Updates (Apollo Cache)
- [ ] Lock-Stealing für Admin-Rolle
- [ ] Monitoring (Lock-Dauer, Fehlerrate)
- [ ] Skalierung (Redis Cluster, PostgreSQL Replication)

---

**Hinweis für Claude Code:**  
Prüfe zunächst die bestehende Projektstruktur und passe die Integration entsprechend an. Nutze die vorhandenen Konventionen für:
- Ordnerstruktur
- Import-Pfade
- Linting/Formatting
- Package Manager (npm/yarn/pnpm)

Implementiere in logischen Commits und teste nach jedem Schritt.