Base de Datos

La API usa Cloudflare D1 (SQLite distribuido) con Drizzle ORM.

Diagrama Entidad-Relación

erDiagram
    API_KEYS ||--o{ OAUTH_TOKENS : "client_name"
    API_KEYS {
        text id PK
        text client_name UK
        text key UK
        text description
        int active
        int created_at
        int last_used_at
    }

    OAUTH_TOKENS {
        text id PK
        text client_name UK
        text access_token
        text refresh_token
        text token_type
        text scope
        text public_key
        int mp_user_id
        int live_mode
        text expires_at
        int created_at
        int updated_at
    }

    OAUTH_STATES {
        text id PK
        text client_name
        text target_url
        text code_verifier
        text used_at
        int created_at
    }

    STORES ||--|{ POS : "store_id"
    STORES {
        text id PK
        text client_name
        text mp_store_id
        text external_id
        text name
        text street_name
        text city_name
        text state_name
        real latitude
        real longitude
        int created_at
    }

    POS {
        text id PK
        text client_name
        text store_id FK
        text mp_pos_id
        text mp_qr_id
        text external_id
        text external_store_id
        text name
        int created_at
    }

    ORDERS {
        text id PK
        text client_name
        text mp_order_id UK
        text external_reference
        text idempotency_key
        text caja_id
        text sucursal_id
        text terminal_id
        text amount
        text description
        text status
        text mp_status
        text mp_status_detail
        int created_at
        int updated_at
        int processed_at
    }

    WEBHOOK_LOGS {
        text id PK
        text action
        text order_id
        text external_reference
        text status
        text raw_payload
        int created_at
    }

    ACTIVITY_LOGS {
        text id PK
        text client_name
        text ip_address
        text action
        text resource_type
        text resource_id
        text status
        text error_message
        text metadata
        int duration_ms
        int created_at
    }

Tablas

api_keys

Credenciales de acceso por cliente. El admin crea/revoca keys.

oauth_tokens

Tokens OAuth de MercadoPago por cliente.

oauth_states

States temporales para el flujo OAuth PKCE. Se limpian diariamente.

stores

Sucursales mapeadas entre el ERP y MercadoPago.

pos

Cajas POS asociadas a sucursales.

orders

Órdenes de pago con estado sincronizado desde MercadoPago.

activity_logs

Log de auditoría de operaciones de escritura (POST, PUT, DELETE).

Índices

-- API Keys
CREATE UNIQUE INDEX idx_api_keys_key ON api_keys(key);
CREATE UNIQUE INDEX idx_api_keys_client ON api_keys(client_name);

-- Orders
CREATE UNIQUE INDEX idx_orders_mp_id ON orders(mp_order_id);
CREATE UNIQUE INDEX idx_orders_idempotency ON orders(client_name, idempotency_key);
CREATE INDEX idx_orders_client ON orders(client_name);
CREATE INDEX idx_orders_external_ref ON orders(client_name, external_reference);

-- Stores / POS
CREATE UNIQUE INDEX idx_stores_external ON stores(client_name, external_id);
CREATE UNIQUE INDEX idx_pos_external ON pos(client_name, external_id);

-- Activity Logs
CREATE INDEX idx_activity_client_created ON activity_logs(client_name, created_at);
CREATE INDEX idx_activity_resource ON activity_logs(resource_type, resource_id);
CREATE INDEX idx_activity_action ON activity_logs(action);

Migraciones

Las migraciones están en migrations/ y se ejecutan con:

# Local
pnpm db:migrate:local

# Producción
pnpm db:migrate:remote

pnpm db:generate