SQL access for the IT team

If your company has its own IT team, they can query the Nori database directly and keep their own logic there (for example, a commission calculation you used to run as a SQL Server stored procedure). Sales, payments, stock and invoices are still recorded through Nori or its API. That protects stock levels, balances, the ERP sync and your CFDI e-invoices.

Who can turn it on

Users with an Administrator license, or with the SQL access permission in their role, can open Administration › SQL access. It isn't available in the demo company.

The two users

User	What it's for
Read (t_<empresa>_read)	Reports, Power BI, Excel and queries from a SQL editor. All its sessions are read-only.
Development (t_<empresa>_owner_custom)	Creating functions, procedures, views and tables in the custom schema. It can also read everything the read user can.

Neither user can write to Nori's tables, and neither can see users, passwords, certificates, settings or integration credentials.

Generate credentials

1. In SQL access, click Generate credentials on the user you need.
2. Copy the password right away. It isn't stored and won't be shown again. If you lose it, generate a new one.
3. Change password invalidates the old one and closes open sessions. Remove access blocks that user from connecting. Disable all SQL access blocks both users.
4. In Allowed networks, enter the IP addresses or ranges (for example 201.150.10.0/24) your team connects from. Nori support applies them on the server. With no allowed networks, nobody can connect, even with a password.

The Log records who generated, changed or removed credentials, who changed the networks, and from which IP. Open connections shows who's connected right now.

Connect

The connection details (server, port, database, user) are on the screen. Connections are always encrypted (sslmode=require).

• DBeaver / DataGrip: create a PostgreSQL connection, enter the server, port, database, user and password, and set SSL to require. You can also paste the JDBC URL shown on the screen.
• Power BI: Get data › PostgreSQL database, with server host:port and the database name. Use Import with scheduled refresh, not DirectQuery.
• Excel: install the PostgreSQL ODBC driver (64-bit psqlODBC) and use Data › Get data › From other sources › ODBC with the connection string shown on the screen.

What you can read

• Nori's tables (public), named in English: documents, document_lines, business_partners, items, item_warehouse, payments… Reports › Data dictionary explains each field.
• The legacy views, with the Nori desktop tables and columns: documentos, partidas, socios, articulos, pagos, partidas_pagos, flujo, almacenes, inventario and 40 others. They let your desktop queries keep working with the same names. Flags (cancelado, activo) are still 0 and 1, and documentos.clase is still FA, NC, PE… Columns the new model no longer has return NULL.
• Whatever your team creates in custom.

With the read user, the search path is legacy, public, custom: SELECT * FROM documentos reads the desktop view and SELECT * FROM documents reads the new table.

Create your own functions

Connect with the development user. Anything you create without a schema name goes into custom:

CREATE OR REPLACE FUNCTION custom.comisiones(p_desde date, p_hasta date)
RETURNS TABLE (vendedor text, ventas numeric, comision numeric)
LANGUAGE sql STABLE AS $$
    SELECT v.nombre, sum(d.subtotal * d.tipo_cambio), sum(d.subtotal * d.tipo_cambio) * v.porcentaje_comision / 100
    FROM legacy.documentos d JOIN legacy.vendedores v ON v.id = d.vendedor_id
    WHERE d.clase = 'FA' AND d.cancelado = 0 AND d.fecha_documento::date BETWEEN p_desde AND p_hasta
    GROUP BY v.nombre, v.porcentaje_comision
$$;

Then call it from a Nori SQL report or a print layout: SELECT * FROM custom.comisiones(:periodo_desde, :periodo_hasta). Nori runs it as the read user, with a time limit.

You can create your own tables in custom (for example, targets per sales rep) and write to them. You can't write to Nori's tables.

Bring over a SQL Server procedure

In SQL access › Import procedure, paste the SQL Server CREATE PROCEDURE:

1. Nori converts it to a PostgreSQL function. If AI is turned on, Improve with AI can refine the conversion.
2. Check the Points to review. Anything that couldn't be converted is listed with the exact code fragment.
3. Fix the code if needed and click Test (compile without saving).
4. Confirm that you reviewed it and click Save and install. Every version is kept in the history, so you can open an earlier one and reinstall it.

If the procedure wrote to business tables (for example, to update balances), that part isn't converted. Use the Nori API for it instead.

Nori desktop queries

When you import .repx layouts with nori:import-reports, and with the Convert from SQL Server button in the SQL report editor, Nori translates the desktop SQL: TOP → LIMIT, ISNULL → COALESCE, GETDATE() → now(), DATEADD/DATEDIFF, CONVERT with date styles, concatenation with +, [brackets], WITH (NOLOCK), @parameters → :parameters and EXEC → SELECT * FROM custom.…. Anything it can't translate is flagged, and the layout is marked Needs review.

PostgreSQL differences to keep in mind:

• Text comparisons with = are case-sensitive. (LIKE is translated to ILIKE, which isn't.)
• Integer division returns an integer, same as in SQL Server. Use ::numeric if you need decimals.
• Queries can't use #temp tables. Use WITH (a CTE) instead.

Service level

The database runs on a shared server. Each query has a time limit (60 s for the read user, 120 s for development), each user has a connection limit, and heavy queries may be stopped. For heavy use (Power BI refreshing every few minutes, many users), Nori offers a read-only replica or a dedicated server as an add-on. Ask your account rep.