Why is the column value not persisting after Postgres function triggers after update?

I have a table called Projects where I need to set a certain column value after an update is executed and the record matches certain conditions. The function triggers correctly and there are no errors but the updated column value does not persist.

Here's my SQL:

create table projects (
  id int not null primary key,
  status varchar,
  number varchar
);

CREATE FUNCTION update_number() RETURNS trigger
    LANGUAGE plpgsql
AS
$$
declare
    status_code  varchar;
begin
    SELECT project_codes.code INTO status_code FROM project_codes WHERE project_codes.status = NEW.status;
    NEW.number := CONCAT('CA-', status_code, '-', nextval('project_sequence'));
    RAISE INFO 'Project: %', NEW;
    RETURN NEW;
end
$$;

CREATE TRIGGER update_number_trig
    AFTER UPDATE
    ON projects
    FOR EACH ROW
    WHEN (NEW.status = 'CAPITAL')
EXECUTE PROCEDURE update_number();

I'm running the following command to test this:

UPDATE projects SET status = 'CAPITAL' WHERE id = '123456';

The function executes after the update operation, I see in the console that the RAISE prints:

Project: (123456, CAPITAL, CA-2DC-1)

But when I do a SELECT on the projects table, the number column is blank.

Why is setting the column value not persisting?

Modifying NEW only has an effect in BEFORE triggers. In an AFTER trigger, the row has already been written, and the modified NEW you return will at most affect other AFTER triggers that come after you.

You need to use a BEFORE UPDATE trigger.