OpsLink — Field Service & Asset Lifecycle

Índices y políticas propuestas

• WorkOrders: IX_WorkOrders_Status_SlaDueAt, IX_WorkOrders_AssetId, IX_WorkOrders_CreatedAt DESC
• Assignments: IX_Assignments_WorkOrderId, IX_Assignments_TechnicianId_ScheduledAt
• Evidence: IX_Evidence_WorkOrderId_CapturedAt, almacenar blobs en Azure Storage
• Inventory: IX_Items_Sku (UNIQUE), IX_Reservations_WorkOrderId, triggers para mantener OnHand/Reserved
• OutboxEvents: IX_OutboxEvents_ProcessedAt NULL → worker (Service Bus) cada N seg.
• Retención: Evidencias crudas ≥ 18 meses; logs/eventos ≥ 12 meses; cumplimiento según contrato.
• Particionado (opcional): por mes en Evidence/Outbox si el volumen crece > 50M filas/año.

# azure-pipelines.yml (OpsLink)
name: $(Date:yyyyMMdd).$(Rev:r)

trigger:
  branches: { include: [ main, develop ] }
pr:
  branches: { include: [ main, develop ] }

variables:
  - name: BuildConfiguration
    value: Release
  - name: DotnetVersion
    value: 9.0.x
  # Usa un Variable Group con secretos (DB, App Service, etc.)
  # - group: OpsLink-Secrets

stages:
- stage: Build_Test
  displayName: Build & Test
  jobs:
  - job: Build
    pool: { vmImage: 'ubuntu-latest' }
    steps:
    - task: UseDotNet@2
      inputs: { version: $(DotnetVersion) }

    - script: dotnet restore
      displayName: Restore

    - script: dotnet build --no-restore -c $(BuildConfiguration)
      displayName: Build

    - script: dotnet test --no-build -c $(BuildConfiguration) --logger trx
      displayName: Test

    # Genera script idempotente de migraciones EF
    - script: |
        dotnet new tool-manifest --force
        dotnet tool install dotnet-ef --version 9.*
        dotnet ef migrations script --idempotent -o $(Build.SourcesDirectory)/artifacts/ef-migrations.sql --project src/SISOneAndZero.Infrastructure/SISOneAndZero.Infrastructure.csproj --startup-project src/SISOneAndZero.Web/SISOneAndZero.Web.csproj
      displayName: EF Core: script idempotente

    - script: dotnet publish src/SISOneAndZero.Web/SISOneAndZero.Web.csproj -c $(BuildConfiguration) -o $(Build.ArtifactStagingDirectory)/web
      displayName: Publish Web

    - task: PublishBuildArtifacts@1
      inputs:
        PathtoPublish: $(Build.ArtifactStagingDirectory)
        ArtifactName: drop
        publishLocation: Container

- stage: Deploy_Staging
  dependsOn: Build_Test
  condition: succeeded()
  jobs:
  - deployment: WebApp_Staging
    environment: 'staging'
    strategy:
      runOnce:
        deploy:
          steps:
          - download: current
            artifact: drop
          # Despliegue WebApp (ajusta el task según Linux/Windows)
          - task: AzureWebApp@1
            inputs:
              azureSubscription: 'SC-Prod'         # Service connection
              appName: 'opslink-stg'
              package: '$(Pipeline.Workspace)/drop/web'
          # Aplicar migraciones en Azure SQL con el script idempotente
          - task: SqlAzureDacpacDeployment@1
            inputs:
              azureSubscription: 'SC-Prod'
              AuthenticationType: 'server'
              ServerName: '$(SqlServerName).database.windows.net'
              DatabaseName: '$(SqlDbName)'
              SqlUsername: '$(SqlUser)'
              SqlPassword: '$(SqlPassword)'
              deployType: 'SqlTask'
              SqlFile: '$(Pipeline.Workspace)/drop/ef-migrations.sql'

