Finding forgotten Odoo customizations with SQL

Every Odoo system that has been live for a few years carries customizations nobody can fully explain: a Studio field someone added for a project that ended, an automation that emails a person who left, a scheduled action that has failed every night since the last upgrade, a module installed to try something out. Each one makes the next upgrade slower and the system harder to reason about.

You can list all of them yourself, in an afternoon, with read-only SQL. This article gives the queries, explains which Odoo versions they fit, and says what to look for in the results.

Before you start

You need access to PostgreSQL (psql, pgAdmin, DBeaver or similar) for your Odoo database. On Odoo Online the practical way in is a downloaded backup: it contains a SQL dump you can restore into any PostgreSQL server and query there. Start the session like this:

Read-only Session setup

SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
SET statement_timeout = '60s';

A note on versions. The table and column names below were checked against Odoo's source code for 16.0 to 19.0, and they also match 20.0. Databases differ, so if a query fails on yours, the error message names the column or table to look at. Since 16.0, translatable text such as field labels and model names is stored as JSON with one key per language, so the queries read the English value with ->>'en_US'. Where versions differ, the query says so.

1. Modules not written by Odoo

Read-only Odoo 16 to 19

SELECT name, author, latest_version, license
FROM ir_module_module
WHERE state = 'installed'
  AND COALESCE(author, '') NOT ILIKE '%Odoo S.A.%'
ORDER BY author, name;

This lists your own modules, OCA modules and anything from the Apps Store. For each one, find out who uses it and whether a newer Odoo version already does the same job. A module with an empty author is usually home-grown. Before 19.0, Odoo fills in "Odoo S.A." as the author when a module's manifest leaves it out, so on 16, 17 and 18 also look through the modules this query leaves out for any name you do not recognize as a standard Odoo app.

2. Custom fields, including Studio fields

Read-only Odoo 16 to 19

SELECT f.model,
       f.name,
       f.ttype,
       f.field_description->>'en_US' AS label,
       f.relation,
       f.store,
       f.name LIKE 'x\_studio\_%' AS looks_like_studio,
       f.create_date::date AS created
FROM ir_model_fields f
WHERE f.state = 'manual'
ORDER BY f.model, f.name;

Custom fields have state = 'manual', and Odoo requires their names to start with x_. Studio names its fields x_studio_ by default, though a user can rename them. Fields defined in module code are not in this list; they show up in step 1 as modules.

Custom models (whole new record types) are listed the same way:

Read-only Odoo 16 to 19

SELECT model, name->>'en_US' AS description, create_date::date AS created
FROM ir_model
WHERE state = 'manual'
ORDER BY model;

3. Are those fields actually used?

A field nobody fills in is a candidate for removal. This query does not count anything itself: it writes one counting query per stored custom field, which you then copy and run. That keeps it to plain SQL with no functions or temporary tables.

Read-only Odoo 16 to 19, generates queries

SELECT format(
  'SELECT %L AS field, count(%I) AS filled, count(*) AS total FROM %I;',
  f.model || '.' || f.name, f.name, replace(f.model, '.', '_')
)
FROM ir_model_fields f
WHERE f.state = 'manual'
  AND f.store
  AND f.ttype NOT IN ('one2many', 'many2many', 'binary')
ORDER BY f.model, f.name;

Each generated line counts how many records have a value. Two caveats: a boolean field counts False as filled, and a model whose table has a non-standard name will fail with "relation does not exist" or "column does not exist" (rare, and harmless in a read-only session). File and image fields are left out because Odoo stores them as attachments, not in a column. A field that is empty on thousands of records is worth a conversation.

4. Views changed outside of code

Views created through Studio belong to a generated module called studio_customization:

Read-only Odoo 16 to 19

SELECT v.id, v.name, v.model, v.type, v.mode, v.active,
       v.write_date::date AS changed
FROM ir_ui_view v
JOIN ir_model_data d ON d.model = 'ir.ui.view' AND d.res_id = v.id
WHERE d.module = 'studio_customization'
ORDER BY v.model, v.name;

Views with no external ID at all were usually created by hand in developer mode or through the website builder. Views marked arch_updated had their XML changed outside of module code, whether in developer mode, the website builder or Studio; those edits are the ones most likely to be overwritten or to break at the next upgrade:

Read-only Odoo 16 to 19

SELECT v.id, v.name, v.model, v.type, v.active, v.arch_updated,
       v.write_date::date AS changed,
       NOT EXISTS (
         SELECT 1 FROM ir_model_data d
         WHERE d.model = 'ir.ui.view' AND d.res_id = v.id
       ) AS no_external_id
FROM ir_ui_view v
WHERE v.arch_updated
   OR NOT EXISTS (
         SELECT 1 FROM ir_model_data d
         WHERE d.model = 'ir.ui.view' AND d.res_id = v.id
       )
ORDER BY v.model, v.name;

5. Automation rules

In 17.0 automation rules became a model of their own with one or more actions attached, so there are two versions of this query.

Both need the Automation Rules module. If PostgreSQL answers relation "base_automation" does not exist, the module is not installed, so there are no automation rules to review and you can skip this section.

Read-only Odoo 17, 18, 19 (requires the Automation Rules module)

