<mxfile host="app.diagrams.net" agent="ApiPay Hub docs-portal" type="device" version="24.7.17">
  <diagram id="apipay-aislamiento-rls" name="Aislamiento multi-tenant con RLS">
    <mxGraphModel dx="1800" dy="1000" grid="1" gridSize="10" guides="1" tooltips="1" connect="1" arrows="1" fold="1" page="1" pageScale="1" pageWidth="1169" pageHeight="826" math="0" shadow="0">
      <root>
        <mxCell id="0" />
        <mxCell id="1" parent="0" />

        <mxCell id="lay-roles" value="Los tres roles de PostgreSQL - ninguno tiene BYPASSRLS" style="rounded=0;whiteSpace=wrap;html=1;fillColor=#F5F5F5;strokeColor=#9E9E9E;dashed=1;dashPattern=8 8;verticalAlign=top;align=left;spacingLeft=10;spacingTop=6;fontColor=#1A1A1A;fontSize=13;fontStyle=1" vertex="1" parent="1">
          <mxGeometry x="40" y="60" width="980" height="200" as="geometry" />
        </mxCell>
        <mxCell id="lay-pol" value="Cada tabla con tenant_id: ENABLE ROW LEVEL SECURITY + FORCE ROW LEVEL SECURITY" style="rounded=0;whiteSpace=wrap;html=1;fillColor=#F5F5F5;strokeColor=#9E9E9E;dashed=1;dashPattern=8 8;verticalAlign=top;align=left;spacingLeft=10;spacingTop=6;fontColor=#1A1A1A;fontSize=13;fontStyle=1" vertex="1" parent="1">
          <mxGeometry x="40" y="290" width="980" height="220" as="geometry" />
        </mxCell>
        <mxCell id="lay-verif" value="Comportamiento verificado contra un PostgreSQL 17 real" style="rounded=0;whiteSpace=wrap;html=1;fillColor=#F5F5F5;strokeColor=#9E9E9E;dashed=1;dashPattern=8 8;verticalAlign=top;align=left;spacingLeft=10;spacingTop=6;fontColor=#1A1A1A;fontSize=13;fontStyle=1" vertex="1" parent="1">
          <mxGeometry x="40" y="540" width="980" height="200" as="geometry" />
        </mxCell>
        <mxCell id="lay-casos" value="Dos tablas con policy propia" style="rounded=0;whiteSpace=wrap;html=1;fillColor=#F5F5F5;strokeColor=#9E9E9E;dashed=1;dashPattern=8 8;verticalAlign=top;align=left;spacingLeft=10;spacingTop=6;fontColor=#1A1A1A;fontSize=13;fontStyle=1" vertex="1" parent="1">
          <mxGeometry x="1060" y="290" width="320" height="220" as="geometry" />
        </mxCell>

        <mxCell id="app" value="apipay_app&lt;br&gt;&lt;br&gt;payment-api y admin-api,&lt;br&gt;trafico de negocio.&lt;br&gt;Cada transaccion empieza con&lt;br&gt;SET LOCAL app.tenant_id" style="rounded=1;arcSize=12;whiteSpace=wrap;html=1;fillColor=#E3F2FD;strokeColor=#1565C0;fontColor=#1A1A1A;fontSize=11" vertex="1" parent="1">
          <mxGeometry x="60" y="110" width="300" height="130" as="geometry" />
        </mxCell>
        <mxCell id="worker" value="apipay_worker&lt;br&gt;&lt;br&gt;webhook-dispatcher, relay del outbox&lt;br&gt;e ingesta de webhooks entrantes.&lt;br&gt;Trabaja SIN app.tenant_id,&lt;br&gt;a proposito" style="rounded=1;arcSize=12;whiteSpace=wrap;html=1;fillColor=#E0F7FA;strokeColor=#00838F;fontColor=#1A1A1A;fontSize=11" vertex="1" parent="1">
          <mxGeometry x="380" y="110" width="300" height="130" as="geometry" />
        </mxCell>
        <mxCell id="migrator" value="apipay_migrator&lt;br&gt;&lt;br&gt;solo Flyway.&lt;br&gt;No es un rol de runtime:&lt;br&gt;ningun servicio se conecta con el&lt;br&gt;para atender trafico" style="rounded=1;arcSize=12;whiteSpace=wrap;html=1;fillColor=#EDE7F6;strokeColor=#4527A0;fontColor=#1A1A1A;fontSize=11" vertex="1" parent="1">
          <mxGeometry x="700" y="110" width="300" height="130" as="geometry" />
        </mxCell>

        <mxCell id="pol1" value="tenant_isolation&lt;br&gt;FOR ALL TO apipay_app&lt;br&gt;&lt;br&gt;USING y WITH CHECK:&lt;br&gt;tenant_id = NULLIF(&lt;br&gt;current_setting('app.tenant_id', true),&lt;br&gt;'')::uuid" style="rounded=1;arcSize=12;whiteSpace=wrap;html=1;fillColor=#E3F2FD;strokeColor=#1565C0;fontColor=#1A1A1A;fontSize=11" vertex="1" parent="1">
          <mxGeometry x="60" y="340" width="300" height="150" as="geometry" />
        </mxCell>
        <mxCell id="pol2" value="platform_access&lt;br&gt;FOR ALL TO apipay_worker&lt;br&gt;&lt;br&gt;USING (true) WITH CHECK (true)&lt;br&gt;&lt;br&gt;Es la unica policy que cruza tenants,&lt;br&gt;y solo la alcanza el worker" style="rounded=1;arcSize=12;whiteSpace=wrap;html=1;fillColor=#E0F7FA;strokeColor=#00838F;fontColor=#1A1A1A;fontSize=11" vertex="1" parent="1">
          <mxGeometry x="380" y="340" width="300" height="150" as="geometry" />
        </mxCell>
        <mxCell id="pol3" value="migrator_maintenance&lt;br&gt;FOR ALL TO apipay_migrator&lt;br&gt;&lt;br&gt;USING (true) WITH CHECK (true)&lt;br&gt;&lt;br&gt;Existe porque FORCE RLS somete&lt;br&gt;tambien al dueno de las tablas" style="rounded=1;arcSize=12;whiteSpace=wrap;html=1;fillColor=#EDE7F6;strokeColor=#4527A0;fontColor=#1A1A1A;fontSize=11" vertex="1" parent="1">
          <mxGeometry x="700" y="340" width="300" height="150" as="geometry" />
        </mxCell>

        <mxCell id="v1" value="apipay_app SIN app.tenant_id&lt;br&gt;0 filas - fail-closed" style="rounded=1;arcSize=12;whiteSpace=wrap;html=1;fillColor=#FFCDD2;strokeColor=#C62828;fontColor=#1A1A1A;fontSize=11;fontStyle=1" vertex="1" parent="1">
          <mxGeometry x="60" y="590" width="460" height="60" as="geometry" />
        </mxCell>
        <mxCell id="v2" value="apipay_app con app.tenant_id fijado&lt;br&gt;solo las filas de ese tenant" style="rounded=1;arcSize=12;whiteSpace=wrap;html=1;fillColor=#C8E6C9;strokeColor=#2E7D32;fontColor=#1A1A1A;fontSize=11" vertex="1" parent="1">
          <mxGeometry x="540" y="590" width="460" height="60" as="geometry" />
        </mxCell>
        <mxCell id="v3" value="apipay_worker sin tenant&lt;br&gt;todas las filas, via platform_access" style="rounded=1;arcSize=12;whiteSpace=wrap;html=1;fillColor=#C8E6C9;strokeColor=#2E7D32;fontColor=#1A1A1A;fontSize=11" vertex="1" parent="1">
          <mxGeometry x="60" y="660" width="460" height="60" as="geometry" />
        </mxCell>
        <mxCell id="v4" value="WITH CHECK impide ademas INSERTAR&lt;br&gt;o mover una fila hacia otro tenant" style="rounded=1;arcSize=12;whiteSpace=wrap;html=1;fillColor=#C8E6C9;strokeColor=#2E7D32;fontColor=#1A1A1A;fontSize=11" vertex="1" parent="1">
          <mxGeometry x="540" y="660" width="460" height="60" as="geometry" />
        </mxCell>

        <mxCell id="tablas" value="Con RLS: tenant_api_keys, tenant_gateway_configs, payment_methods, payment_intents, transactions y transaction_events (con todas sus particiones), refunds, webhook_endpoints, outbound_webhook_deliveries, outbox_events, audit_log.&lt;br&gt;&lt;br&gt;Exentas: gateway_catalog (catalogo global, sin tenant_id) y flyway_schema_history." style="shape=note;size=16;whiteSpace=wrap;html=1;fillColor=#E8F5E9;strokeColor=#2E7D32;fontColor=#1A1A1A;fontSize=10;align=left;verticalAlign=top;spacingLeft=8;spacingTop=4" vertex="1" parent="1">
          <mxGeometry x="1060" y="60" width="320" height="200" as="geometry" />
        </mxCell>
        <mxCell id="tenants" value="apipay.tenants&lt;br&gt;tenant_self FOR SELECT TO apipay_app:&lt;br&gt;la fila ES el tenant, asi que compara id,&lt;br&gt;no tenant_id" style="rounded=1;arcSize=12;whiteSpace=wrap;html=1;fillColor=#E8F5E9;strokeColor=#2E7D32;fontColor=#1A1A1A;fontSize=10" vertex="1" parent="1">
          <mxGeometry x="1075" y="340" width="290" height="80" as="geometry" />
        </mxCell>
        <mxCell id="inbound" value="apipay.inbound_webhook_events&lt;br&gt;tenant_read FOR SELECT TO apipay_app:&lt;br&gt;la escribe el worker ANTES de conocer&lt;br&gt;el tenant, asi que app solo puede leer" style="rounded=1;arcSize=12;whiteSpace=wrap;html=1;fillColor=#E8F5E9;strokeColor=#2E7D32;fontColor=#1A1A1A;fontSize=10" vertex="1" parent="1">
          <mxGeometry x="1075" y="430" width="290" height="70" as="geometry" />
        </mxCell>

        <mxCell id="failclosed" value="Fail-closed, no fail-open&lt;br&gt;&lt;br&gt;current_setting('app.tenant_id', true) con missing_ok devuelve NULL si nadie fijo la variable, y NULLIF cubre el string vacio. Sin contexto de tenant la expresion es NULL y la policy no califica NINGUNA fila: no lanza error, devuelve el conjunto vacio.&lt;br&gt;&lt;br&gt;Un bug de query no puede cruzar tenants; un servicio que olvida fijar el tenant no ve nada. Ese mismo mecanismo es el que dejaba al relay del outbox sin publicar." style="rounded=1;arcSize=8;whiteSpace=wrap;html=1;fillColor=#FDECEA;strokeColor=#C62828;fontColor=#1A1A1A;fontSize=10;align=left;verticalAlign=top;spacingLeft=8;spacingTop=6" vertex="1" parent="1">
          <mxGeometry x="1060" y="540" width="320" height="200" as="geometry" />
        </mxCell>

        <mxCell id="nota-force" value="FORCE ROW LEVEL SECURITY somete tambien al dueno de las tablas: sin migrator_maintenance, Flyway no podria ejecutar seeds ni backfills. No debilita el aislamiento, porque el dueno puede alterar las tablas de todos modos y ningun rol de runtime alcanza esa policy. Las particiones nuevas de transactions y transaction_events nacen protegidas: la funcion que las crea llama a apipay.apply_tenant_rls()." style="shape=note;size=16;whiteSpace=wrap;html=1;fillColor=#FFFDE7;strokeColor=#F9A825;fontColor=#1A1A1A;fontSize=10;align=left;verticalAlign=top;spacingLeft=8;spacingTop=4" vertex="1" parent="1">
          <mxGeometry x="40" y="770" width="980" height="80" as="geometry" />
        </mxCell>
        <mxCell id="nota-setlocal" value="SET LOCAL vale solo hasta el fin de la transaccion: se fija con set_config('app.tenant_id', ?, true), de modo que una conexion devuelta al pool no puede filtrar el tenant de la peticion anterior." style="shape=note;size=16;whiteSpace=wrap;html=1;fillColor=#FFFDE7;strokeColor=#F9A825;fontColor=#1A1A1A;fontSize=10;align=left;verticalAlign=top;spacingLeft=8;spacingTop=4" vertex="1" parent="1">
          <mxGeometry x="1060" y="770" width="320" height="80" as="geometry" />
        </mxCell>

        <mxCell id="e-app-pol1" value="sujeto a" style="edgeStyle=orthogonalEdgeStyle;rounded=0;html=1;jettySize=auto;orthogonalLoop=1;strokeColor=#455A64;fontColor=#1A1A1A;fontSize=10;labelBackgroundColor=#FFFFFF;endArrow=block;endFill=1;exitX=0.5;exitY=1;exitDx=0;exitDy=0;entryX=0.5;entryY=0;entryDx=0;entryDy=0" edge="1" parent="1" source="app" target="pol1">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="e-worker-pol2" value="sujeto a" style="edgeStyle=orthogonalEdgeStyle;rounded=0;html=1;jettySize=auto;orthogonalLoop=1;strokeColor=#455A64;fontColor=#1A1A1A;fontSize=10;labelBackgroundColor=#FFFFFF;endArrow=block;endFill=1;exitX=0.5;exitY=1;exitDx=0;exitDy=0;entryX=0.5;entryY=0;entryDx=0;entryDy=0" edge="1" parent="1" source="worker" target="pol2">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="e-migrator-pol3" value="sujeto a" style="edgeStyle=orthogonalEdgeStyle;rounded=0;html=1;jettySize=auto;orthogonalLoop=1;strokeColor=#455A64;fontColor=#1A1A1A;fontSize=10;labelBackgroundColor=#FFFFFF;endArrow=block;endFill=1;exitX=0.5;exitY=1;exitDx=0;exitDy=0;entryX=0.5;entryY=0;entryDx=0;entryDy=0" edge="1" parent="1" source="migrator" target="pol3">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>

        <mxCell id="e-pol1-v1" value="" style="edgeStyle=orthogonalEdgeStyle;rounded=0;html=1;jettySize=auto;orthogonalLoop=1;strokeColor=#C62828;fontColor=#B71C1C;fontSize=10;labelBackgroundColor=#FFFFFF;endArrow=block;endFill=1;exitX=0.15;exitY=1;exitDx=0;exitDy=0;entryX=0.15;entryY=0;entryDx=0;entryDy=0" edge="1" parent="1" source="pol1" target="v1">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="e-pol1-v2" value="" style="edgeStyle=orthogonalEdgeStyle;rounded=0;html=1;jettySize=auto;orthogonalLoop=1;strokeColor=#2E7D32;fontColor=#1B5E20;fontSize=10;labelBackgroundColor=#FFFFFF;endArrow=block;endFill=1;exitX=0.75;exitY=1;exitDx=0;exitDy=0;entryX=0.1;entryY=0;entryDx=0;entryDy=0" edge="1" parent="1" source="pol1" target="v2">
          <mxGeometry relative="1" as="geometry">
            <Array as="points">
              <mxPoint x="285" y="520" />
              <mxPoint x="586" y="520" />
            </Array>
          </mxGeometry>
        </mxCell>
        <mxCell id="e-pol2-v3" value="" style="edgeStyle=orthogonalEdgeStyle;rounded=0;html=1;jettySize=auto;orthogonalLoop=1;strokeColor=#2E7D32;fontColor=#1B5E20;fontSize=10;labelBackgroundColor=#FFFFFF;endArrow=block;endFill=1;exitX=0.5;exitY=1;exitDx=0;exitDy=0;entryX=0.6;entryY=0;entryDx=0;entryDy=0" edge="1" parent="1" source="pol2" target="v3">
          <mxGeometry relative="1" as="geometry">
            <Array as="points">
              <mxPoint x="530" y="527" />
              <mxPoint x="336" y="527" />
            </Array>
          </mxGeometry>
        </mxCell>
        <mxCell id="e-pol1-v4" value="" style="edgeStyle=orthogonalEdgeStyle;rounded=0;html=1;jettySize=auto;orthogonalLoop=1;strokeColor=#2E7D32;fontColor=#1B5E20;fontSize=10;labelBackgroundColor=#FFFFFF;endArrow=block;endFill=1;exitX=0.9;exitY=1;exitDx=0;exitDy=0;entryX=0.5;entryY=0;entryDx=0;entryDy=0" edge="1" parent="1" source="pol1" target="v4">
          <mxGeometry relative="1" as="geometry">
            <Array as="points">
              <mxPoint x="330" y="533" />
              <mxPoint x="770" y="533" />
            </Array>
          </mxGeometry>
        </mxCell>
        <mxCell id="e-pol-casos" value="excepciones" style="edgeStyle=orthogonalEdgeStyle;rounded=0;html=1;jettySize=auto;orthogonalLoop=1;dashed=1;strokeColor=#455A64;fontColor=#1A1A1A;fontSize=10;labelBackgroundColor=#FFFFFF;endArrow=block;endFill=1;exitX=1;exitY=0.5;exitDx=0;exitDy=0;entryX=0;entryY=0.5;entryDx=0;entryDy=0" edge="1" parent="1" source="lay-pol" target="lay-casos">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="e-verif-fail" value="por que" style="edgeStyle=orthogonalEdgeStyle;rounded=0;html=1;jettySize=auto;orthogonalLoop=1;dashed=1;strokeColor=#C62828;fontColor=#B71C1C;fontSize=10;labelBackgroundColor=#FFFFFF;endArrow=block;endFill=1;exitX=1;exitY=0.5;exitDx=0;exitDy=0;entryX=0;entryY=0.5;entryDx=0;entryDy=0" edge="1" parent="1" source="lay-verif" target="failclosed">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
      </root>
    </mxGraphModel>
  </diagram>
</mxfile>