- stage: Deploy_Prod
  dependsOn: Deploy_Staging
  condition: succeeded()
  jobs:
  - deployment: WebApp_Prod
    environment: 'production'   # usa approvals
    strategy:
      runOnce:
        deploy:
          steps:
          - download: current
            artifact: drop
          - task: AzureWebApp@1
            inputs:
              azureSubscription: 'SC-Prod'
              appName: 'opslink-prod'
              package: '$(Pipeline.Workspace)/drop/web'
          - task: SqlAzureDacpacDeployment@1
            inputs:
              azureSubscription: 'SC-Prod'
              AuthenticationType: 'server'
              ServerName: '$(SqlServerName).database.windows.net'
              DatabaseName: '$(SqlDbName)'
              SqlUsername: '$(SqlUser)'
              SqlPassword: '$(SqlPassword)'
              deployType: 'SqlTask'
              SqlFile: '$(Pipeline.Workspace)/drop/ef-migrations.sql'

-- WorkOrders (simplificado)
CREATE TABLE dbo.WorkOrders(
  Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
  CustomerId UNIQUEIDENTIFIER NOT NULL,
  AssetId UNIQUEIDENTIFIER NULL,
  Status TINYINT NOT NULL,
  SlaDueAt DATETIME2 NOT NULL,
  CreatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
  UpdatedAt DATETIME2 NOT NULL
);
CREATE INDEX IX_WorkOrders_Status_SlaDueAt ON dbo.WorkOrders(Status, SlaDueAt);

-- WorkOrderAssignments
CREATE TABLE dbo.WorkOrderAssignments(
  Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
  WorkOrderId UNIQUEIDENTIFIER NOT NULL REFERENCES dbo.WorkOrders(Id),
  TechnicianId UNIQUEIDENTIFIER NOT NULL,
  ScheduledAt DATETIME2 NULL,
  WindowStart DATETIME2 NULL,
  WindowEnd DATETIME2 NULL,
  State TINYINT NOT NULL
);
CREATE INDEX IX_Assignments_WorkOrder ON dbo.WorkOrderAssignments(WorkOrderId);

-- Evidence
CREATE TABLE dbo.Evidence(
  Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
  WorkOrderId UNIQUEIDENTIFIER NOT NULL REFERENCES dbo.WorkOrders(Id),
  Type VARCHAR(20) NOT NULL,
  BlobUrl NVARCHAR(400) NOT NULL,
  CapturedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Evidence_WorkOrder_Captured ON dbo.Evidence(WorkOrderId, CapturedAt DESC);

-- Inventory
CREATE TABLE dbo.InventoryItems(
  Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
  Sku NVARCHAR(64) NOT NULL UNIQUE,
  Description NVARCHAR(200) NOT NULL,
  OnHand INT NOT NULL DEFAULT 0,
  Reserved INT NOT NULL DEFAULT 0
);
CREATE TABLE dbo.InventoryReservations(
  Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
  WorkOrderId UNIQUEIDENTIFIER NOT NULL REFERENCES dbo.WorkOrders(Id),
  ItemId UNIQUEIDENTIFIER NOT NULL REFERENCES dbo.InventoryItems(Id),
  Qty INT NOT NULL,
  ReservedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_Reservations_WorkOrder ON dbo.InventoryReservations(WorkOrderId);

-- Outbox (event-driven)
CREATE TABLE dbo.OutboxEvents(
  Id BIGINT IDENTITY(1,1) PRIMARY KEY,
  Type NVARCHAR(120) NOT NULL,
  AggregateId UNIQUEIDENTIFIER NOT NULL,
  Payload NVARCHAR(MAX) NOT NULL,
  CreatedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
  ProcessedAt DATETIME2 NULL
);
CREATE INDEX IX_Outbox_NotProcessed ON dbo.OutboxEvents(ProcessedAt) WHERE ProcessedAt IS NULL;