SELECT a.id,
       a.name->>'en_US' AS rule,
       m.model,
       a.trigger,
       a.active,
       a.last_run,
       count(s.id) AS actions,
       string_agg(DISTINCT s.state, ', ') AS action_types
FROM base_automation a
JOIN ir_model m ON m.id = a.model_id
LEFT JOIN ir_act_server s ON s.base_automation_id = a.id
GROUP BY a.id, m.model
ORDER BY a.active DESC, m.model;

Read-only Odoo 16

SELECT a.id, s.name->>'en_US' AS rule, m.model, a.trigger,
       a.active, a.last_run, s.state AS action_type
FROM base_automation a
JOIN ir_act_server s ON s.id = a.action_server_id
JOIN ir_model m ON m.id = s.model_id
ORDER BY a.active DESC, m.model;

Look at last_run for time-based rules, and at action_types: code means the rule runs Python, which deserves a careful read.

6. Server actions that run Python code

Read-only Odoo 17, 18, 19

SELECT s.id, s.name, m.model, s.usage,
       s.binding_model_id IS NOT NULL AS in_action_menu,
       length(s.code) AS code_length,
       s.write_date::date AS changed
FROM ir_act_server s
JOIN ir_model m ON m.id = s.model_id
WHERE s.state = 'code'
  AND NOT EXISTS (
        SELECT 1 FROM ir_model_data d
        WHERE d.model = 'ir.actions.server' AND d.res_id = s.id
          AND d.module NOT IN ('__export__', '__import__', 'studio_customization')
      )
ORDER BY m.model, s.name;

The filter keeps actions created in the user interface, imported from a file or made by Studio, and drops those shipped in module code. In 16.0 the action name is translated, so use s.name->>'en_US' there. Read the code column of each result: look for hard-coded record IDs, email addresses, sudo() and anything that writes to accounting records.

7. Scheduled actions, and which ones are failing

Read-only Odoo 16 to 19 (see the note below for 16)

SELECT c.id, c.cron_name, c.active,
       c.interval_number || ' ' || c.interval_type AS every,
       c.lastcall, c.nextcall,
       u.login AS runs_as,
       d.module AS defined_in
FROM ir_cron c
JOIN res_users u ON u.id = c.user_id
LEFT JOIN ir_model_data d ON d.model = 'ir.cron' AND d.res_id = c.id
ORDER BY c.active DESC, c.lastcall NULLS FIRST;

In 16.0 the job name is translated, so use c.cron_name->>'en_US' there. An empty defined_in means someone created the job by hand. An active job with an old lastcall has not completed successfully in a while. From 18.0 Odoo also counts consecutive failures:

Read-only Odoo 18, 19

SELECT id, cron_name, failure_count, first_failure_date, lastcall
FROM ir_cron
WHERE active AND failure_count > 0
ORDER BY first_failure_date;

From 18.0, a job that fails at least five times in a row over more than seven days is switched off automatically and its failure count goes back to zero, so also review the inactive jobs in the first query that have a defined_in module. The reason for a failure, and the notice that a job was switched off, are in the Odoo server log, not in the database.

8. Custom reports

Read-only Odoo 16 to 19

SELECT r.id, r.name->>'en_US' AS report, r.model, r.report_name, r.report_type
FROM ir_act_report_xml r
WHERE NOT EXISTS (
        SELECT 1 FROM ir_model_data d
        WHERE d.model = 'ir.actions.report' AND d.res_id = r.id
          AND d.module NOT IN ('__export__', '__import__', 'studio_customization')
      )
ORDER BY r.model;

9. Who still logs in

Automations often run as, or send to, a specific user. Knowing who is still active helps judge whether a customization still has an owner. Odoo regularly cleans this table down to each user's latest entry, so it shows the last login rather than a history. It records sign-ins with a password, so someone who stays signed in for weeks can look inactive.

Read-only Odoo 16 to 19

SELECT u.login, u.active, max(l.create_date) AS last_login
FROM res_users u
LEFT JOIN res_users_log l ON l.create_uid = u.id
WHERE NOT u.share
GROUP BY u.login, u.active
ORDER BY last_login NULLS FIRST;

What to do with the list

Put everything in one spreadsheet with a row per item and four possible verdicts:

• Keep: used, working and understood. Write one sentence on what it does and who relies on it.
• Fix: used but failing or fragile, such as the scheduled action that errors nightly or the edited standard view.
• Consolidate: three reports that print the same thing, two fields holding the same fact.
• Remove: empty fields, inactive rules nobody misses, modules nobody uses.

Make the changes on a copy first, with the people who use each area testing, then on production in small batches you can undo. Removing a field or uninstalling a module deletes its data, so export anything you might want before it goes.

The result is worth the afternoon. A system you can explain item by item is cheaper to support and far cheaper to upgrade, because every customization removed is one that never has to be ported. If you are planning a move to Odoo 19, our notes on upgrading custom modules pick up where this list leaves off, and our Odoo 19 upgrade page explains how we run one for you.

If you would rather not run the queries yourself, our free read-only Odoo scan counts and names the customizations in your database, and customization cleanup is the service that works through the list with you.