diff --git a/sql/swh-func.sql b/sql/swh-func.sql
index 4138278a6..9940c6f40 100644
--- a/sql/swh-func.sql
+++ b/sql/swh-func.sql
@@ -1,1517 +1,1523 @@
 -- create a temporary table called tmp_TBLNAME, mimicking existing table
 -- TBLNAME
 --
 -- Args:
 --     tblname: name of the table to mimick
 create or replace function swh_mktemp(tblname regclass)
     returns void
     language plpgsql
 as $$
 begin
     execute format('
 	create temporary table tmp_%1$I
 	    (like %1$I including defaults)
 	    on commit drop;
       alter table tmp_%1$I drop column if exists object_id;
 	', tblname);
     return;
 end
 $$;
 
 -- create a temporary table for directory entries called tmp_TBLNAME,
 -- mimicking existing table TBLNAME with an extra dir_id (sha1_git)
 -- column, and dropping the id column.
 --
 -- This is used to create the tmp_directory_entry_<foo> tables.
 --
 -- Args:
 --     tblname: name of the table to mimick
 create or replace function swh_mktemp_dir_entry(tblname regclass)
     returns void
     language plpgsql
 as $$
 begin
     execute format('
 	create temporary table tmp_%1$I
 	    (like %1$I including defaults, dir_id sha1_git)
 	    on commit drop;
         alter table tmp_%1$I drop column id;
 	', tblname);
     return;
 end
 $$;
 
 
 -- create a temporary table for revisions called tmp_revisions,
 -- mimicking existing table revision, replacing the foreign keys to
 -- people with an email and name field
 --
 create or replace function swh_mktemp_revision()
     returns void
     language sql
 as $$
     create temporary table tmp_revision (
         like revision including defaults,
         author_fullname bytea,
         author_name bytea,
         author_email bytea,
         committer_fullname bytea,
         committer_name bytea,
         committer_email bytea
     ) on commit drop;
     alter table tmp_revision drop column author;
     alter table tmp_revision drop column committer;
     alter table tmp_revision drop column object_id;
 $$;
 
 
 -- create a temporary table for releases called tmp_release,
 -- mimicking existing table release, replacing the foreign keys to
 -- people with an email and name field
 --
 create or replace function swh_mktemp_release()
     returns void
     language sql
 as $$
     create temporary table tmp_release (
         like release including defaults,
         author_fullname bytea,
         author_name bytea,
         author_email bytea
     ) on commit drop;
     alter table tmp_release drop column author;
     alter table tmp_release drop column object_id;
 $$;
 
 -- create a temporary table with a single "bytea" column for fast object lookup.
 create or replace function swh_mktemp_bytea()
     returns void
     language sql
 as $$
     create temporary table tmp_bytea (
       id bytea
     ) on commit drop;
 $$;
 
 -- create a temporary table for occurrence_history
 create or replace function swh_mktemp_occurrence_history()
     returns void
     language sql
 as $$
     create temporary table tmp_occurrence_history(
         like occurrence_history including defaults,
         visit bigint not null
     ) on commit drop;
     alter table tmp_occurrence_history
       drop column visits,
       drop column object_id;
 $$;
 
 -- create a temporary table for entity_history, sans id
 create or replace function swh_mktemp_entity_history()
     returns void
     language sql
 as $$
     create temporary table tmp_entity_history (
         like entity_history including defaults) on commit drop;
     alter table tmp_entity_history drop column id;
 $$;
 
 -- create a temporary table for entities called tmp_entity_lister,
 -- with only the columns necessary for retrieving the uuid of a listed
 -- entity.
 create or replace function swh_mktemp_entity_lister()
     returns void
     language sql
 as $$
   create temporary table tmp_entity_lister (
     id              bigint,
     lister_metadata jsonb
   ) on commit drop;
 $$;
 
 -- create a temporary table for the branches of a snapshot
 create or replace function swh_mktemp_snapshot_branch()
     returns void
     language sql
 as $$
   create temporary table tmp_snapshot_branch (
       name bytea not null,
       target bytea,
       target_type snapshot_target
   ) on commit drop;
 $$;
 
 create or replace function swh_mktemp_tool()
     returns void
     language sql
 as $$
     create temporary table tmp_tool (
       like tool including defaults
     ) on commit drop;
     alter table tmp_tool drop column id;
 $$;
 
 
 -- a content signature is a set of cryptographic checksums that we use to
 -- uniquely identify content, for the purpose of verifying if we already have
 -- some content or not during content injection
 create type content_signature as (
     sha1       sha1,
     sha1_git   sha1_git,
     sha256     sha256,
     blake2s256 blake2s256
 );
 
 
 -- check which entries of tmp_content are missing from content
 --
 -- operates in bulk: 0. swh_mktemp(content), 1. COPY to tmp_content,
 -- 2. call this function
 create or replace function swh_content_missing()
     returns setof content_signature
     language plpgsql
 as $$
 begin
     return query (
       select sha1, sha1_git, sha256, blake2s256 from tmp_content as tmp
       where not exists (
         select 1 from content as c
         where c.sha1 = tmp.sha1 and
               c.sha1_git = tmp.sha1_git and
               c.sha256 = tmp.sha256
       )
     );
     return;
 end
 $$;
 
 -- check which entries of tmp_content_sha1 are missing from content
 --
 -- operates in bulk: 0. swh_mktemp_content_sha1(), 1. COPY to tmp_content_sha1,
 -- 2. call this function
 create or replace function swh_content_missing_per_sha1()
     returns setof sha1
     language plpgsql
 as $$
 begin
     return query
            (select id::sha1
             from tmp_bytea as tmp
             where not exists
             (select 1 from content as c where c.sha1=tmp.id));
 end
 $$;
 
 
 -- check which entries of tmp_skipped_content are missing from skipped_content
 --
 -- operates in bulk: 0. swh_mktemp(skipped_content), 1. COPY to tmp_skipped_content,
 -- 2. call this function
 create or replace function swh_skipped_content_missing()
     returns setof content_signature
     language plpgsql
 as $$
 begin
     return query
 	select sha1, sha1_git, sha256, blake2s256 from tmp_skipped_content t
 	where not exists
 	(select 1 from skipped_content s where
 	    s.sha1 is not distinct from t.sha1 and
 	    s.sha1_git is not distinct from t.sha1_git and
 	    s.sha256 is not distinct from t.sha256);
     return;
 end
 $$;
 
 
 -- Look up content based on one or several different checksums. Return all
 -- content information if the content is found; a NULL row otherwise.
 --
 -- At least one checksum should be not NULL. If several are not NULL, they will
 -- be AND-ed together in the lookup query.
 --
 -- Note: this function is meant to be used to look up individual contents
 -- (e.g., for the web app), for batch lookup of missing content (e.g., to be
 -- added) see swh_content_missing
 create or replace function swh_content_find(
     sha1       sha1       default NULL,
     sha1_git   sha1_git   default NULL,
     sha256     sha256     default NULL,
     blake2s256 blake2s256 default NULL
 )
     returns content
     language plpgsql
 as $$
 declare
     con content;
     filters text[] := array[] :: text[];  -- AND-clauses used to filter content
     q text;
 begin
     if sha1 is not null then
         filters := filters || format('sha1 = %L', sha1);
     end if;
     if sha1_git is not null then
         filters := filters || format('sha1_git = %L', sha1_git);
     end if;
     if sha256 is not null then
         filters := filters || format('sha256 = %L', sha256);
     end if;
     if blake2s256 is not null then
         filters := filters || format('blake2s256 = %L', blake2s256);
     end if;
 
     if cardinality(filters) = 0 then
         return null;
     else
         q = format('select * from content where %s',
                    array_to_string(filters, ' and '));
         execute q into con;
 	return con;
     end if;
 end
 $$;
 
 
 -- add tmp_content entries to content, skipping duplicates
 --
 -- operates in bulk: 0. swh_mktemp(content), 1. COPY to tmp_content,
 -- 2. call this function
 create or replace function swh_content_add()
     returns void
     language plpgsql
 as $$
 begin
     insert into content (sha1, sha1_git, sha256, blake2s256, length, status)
         select distinct sha1, sha1_git, sha256, blake2s256, length, status
 	from tmp_content
 	where (sha1, sha1_git, sha256) in (
             select sha1, sha1_git, sha256
             from swh_content_missing()
         );
         -- TODO XXX use postgres 9.5 "UPSERT" support here, when available.
         -- Specifically, using "INSERT .. ON CONFLICT IGNORE" we can avoid
         -- the extra swh_content_missing() query here.
     return;
 end
 $$;
 
 
 -- add tmp_skipped_content entries to skipped_content, skipping duplicates
 --
 -- operates in bulk: 0. swh_mktemp(skipped_content), 1. COPY to tmp_skipped_content,
 -- 2. call this function
 create or replace function swh_skipped_content_add()
     returns void
     language plpgsql
 as $$
 begin
     insert into skipped_content (sha1, sha1_git, sha256, blake2s256, length, status, reason, origin)
         select distinct sha1, sha1_git, sha256, blake2s256, length, status, reason, origin
 	from tmp_skipped_content
 	where (coalesce(sha1, ''), coalesce(sha1_git, ''), coalesce(sha256, '')) in (
             select coalesce(sha1, ''), coalesce(sha1_git, ''), coalesce(sha256, '')
             from swh_skipped_content_missing()
         );
         -- TODO XXX use postgres 9.5 "UPSERT" support here, when available.
         -- Specifically, using "INSERT .. ON CONFLICT IGNORE" we can avoid
         -- the extra swh_content_missing() query here.
     return;
 end
 $$;
 
 -- Update content entries from temporary table.
 -- (columns are potential new columns added to the schema, this cannot be empty)
 --
 create or replace function swh_content_update(columns_update text[])
     returns void
     language plpgsql
 as $$
 declare
    query text;
    tmp_array text[];
 begin
     if array_length(columns_update, 1) = 0 then
         raise exception 'Please, provide the list of column names to update.';
     end if;
 
     tmp_array := array(select format('%1$s=t.%1$s', unnest) from unnest(columns_update));
 
     query = format('update content set %s
                     from tmp_content t where t.sha1 = content.sha1',
                     array_to_string(tmp_array, ', '));
 
     execute query;
 
     return;
 end
 $$;
 
 comment on function swh_content_update(text[]) IS 'Update existing content''s columns';
 
 -- check which entries of tmp_directory are missing from directory
 --
 -- operates in bulk: 0. swh_mktemp(directory), 1. COPY to tmp_directory,
 -- 2. call this function
 create or replace function swh_directory_missing()
     returns setof sha1_git
     language plpgsql
 as $$
 begin
     return query
 	select id from tmp_directory t
 	where not exists (
 	    select 1 from directory d
 	    where d.id = t.id);
     return;
 end
 $$;
 
 
 -- Retrieve information on directory from temporary table
 create or replace function swh_directory_get()
     returns setof directory
     language plpgsql
 as $$
 begin
     return query
 	select d.*
         from tmp_directory t
         inner join directory d on t.id = d.id;
     return;
 end
 $$;
 
 
 create type directory_entry_type as enum('file', 'dir', 'rev');
 
 
 -- Add tmp_directory_entry_* entries to directory_entry_* and directory,
 -- skipping duplicates in directory_entry_*.  This is a generic function that
 -- works on all kind of directory entries.
 --
 -- operates in bulk: 0. swh_mktemp_dir_entry('directory_entry_*'), 1 COPY to
 -- tmp_directory_entry_*, 2. call this function
 --
 -- Assumption: this function is used in the same transaction that inserts the
 -- context directory in table "directory".
 create or replace function swh_directory_entry_add(typ directory_entry_type)
     returns void
     language plpgsql
 as $$
 begin
     execute format('
     insert into directory_entry_%1$s (target, name, perms)
     select distinct t.target, t.name, t.perms
     from tmp_directory_entry_%1$s t
     where not exists (
     select 1
     from directory_entry_%1$s i
     where t.target = i.target and t.name = i.name and t.perms = i.perms)
    ', typ);
 
     execute format('
     with new_entries as (
 	select t.dir_id, array_agg(i.id) as entries
 	from tmp_directory_entry_%1$s t
 	inner join directory_entry_%1$s i
 	using (target, name, perms)
 	group by t.dir_id
     )
     update tmp_directory as d
     set %1$s_entries = new_entries.entries
     from new_entries
     where d.id = new_entries.dir_id
     ', typ);
 
     return;
 end
 $$;
 
 -- Insert the data from tmp_directory, tmp_directory_entry_file,
 -- tmp_directory_entry_dir, tmp_directory_entry_rev into their final
 -- tables.
 --
 -- Prerequisites:
 --  directory ids in tmp_directory
 --  entries in tmp_directory_entry_{file,dir,rev}
 --
 create or replace function swh_directory_add()
     returns void
     language plpgsql
 as $$
 begin
     perform swh_directory_entry_add('file');
     perform swh_directory_entry_add('dir');
     perform swh_directory_entry_add('rev');
 
     insert into directory
     select * from tmp_directory t
     where not exists (
         select 1 from directory d
 	where d.id = t.id);
 
     return;
 end
 $$;
 
 -- a directory listing entry with all the metadata
 --
 -- can be used to list a directory, and retrieve all the data in one go.
 create type directory_entry as
 (
   dir_id   sha1_git,     -- id of the parent directory
   type     directory_entry_type,  -- type of entry
   target   sha1_git,     -- id of target
   name     unix_path,    -- path name, relative to containing dir
   perms    file_perms,   -- unix-like permissions
   status   content_status,  -- visible or absent
   sha1     sha1,            -- content if sha1 if type is not dir
   sha1_git sha1_git,        -- content's sha1 git if type is not dir
   sha256   sha256,          -- content's sha256 if type is not dir
   length   bigint           -- content length if type is not dir
 );
 
 
 -- List a single level of directory walked_dir_id
 -- FIXME: order by name is not correct. For git, we need to order by
 -- lexicographic order but as if a trailing / is present in directory
 -- name
 create or replace function swh_directory_walk_one(walked_dir_id sha1_git)
     returns setof directory_entry
     language sql
     stable
 as $$
     with dir as (
 	select id as dir_id, dir_entries, file_entries, rev_entries
 	from directory
 	where id = walked_dir_id),
     ls_d as (select dir_id, unnest(dir_entries) as entry_id from dir),
     ls_f as (select dir_id, unnest(file_entries) as entry_id from dir),
     ls_r as (select dir_id, unnest(rev_entries) as entry_id from dir)
     (select dir_id, 'dir'::directory_entry_type as type,
             e.target, e.name, e.perms, NULL::content_status,
             NULL::sha1, NULL::sha1_git, NULL::sha256, NULL::bigint
      from ls_d
      left join directory_entry_dir e on ls_d.entry_id = e.id)
     union
     (select dir_id, 'file'::directory_entry_type as type,
             e.target, e.name, e.perms, c.status,
             c.sha1, c.sha1_git, c.sha256, c.length
      from ls_f
      left join directory_entry_file e on ls_f.entry_id = e.id
      left join content c on e.target = c.sha1_git)
     union
     (select dir_id, 'rev'::directory_entry_type as type,
             e.target, e.name, e.perms, NULL::content_status,
             NULL::sha1, NULL::sha1_git, NULL::sha256, NULL::bigint
      from ls_r
      left join directory_entry_rev e on ls_r.entry_id = e.id)
     order by name;
 $$;
 
 -- List recursively the revision directory arborescence
 create or replace function swh_directory_walk(walked_dir_id sha1_git)
     returns setof directory_entry
     language sql
     stable
 as $$
     with recursive entries as (
         select dir_id, type, target, name, perms, status, sha1, sha1_git,
                sha256, length
         from swh_directory_walk_one(walked_dir_id)
         union all
         select dir_id, type, target, (dirname || '/' || name)::unix_path as name,
                perms, status, sha1, sha1_git, sha256, length
         from (select (swh_directory_walk_one(dirs.target)).*, dirs.name as dirname
               from (select target, name from entries where type = 'dir') as dirs) as with_parent
     )
     select dir_id, type, target, name, perms, status, sha1, sha1_git, sha256, length
     from entries
 $$;
 
 create or replace function swh_revision_walk(revision_id sha1_git)
   returns setof directory_entry
   language sql
   stable
 as $$
   select dir_id, type, target, name, perms, status, sha1, sha1_git, sha256, length
   from swh_directory_walk((select directory from revision where id=revision_id))
 $$;
 
 COMMENT ON FUNCTION swh_revision_walk(sha1_git) IS 'Recursively list the revision targeted directory arborescence';
 
 
 -- Find a directory entry by its path
 create or replace function swh_find_directory_entry_by_path(
     walked_dir_id sha1_git,
     dir_or_content_path bytea[])
     returns directory_entry
     language plpgsql
 as $$
 declare
     end_index integer;
     paths bytea default '';
     path bytea;
     res bytea[];
     r record;
 begin
     end_index := array_upper(dir_or_content_path, 1);
     res[1] := walked_dir_id;
 
     for i in 1..end_index
     loop
         path := dir_or_content_path[i];
         -- concatenate path for patching the name in the result record (if we found it)
         if i = 1 then
             paths = path;
         else
             paths := paths || '/' || path;  -- concatenate paths
         end if;
 
         if i <> end_index then
             select *
             from swh_directory_walk_one(res[i] :: sha1_git)
             where name=path
             and type = 'dir'
             limit 1 into r;
         else
             select *
             from swh_directory_walk_one(res[i] :: sha1_git)
             where name=path
             limit 1 into r;
         end if;
 
         -- find the path
         if r is null then
            return null;
         else
             -- store the next dir to lookup the next local path from
             res[i+1] := r.target;
         end if;
     end loop;
 
     -- at this moment, r is the result. Patch its 'name' with the full path before returning it.
     r.name := paths;
     return r;
 end
 $$;
 
 -- List all revision IDs starting from a given revision, going back in time
 --
 -- TODO ordering: should be breadth-first right now (what do we want?)
 -- TODO ordering: ORDER BY parent_rank somewhere?
 create or replace function swh_revision_list(root_revisions bytea[], num_revs bigint default NULL)
     returns table (id sha1_git, parents bytea[])
     language sql
     stable
 as $$
     with recursive full_rev_list(id) as (
         (select id from revision where id = ANY(root_revisions))
         union
         (select h.parent_id
          from revision_history as h
          join full_rev_list on h.id = full_rev_list.id)
     ),
     rev_list as (select id from full_rev_list limit num_revs)
     select rev_list.id as id,
            array(select rh.parent_id::bytea
                  from revision_history rh
                  where rh.id = rev_list.id
                  order by rh.parent_rank
                 ) as parent
     from rev_list;
 $$;
 
 -- List all the children of a given revision
 create or replace function swh_revision_list_children(root_revisions bytea[], num_revs bigint default NULL)
     returns table (id sha1_git, parents bytea[])
     language sql
     stable
 as $$
     with recursive full_rev_list(id) as (
         (select id from revision where id = ANY(root_revisions))
         union
         (select h.id
          from revision_history as h
          join full_rev_list on h.parent_id = full_rev_list.id)
     ),
     rev_list as (select id from full_rev_list limit num_revs)
     select rev_list.id as id,
            array(select rh.parent_id::bytea
                  from revision_history rh
                  where rh.id = rev_list.id
                  order by rh.parent_rank
                 ) as parent
     from rev_list;
 $$;
 
 
 -- Detailed entry for a revision
 create type revision_entry as
 (
   id                             sha1_git,
   date                           timestamptz,
   date_offset                    smallint,
   date_neg_utc_offset            boolean,
   committer_date                 timestamptz,
   committer_date_offset          smallint,
   committer_date_neg_utc_offset  boolean,
   type                           revision_type,
   directory                      sha1_git,
   message                        bytea,
   author_id                      bigint,
   author_fullname                bytea,
   author_name                    bytea,
   author_email                   bytea,
   committer_id                   bigint,
   committer_fullname             bytea,
   committer_name                 bytea,
   committer_email                bytea,
   metadata                       jsonb,
   synthetic                      boolean,
   parents                        bytea[],
   object_id                      bigint
 );
 
 
 -- "git style" revision log. Similar to swh_revision_list(), but returning all
 -- information associated to each revision, and expanding authors/committers
 create or replace function swh_revision_log(root_revisions bytea[], num_revs bigint default NULL)
     returns setof revision_entry
     language sql
     stable
 as $$
     select t.id, r.date, r.date_offset, r.date_neg_utc_offset,
            r.committer_date, r.committer_date_offset, r.committer_date_neg_utc_offset,
            r.type, r.directory, r.message,
            a.id, a.fullname, a.name, a.email,
            c.id, c.fullname, c.name, c.email,
            r.metadata, r.synthetic, t.parents, r.object_id
     from swh_revision_list(root_revisions, num_revs) as t
     left join revision r on t.id = r.id
     left join person a on a.id = r.author
     left join person c on c.id = r.committer;
 $$;
 
 
 -- Retrieve revisions from tmp_bytea in bulk
 create or replace function swh_revision_get()
     returns setof revision_entry
     language plpgsql
 as $$
 begin
     return query
         select r.id, r.date, r.date_offset, r.date_neg_utc_offset,
                r.committer_date, r.committer_date_offset, r.committer_date_neg_utc_offset,
                r.type, r.directory, r.message,
                a.id, a.fullname, a.name, a.email, c.id, c.fullname, c.name, c.email, r.metadata, r.synthetic,
          array(select rh.parent_id::bytea from revision_history rh where rh.id = t.id order by rh.parent_rank)
                    as parents, r.object_id
         from tmp_bytea t
         left join revision r on t.id = r.id
         left join person a on a.id = r.author
         left join person c on c.id = r.committer;
     return;
 end
 $$;
 
 -- List missing revisions from tmp_bytea
 create or replace function swh_revision_missing()
     returns setof sha1_git
     language plpgsql
 as $$
 begin
     return query
         select id::sha1_git from tmp_bytea t
 	where not exists (
 	    select 1 from revision r
 	    where r.id = t.id);
     return;
 end
 $$;
 
 -- Detailed entry for a release
 create type release_entry as
 (
   id                   sha1_git,
   target               sha1_git,
   target_type          object_type,
   date                 timestamptz,
   date_offset          smallint,
   date_neg_utc_offset  boolean,
   name                 bytea,
   comment              bytea,
   synthetic            boolean,
   author_id            bigint,
   author_fullname      bytea,
   author_name          bytea,
   author_email         bytea,
   object_id            bigint
 );
 
 -- Detailed entry for release
 create or replace function swh_release_get()
     returns setof release_entry
     language plpgsql
 as $$
 begin
     return query
         select r.id, r.target, r.target_type, r.date, r.date_offset, r.date_neg_utc_offset, r.name, r.comment,
                r.synthetic, p.id as author_id, p.fullname as author_fullname, p.name as author_name, p.email as author_email, r.object_id
         from tmp_bytea t
         inner join release r on t.id = r.id
         inner join person p on p.id = r.author;
     return;
 end
 $$;
 
 -- Create entries in person from tmp_revision
 create or replace function swh_person_add_from_revision()
     returns void
     language plpgsql
 as $$
 begin
     with t as (
         select author_fullname as fullname, author_name as name, author_email as email from tmp_revision
     union
         select committer_fullname as fullname, committer_name as name, committer_email as email from tmp_revision
     ) insert into person (fullname, name, email)
     select distinct fullname, name, email from t
     where not exists (
         select 1
         from person p
         where t.fullname = p.fullname
     );
     return;
 end
 $$;
 
 
 -- Create entries in revision from tmp_revision
 create or replace function swh_revision_add()
     returns void
     language plpgsql
 as $$
 begin
     perform swh_person_add_from_revision();
 
     insert into revision (id, date, date_offset, date_neg_utc_offset, committer_date, committer_date_offset, committer_date_neg_utc_offset, type, directory, message, author, committer, metadata, synthetic)
     select t.id, t.date, t.date_offset, t.date_neg_utc_offset, t.committer_date, t.committer_date_offset, t.committer_date_neg_utc_offset, t.type, t.directory, t.message, a.id, c.id, t.metadata, t.synthetic
     from tmp_revision t
     left join person a on a.fullname = t.author_fullname
     left join person c on c.fullname = t.committer_fullname;
     return;
 end
 $$;
 
 
 -- List missing releases from tmp_bytea
 create or replace function swh_release_missing()
     returns setof sha1_git
     language plpgsql
 as $$
 begin
   return query
     select id::sha1_git from tmp_bytea t
     where not exists (
       select 1 from release r
       where r.id = t.id);
 end
 $$;
 
 
 -- Create entries in person from tmp_release
 create or replace function swh_person_add_from_release()
     returns void
     language plpgsql
 as $$
 begin
     with t as (
         select distinct author_fullname as fullname, author_name as name, author_email as email from tmp_release
     ) insert into person (fullname, name, email)
     select fullname, name, email from t
     where not exists (
         select 1
         from person p
         where t.fullname = p.fullname
     );
     return;
 end
 $$;
 
 
 -- Create entries in release from tmp_release
 create or replace function swh_release_add()
     returns void
     language plpgsql
 as $$
 begin
     perform swh_person_add_from_release();
 
     insert into release (id, target, target_type, date, date_offset, date_neg_utc_offset, name, comment, author, synthetic)
     select t.id, t.target, t.target_type, t.date, t.date_offset, t.date_neg_utc_offset, t.name, t.comment, a.id, t.synthetic
     from tmp_release t
     left join person a on a.fullname = t.author_fullname;
     return;
 end
 $$;
 
 create or replace function swh_occurrence_update_for_origin(origin_id bigint)
   returns void
   language sql
 as $$
   delete from occurrence where origin = origin_id;
   insert into occurrence (origin, branch, target, target_type)
     select origin, branch, target, target_type
     from occurrence_history
     where origin = origin_id and
           (select visit from origin_visit
            where origin = origin_id
            order by date desc
            limit 1) = any(visits);
 $$;
 
 create or replace function swh_occurrence_update_all()
   returns void
   language plpgsql
 as $$
 declare
   origin_id origin.id%type;
 begin
   for origin_id in
     select distinct id from origin
   loop
     perform swh_occurrence_update_for_origin(origin_id);
   end loop;
   return;
 end;
 $$;
 
 -- add a new origin_visit for origin origin_id at date.
 --
 -- Returns the new visit id.
 create or replace function swh_origin_visit_add(origin_id bigint, date timestamptz)
     returns bigint
     language sql
 as $$
   with last_known_visit as (
     select coalesce(max(visit), 0) as visit
     from origin_visit
     where origin = origin_id
   )
   insert into origin_visit (origin, date, visit, status)
   values (origin_id, date, (select visit from last_known_visit) + 1, 'ongoing')
   returning visit;
 $$;
 
 -- add tmp_occurrence_history entries to occurrence_history
 --
 -- operates in bulk: 0. swh_mktemp(occurrence_history), 1. COPY to tmp_occurrence_history,
 -- 2. call this function
 create or replace function swh_occurrence_history_add()
     returns void
     language plpgsql
 as $$
 declare
   origin_id origin.id%type;
 begin
   -- Create or update occurrence_history
   with occurrence_history_id_visit as (
     select tmp_occurrence_history.*, object_id, visits from tmp_occurrence_history
     left join occurrence_history using(origin, branch, target, target_type)
   ),
   occurrences_to_update as (
     select object_id, visit from occurrence_history_id_visit where object_id is not null
   ),
   update_occurrences as (
     update occurrence_history
     set visits = array(select unnest(occurrence_history.visits) as e
                         union
                        select occurrences_to_update.visit as e
                        order by e)
     from occurrences_to_update
     where occurrence_history.object_id = occurrences_to_update.object_id
   )
   insert into occurrence_history (origin, branch, target, target_type, visits)
     select origin, branch, target, target_type, ARRAY[visit]
       from occurrence_history_id_visit
       where object_id is null;
 
   -- update occurrence
   for origin_id in
     select distinct origin from tmp_occurrence_history
   loop
     perform swh_occurrence_update_for_origin(origin_id);
   end loop;
   return;
 end
 $$;
 
 create or replace function swh_snapshot_add(origin bigint, visit bigint, snapshot_id snapshot.id%type)
   returns void
   language plpgsql
 as $$
 declare
   snapshot_object_id snapshot.object_id%type;
 begin
   select object_id from snapshot where id = snapshot_id into snapshot_object_id;
   if snapshot_object_id is null then
      insert into snapshot (id) values (snapshot_id) returning object_id into snapshot_object_id;
-     with all_branches(name, target_type, target) as (
-       select name, target_type, target from tmp_snapshot_branch
-     ), inserted as (
-       insert into snapshot_branch (name, target_type, target)
-       select name, target_type, target from all_branches
-       on conflict do nothing
-       returning object_id
-     )
+     insert into snapshot_branch (name, target_type, target)
+       select name, target_type, target from tmp_snapshot_branch tmp
+       where not exists (
+         select 1
+         from snapshot_branch sb
+         where sb.name = tmp.name
+           and sb.target = tmp.target
+           and sb.target_type = tmp.target_type
+       )
+       on conflict do nothing;
      insert into snapshot_branches (snapshot_id, branch_id)
-     select snapshot_object_id, object_id as branch_id from inserted
-     union all
-     select snapshot_object_id, object_id as branch_id
-       from all_branches ab
+     select snapshot_object_id, sb.object_id as branch_id
+       from tmp_snapshot_branch tmp
        join snapshot_branch sb
-         on sb.name = ab.name
-           and sb.target_type is not distinct from ab.target_type
-           and sb.target is not distinct from ab.target;
+       using (name, target, target_type)
+       where tmp.target is not null and tmp.target_type is not null
+     union
+     select snapshot_object_id, sb.object_id as branch_id
+       from tmp_snapshot_branch tmp
+       join snapshot_branch sb
+       using (name)
+       where tmp.target is null and tmp.target_type is null
+         and sb.target is null and sb.target_type is null;
   end if;
   update origin_visit ov
     set snapshot_id = snapshot_object_id
     where ov.origin=swh_snapshot_add.origin and ov.visit=swh_snapshot_add.visit;
 end;
 $$;
 
 create type snapshot_result as (
   snapshot_id  sha1_git,
   name         bytea,
   target       bytea,
   target_type  snapshot_target
 );
 
 create or replace function swh_snapshot_get_by_id(id snapshot.id%type)
   returns setof snapshot_result
   language sql
   stable
 as $$
   select
     swh_snapshot_get_by_id.id as snapshot_id, name, target, target_type
   from snapshot_branches
   inner join snapshot_branch on snapshot_branches.branch_id = snapshot_branch.object_id
   where snapshot_id = (select object_id from snapshot where snapshot.id = swh_snapshot_get_by_id.id)
 $$;
 
 create or replace function swh_snapshot_get_by_origin_visit(origin_id bigint, visit_id bigint)
   returns snapshot.id%type
   language sql
   stable
 as $$
   select snapshot.id
   from origin_visit
   left join snapshot
   on snapshot.object_id = origin_visit.snapshot_id
   where origin_visit.origin=origin_id and origin_visit.visit=visit_id;
 $$;
 
 -- Absolute path: directory reference + complete path relative to it
 create type content_dir as (
     directory  sha1_git,
     path       unix_path
 );
 
 
 -- Find the containing directory of a given content, specified by sha1
 -- (note: *not* sha1_git).
 --
 -- Return a pair (dir_it, path) where path is a UNIX path that, from the
 -- directory root, reach down to a file with the desired content. Return NULL
 -- if no match is found.
 --
 -- In case of multiple paths (i.e., pretty much always), an arbitrary one is
 -- chosen.
 create or replace function swh_content_find_directory(content_id sha1)
     returns content_dir
     language sql
     stable
 as $$
     with recursive path as (
 	-- Recursively build a path from the requested content to a root
 	-- directory. Each iteration returns a pair (dir_id, filename) where
 	-- filename is relative to dir_id. Stops when no parent directory can
 	-- be found.
 	(select dir.id as dir_id, dir_entry_f.name as name, 0 as depth
 	 from directory_entry_file as dir_entry_f
 	 join content on content.sha1_git = dir_entry_f.target
 	 join directory as dir on dir.file_entries @> array[dir_entry_f.id]
 	 where content.sha1 = content_id
 	 limit 1)
 	union all
 	(select dir.id as dir_id,
 		(dir_entry_d.name || '/' || path.name)::unix_path as name,
 		path.depth + 1
 	 from path
 	 join directory_entry_dir as dir_entry_d on dir_entry_d.target = path.dir_id
 	 join directory as dir on dir.dir_entries @> array[dir_entry_d.id]
 	 limit 1)
     )
     select dir_id, name from path order by depth desc limit 1;
 $$;
 
 
 -- Walk the revision history starting from a given revision, until a matching
 -- occurrence is found. Return all occurrence information if one is found, NULL
 -- otherwise.
 create or replace function swh_revision_find_occurrence(revision_id sha1_git)
     returns occurrence
     language sql
     stable
 as $$
 	select origin, branch, target, target_type
   from swh_revision_list_children(ARRAY[revision_id] :: bytea[]) as rev_list
 	left join occurrence_history occ_hist
   on rev_list.id = occ_hist.target
 	where occ_hist.origin is not null and
         occ_hist.target_type = 'revision'
 	limit 1;
 $$;
 
 -- Find the visit of origin id closest to date visit_date
 create or replace function swh_visit_find_by_date(origin bigint, visit_date timestamptz default NOW())
     returns origin_visit
     language sql
     stable
 as $$
   with closest_two_visits as ((
     select ov, (date - visit_date) as interval
     from origin_visit ov
     where ov.origin = origin
           and ov.date >= visit_date
     order by ov.date asc
     limit 1
   ) union (
     select ov, (visit_date - date) as interval
     from origin_visit ov
     where ov.origin = origin
           and ov.date < visit_date
     order by ov.date desc
     limit 1
   )) select (ov).* from closest_two_visits order by interval limit 1
 $$;
 
 -- Find the visit of origin id closest to date visit_date
 create or replace function swh_visit_get(origin bigint)
     returns origin_visit
     language sql
     stable
 as $$
     select *
     from origin_visit
     where origin=origin
     order by date desc
 $$;
 
 
 -- Retrieve occurrence by filtering on origin_id and optionally on
 -- branch_name and/or validity range
 create or replace function swh_occurrence_get_by(
        origin_id bigint,
        branch_name bytea default NULL,
        date timestamptz default NULL)
     returns setof occurrence_history
     language plpgsql
 as $$
 declare
     filters text[] := array[] :: text[];  -- AND-clauses used to filter content
     visit_id bigint;
     q text;
 begin
     if origin_id is null then
         raise exception 'Needs an origin_id to get an occurrence.';
     end if;
     filters := filters || format('origin = %L', origin_id);
     if branch_name is not null then
         filters := filters || format('branch = %L', branch_name);
     end if;
     if date is not null then
         select visit from swh_visit_find_by_date(origin_id, date) into visit_id;
     else
         select visit from origin_visit where origin = origin_id order by origin_visit.date desc limit 1 into visit_id;
     end if;
     if visit_id is null then
         return;
     end if;
     filters := filters || format('%L = any(visits)', visit_id);
 
     q = format('select * from occurrence_history where %s',
                array_to_string(filters, ' and '));
     return query execute q;
 end
 $$;
 
 
 -- Retrieve revisions by occurrence criterion filtering
 create or replace function swh_revision_get_by(
        origin_id bigint,
        branch_name bytea default NULL,
        date timestamptz default NULL)
     returns setof revision_entry
     language sql
     stable
 as $$
     select r.id, r.date, r.date_offset, r.date_neg_utc_offset,
         r.committer_date, r.committer_date_offset, r.committer_date_neg_utc_offset,
         r.type, r.directory, r.message,
         a.id, a.fullname, a.name, a.email, c.id, c.fullname, c.name, c.email, r.metadata, r.synthetic,
         array(select rh.parent_id::bytea
             from revision_history rh
             where rh.id = r.id
             order by rh.parent_rank
         ) as parents, r.object_id
     from swh_occurrence_get_by(origin_id, branch_name, date) as occ
     inner join revision r on occ.target = r.id
     left join person a on a.id = r.author
     left join person c on c.id = r.committer;
 $$;
 
 -- Retrieve a release by occurrence criterion
 create or replace function swh_release_get_by(
        origin_id bigint)
     returns setof release_entry
     language sql
     stable
 as $$
    select r.id, r.target, r.target_type, r.date, r.date_offset, r.date_neg_utc_offset,
         r.name, r.comment, r.synthetic, a.id as author_id, a.fullname as author_fullname,
         a.name as author_name, a.email as author_email, r.object_id
     from release r
     inner join occurrence_history occ on occ.target = r.target
     left join person a on a.id = r.author
     where occ.origin = origin_id and occ.target_type = 'revision' and r.target_type = 'revision';
 $$;
 
 
 create type object_found as (
     sha1_git   sha1_git,
     type       object_type,
     id         bytea,       -- sha1 or sha1_git depending on object_type
     object_id  bigint
 );
 
 -- Find objects by sha1_git, return their type and their main identifier
 create or replace function swh_object_find_by_sha1_git()
     returns setof object_found
     language plpgsql
 as $$
 begin
     return query
     with known_objects as ((
         select id as sha1_git, 'release'::object_type as type, id, object_id from release r
         where exists (select 1 from tmp_bytea t where t.id = r.id)
     ) union all (
         select id as sha1_git, 'revision'::object_type as type, id, object_id from revision r
         where exists (select 1 from tmp_bytea t where t.id = r.id)
     ) union all (
         select id as sha1_git, 'directory'::object_type as type, id, object_id from directory d
         where exists (select 1 from tmp_bytea t where t.id = d.id)
     ) union all (
         select sha1_git as sha1_git, 'content'::object_type as type, sha1 as id, object_id from content c
         where exists (select 1 from tmp_bytea t where t.id = c.sha1_git)
     ))
     select t.id::sha1_git as sha1_git, k.type, k.id, k.object_id from tmp_bytea t
       left join known_objects k on t.id = k.sha1_git;
 end
 $$;
 
 -- Create entries in entity_history from tmp_entity_history
 --
 -- TODO: do something smarter to compress the entries if the data
 -- didn't change.
 create or replace function swh_entity_history_add()
     returns void
     language plpgsql
 as $$
 begin
     insert into entity_history (
         uuid, parent, name, type, description, homepage, active, generated, lister_metadata, metadata, validity
     ) select * from tmp_entity_history;
     return;
 end
 $$;
 
 
 create or replace function swh_update_entity_from_entity_history()
     returns trigger
     language plpgsql
 as $$
 begin
     insert into entity (uuid, parent, name, type, description, homepage, active, generated,
       lister_metadata, metadata, last_seen, last_id)
       select uuid, parent, name, type, description, homepage, active, generated,
              lister_metadata, metadata, unnest(validity), id
       from entity_history
       where uuid = NEW.uuid
       order by unnest(validity) desc limit 1
     on conflict (uuid) do update set
       parent = EXCLUDED.parent,
       name = EXCLUDED.name,
       type = EXCLUDED.type,
       description = EXCLUDED.description,
       homepage = EXCLUDED.homepage,
       active = EXCLUDED.active,
       generated = EXCLUDED.generated,
       lister_metadata = EXCLUDED.lister_metadata,
       metadata = EXCLUDED.metadata,
       last_seen = EXCLUDED.last_seen,
       last_id = EXCLUDED.last_id;
 
     return null;
 end
 $$;
 
 create trigger update_entity
   after insert or update
   on entity_history
   for each row
   execute procedure swh_update_entity_from_entity_history();
 
 -- map an id of tmp_entity_lister to a full entity
 create type entity_id as (
     id               bigint,
     uuid             uuid,
     parent           uuid,
     name             text,
     type             entity_type,
     description      text,
     homepage         text,
     active           boolean,
     generated        boolean,
     lister_metadata  jsonb,
     metadata         jsonb,
     last_seen        timestamptz,
     last_id          bigint
 );
 
 -- find out the uuid of the entries of entity with the metadata
 -- contained in tmp_entity_lister
 create or replace function swh_entity_from_tmp_entity_lister()
     returns setof entity_id
     language plpgsql
 as $$
 begin
   return query
     select t.id, e.*
     from tmp_entity_lister t
     left join entity e
     on e.lister_metadata @> t.lister_metadata;
   return;
 end
 $$;
 
 create or replace function swh_entity_get(entity_uuid uuid)
     returns setof entity
     language sql
     stable
 as $$
   with recursive entity_hierarchy as (
   select e.*
     from entity e where uuid = entity_uuid
     union
     select p.*
     from entity_hierarchy e
     join entity p on e.parent = p.uuid
   )
   select *
   from entity_hierarchy;
 $$;
 
 
 -- Object listing by object_id
 
 create or replace function swh_content_list_by_object_id(
     min_excl bigint,
     max_incl bigint
 )
     returns setof content
     language sql
     stable
 as $$
     select * from content
     where object_id > min_excl and object_id <= max_incl
     order by object_id;
 $$;
 
 create or replace function swh_revision_list_by_object_id(
     min_excl bigint,
     max_incl bigint
 )
     returns setof revision_entry
     language sql
     stable
 as $$
     with revs as (
         select * from revision
         where object_id > min_excl and object_id <= max_incl
     )
     select r.id, r.date, r.date_offset, r.date_neg_utc_offset,
            r.committer_date, r.committer_date_offset, r.committer_date_neg_utc_offset,
            r.type, r.directory, r.message,
            a.id, a.fullname, a.name, a.email, c.id, c.fullname, c.name, c.email, r.metadata, r.synthetic,
            array(select rh.parent_id::bytea from revision_history rh where rh.id = r.id order by rh.parent_rank)
                as parents, r.object_id
     from revs r
     left join person a on a.id = r.author
     left join person c on c.id = r.committer
     order by r.object_id;
 $$;
 
 create or replace function swh_release_list_by_object_id(
     min_excl bigint,
     max_incl bigint
 )
     returns setof release_entry
     language sql
     stable
 as $$
     with rels as (
         select * from release
         where object_id > min_excl and object_id <= max_incl
     )
     select r.id, r.target, r.target_type, r.date, r.date_offset, r.date_neg_utc_offset, r.name, r.comment,
            r.synthetic, p.id as author_id, p.fullname as author_fullname, p.name as author_name, p.email as author_email, r.object_id
     from rels r
     left join person p on p.id = r.author
     order by r.object_id;
 $$;
 
 
 create or replace function swh_occurrence_by_origin_visit(origin_id bigint, visit_id bigint)
     returns setof occurrence
     language sql
     stable
 as $$
   select origin, branch, target, target_type from occurrence_history
   where origin = origin_id and visit_id = ANY(visits);
 $$;
 
 -- end revision_metadata functions
 -- origin_metadata functions
 create type origin_metadata_signature as (
     id bigint,
     origin_id bigint,
     discovery_date timestamptz,
     tool_id bigint,
     metadata jsonb,
     provider_id integer,
     provider_name text,
     provider_type text,
     provider_url  text
 );
 create or replace function swh_origin_metadata_get_by_origin(
        origin integer)
     returns setof origin_metadata_signature
     language sql
     stable
 as $$
     select om.id as id, origin_id, discovery_date, tool_id, om.metadata,
            mp.id as provider_id, provider_name, provider_type, provider_url
     from origin_metadata as om
     inner join metadata_provider mp on om.provider_id = mp.id
     where om.origin_id = origin
     order by discovery_date desc;
 $$;
 
 create or replace function swh_origin_metadata_get_by_provider_type(
        origin integer,
        type text)
     returns setof origin_metadata_signature
     language sql
     stable
 as $$
     select om.id as id, origin_id, discovery_date, tool_id, om.metadata,
            mp.id as provider_id, provider_name, provider_type, provider_url
     from origin_metadata as om
     inner join metadata_provider mp on om.provider_id = mp.id
     where om.origin_id = origin
     and mp.provider_type = type
     order by discovery_date desc;
 $$;
 -- end origin_metadata functions
 
 -- add tmp_tool entries to tool,
 -- skipping duplicates if any.
 --
 -- operates in bulk: 0. create temporary tmp_tool, 1. COPY to
 -- it, 2. call this function to insert and filtering out duplicates
 create or replace function swh_tool_add()
     returns setof tool
     language plpgsql
 as $$
 begin
       insert into tool(name, version, configuration)
       select name, version, configuration from tmp_tool tmp
       on conflict(name, version, configuration) do nothing;
 
       return query
           select id, name, version, configuration
           from tmp_tool join tool
               using(name, version, configuration);
 
       return;
 end
 $$;
 
 
 -- simple counter mapping a textual label to an integer value
 create type counter as (
     label  text,
     value  bigint
 );
 
 -- return statistics about the number of tuples in various SWH tables
 --
 -- Note: the returned values are based on postgres internal statistics
 -- (pg_class table), which are only updated daily (by autovacuum) or so
 create or replace function swh_stat_counters()
     returns setof counter
     language sql
     stable
 as $$
     select object_type as label, value as value
     from object_counts
     where object_type in (
         'content',
         'directory',
         'directory_entry_dir',
         'directory_entry_file',
         'directory_entry_rev',
         'occurrence',
         'occurrence_history',
         'origin',
         'origin_visit',
         'person',
         'entity',
         'entity_history',
         'release',
         'revision',
         'revision_history',
         'skipped_content'
     );
 $$;
 
 create or replace function swh_update_counter(object_type text)
     returns void
     language plpgsql
 as $$
 begin
     execute format('
 	insert into object_counts
     (value, last_update, object_type)
   values
     ((select count(*) from %1$I), NOW(), %1$L)
   on conflict (object_type) do update set
     value = excluded.value,
     last_update = excluded.last_update',
   object_type);
     return;
 end;
 $$;
diff --git a/sql/swh-schema.sql b/sql/swh-schema.sql
index 975be8f28..df4553d28 100644
--- a/sql/swh-schema.sql
+++ b/sql/swh-schema.sql
@@ -1,434 +1,434 @@
 ---
 --- Software Heritage Data Model
 ---
 
 -- drop schema if exists swh cascade;
 -- create schema swh;
 -- set search_path to swh;
 
 create table dbversion
 (
   version     int primary key,
   release     timestamptz,
   description text
 );
 
 insert into dbversion(version, release, description)
-      values(116, now(), 'Work In Progress');
+      values(117, now(), 'Work In Progress');
 
 -- a SHA1 checksum (not necessarily originating from Git)
 create domain sha1 as bytea check (length(value) = 20);
 
 -- a Git object ID, i.e., a SHA1 checksum
 create domain sha1_git as bytea check (length(value) = 20);
 
 -- a SHA256 checksum
 create domain sha256 as bytea check (length(value) = 32);
 
 -- a blake2 checksum
 create domain blake2s256 as bytea check (length(value) = 32);
 
 -- UNIX path (absolute, relative, individual path component, etc.)
 create domain unix_path as bytea;
 
 -- a set of UNIX-like access permissions, as manipulated by, e.g., chmod
 create domain file_perms as int;
 
 -- Checksums about actual file content. Note that the content itself is not
 -- stored in the DB, but on external (key-value) storage. A single checksum is
 -- used as key there, but the other can be used to verify that we do not inject
 -- content collisions not knowingly.
 create table content
 (
   sha1       sha1 not null,
   sha1_git   sha1_git not null,
   sha256     sha256 not null,
   blake2s256 blake2s256,
   length     bigint not null,
   ctime      timestamptz not null default now(),
              -- creation time, i.e. time of (first) injection into the storage
   status     content_status not null default 'visible',
   object_id  bigserial
 );
 
 
 -- Entities constitute a typed hierarchy of organization, hosting
 -- facilities, groups, people and software projects.
 --
 -- Examples of entities: Software Heritage, Debian, GNU, GitHub,
 -- Apache, The Linux Foundation, the Debian Python Modules Team, the
 -- torvalds GitHub user, the torvalds/linux GitHub project.
 --
 -- The data model is hierarchical (via the parent attribute) and might
 -- store sub-branches of existing entities. The key feature of an
 -- entity is might be *listed* (if it is available in listable_entity)
 -- to retrieve information about its content, i.e: sub-entities,
 -- projects, origins.
 
 -- The history of entities. Allows us to keep historical metadata
 -- about entities.  The temporal invariant is the uuid. Root
 -- organization uuids are manually generated (and available in
 -- swh-data.sql).
 --
 -- For generated entities (generated = true), we can provide
 -- generation_metadata to allow listers to retrieve the uuids of previous
 -- iterations of the entity.
 --
 -- Inactive entities that have been active in the past (active =
 -- false) should register the timestamp at which we saw them
 -- deactivate, in a new entry of entity_history.
 create table entity_history
 (
   id               bigserial not null,
   uuid             uuid,
   parent           uuid,             -- should reference entity_history(uuid)
   name             text not null,
   type             entity_type not null,
   description      text,
   homepage         text,
   active           boolean not null, -- whether the entity was seen on the last listing
   generated        boolean not null, -- whether this entity has been generated by a lister
   lister_metadata  jsonb,            -- lister-specific metadata, used for queries
   metadata         jsonb,
   validity         timestamptz[]     -- timestamps at which we have seen this entity
 );
 
 -- The entity table provides a view of the latest information on a
 -- given entity. It is updated via a trigger on entity_history.
 create table entity
 (
   uuid             uuid not null,
   parent           uuid,
   name             text not null,
   type             entity_type not null,
   description      text,
   homepage         text,
   active           boolean not null, -- whether the entity was seen on the last listing
   generated        boolean not null, -- whether this entity has been generated by a lister
   lister_metadata  jsonb,            -- lister-specific metadata, used for queries
   metadata         jsonb,
   last_seen        timestamptz,      -- last listing time or disappearance time for active=false
   last_id          bigint            -- last listing id
 );
 
 -- Register the equivalence between two entities. Allows sideways
 -- navigation in the entity table
 create table entity_equivalence
 (
   entity1 uuid,
   entity2 uuid
 );
 
 -- Register a lister for a specific entity.
 create table listable_entity
 (
   uuid         uuid,
   enabled      boolean not null default true, -- do we list this entity automatically?
   list_engine  text,  -- crawler to be used to list entity's content
   list_url     text,  -- root URL to start the listing
   list_params  jsonb,  -- org-specific listing parameter
   latest_list  timestamptz  -- last time the entity's content has been listed
 );
 
 -- Log of all entity listings (i.e., entity crawling) that have been
 -- done in the past, or are still ongoing.
 create table list_history
 (
   id        bigserial not null,
   date      timestamptz not null,
   status    boolean,   -- true if and only if the listing has been successful
   result    jsonb,     -- more detailed return value, depending on status
   stdout    text,
   stderr    text,
   duration  interval,  -- fetch duration of NULL if still ongoing
   entity    uuid
 );
 
 
 -- An origin is a place, identified by an URL, where software can be found. We
 -- support different kinds of origins, e.g., git and other VCS repositories,
 -- web pages that list tarballs URLs (e.g., http://www.kernel.org), indirect
 -- tarball URLs (e.g., http://www.example.org/latest.tar.gz), etc. The key
 -- feature of an origin is that it can be *fetched* (wget, git clone, svn
 -- checkout, etc.) to retrieve all the contained software.
 create table origin
 (
   id       bigserial not null,
   type     text, -- TODO use an enum here (?)
   url      text not null,
   lister   uuid,
   project  uuid
 );
 
 -- Content we have seen but skipped for some reason. This table is
 -- separate from the content table as we might not have the sha1
 -- checksum of that data (for instance when we inject git
 -- repositories, objects that are too big will be skipped here, and we
 -- will only know their sha1_git). 'reason' contains the reason the
 -- content was skipped. origin is a nullable column allowing to find
 -- out which origin contains that skipped content.
 create table skipped_content
 (
   sha1       sha1,
   sha1_git   sha1_git,
   sha256     sha256,
   blake2s256 blake2s256,
   length     bigint not null,
   ctime      timestamptz not null default now(),
   status     content_status not null default 'absent',
   reason     text not null,
   origin     bigint,
   object_id  bigserial
 );
 
 -- Log of all origin fetches (i.e., origin crawling) that have been done in the
 -- past, or are still ongoing. Similar to list_history, but for origins.
 create table fetch_history
 (
   id        bigserial,
   origin    bigint,
   date      timestamptz not null,
   status    boolean,  -- true if and only if the fetch has been successful
   result    jsonb,     -- more detailed returned values, times, etc...
   stdout    text,
   stderr    text,     -- null when status is true, filled otherwise
   duration  interval  -- fetch duration of NULL if still ongoing
 );
 
 
 -- A file-system directory.  A directory is a list of directory entries (see
 -- tables: directory_entry_{dir,file}).
 --
 -- To list the contents of a directory:
 -- 1. list the contained directory_entry_dir using array dir_entries
 -- 2. list the contained directory_entry_file using array file_entries
 -- 3. list the contained directory_entry_rev using array rev_entries
 -- 4. UNION
 --
 -- Synonyms/mappings:
 -- * git: tree
 create table directory
 (
   id            sha1_git,
   dir_entries   bigint[],  -- sub-directories, reference directory_entry_dir
   file_entries  bigint[],  -- contained files, reference directory_entry_file
   rev_entries   bigint[],  -- mounted revisions, reference directory_entry_rev
   object_id     bigserial  -- short object identifier
 );
 
 -- A directory entry pointing to a sub-directory.
 create table directory_entry_dir
 (
   id      bigserial,
   target  sha1_git,   -- id of target directory
   name    unix_path,  -- path name, relative to containing dir
   perms   file_perms  -- unix-like permissions
 );
 
 -- A directory entry pointing to a file.
 create table directory_entry_file
 (
   id      bigserial,
   target  sha1_git,   -- id of target file
   name    unix_path,  -- path name, relative to containing dir
   perms   file_perms  -- unix-like permissions
 );
 
 -- A directory entry pointing to a revision.
 create table directory_entry_rev
 (
   id      bigserial,
   target  sha1_git,   -- id of target revision
   name    unix_path,  -- path name, relative to containing dir
   perms   file_perms  -- unix-like permissions
 );
 
 create table person
 (
   id        bigserial,
   name      bytea,          -- advisory: not null if we managed to parse a name
   email     bytea,          -- advisory: not null if we managed to parse an email
   fullname  bytea not null  -- freeform specification; what is actually used in the checksums
                             --     will usually be of the form 'name <email>'
 );
 
 -- A snapshot of a software project at a specific point in time.
 --
 -- Synonyms/mappings:
 -- * git / subversion / etc: commit
 -- * tarball: a specific tarball
 --
 -- Revisions are organized as DAGs. Each revision points to 0, 1, or more (in
 -- case of merges) parent revisions. Each revision points to a directory, i.e.,
 -- a file-system tree containing files and directories.
 create table revision
 (
   id                    sha1_git,
   date                  timestamptz,
   date_offset           smallint,
   committer_date        timestamptz,
   committer_date_offset smallint,
   type                  revision_type not null,
   directory             sha1_git,  -- file-system tree
   message               bytea,
   author                bigint,
   committer             bigint,
   synthetic             boolean not null default false,  -- true if synthetic (cf. swh-loader-tar)
   metadata              jsonb, -- extra metadata (tarball checksums, extra commit information, etc...)
   object_id             bigserial,
   date_neg_utc_offset   boolean,
   committer_date_neg_utc_offset boolean
 );
 
 
 -- either this table or the sha1_git[] column on the revision table
 create table revision_history
 (
   id           sha1_git,
   parent_id    sha1_git,
   parent_rank  int not null default 0
     -- parent position in merge commits, 0-based
 );
 
 -- The timestamps at which Software Heritage has made a visit of the given origin.
 create table origin_visit
 (
   origin       bigint not null,
   visit        bigint not null,
   date         timestamptz not null,
   status       origin_visit_status not null,
   metadata     jsonb,
   snapshot_id  bigint
 );
 
 comment on column origin_visit.origin is 'Visited origin';
 comment on column origin_visit.visit is 'Visit number the visit occurred for that origin';
 comment on column origin_visit.date is 'Visit date for that origin';
 comment on column origin_visit.status is 'Visit status for that origin';
 comment on column origin_visit.metadata is 'Metadata associated with the visit';
 comment on column origin_visit.snapshot_id is 'id of the snapshot associated with the visit';
 
 
 -- The content of software origins is indexed starting from top-level pointers
 -- called "branches". Every time we fetch some origin we store in this table
 -- where the branches pointed to at fetch time.
 --
 -- Synonyms/mappings:
 -- * git: ref (in the "git update-ref" sense)
 create table occurrence_history
 (
   origin       bigint not null,
   branch       bytea not null,        -- e.g., b"master" (for VCS), or b"sid" (for Debian)
   target       sha1_git not null,     -- ref target, e.g., commit id
   target_type  object_type not null,  -- ref target type
   visits       bigint[] not null,     -- the visits where that occurrence was valid. References
                                       -- origin_visit(visit), where o_h.origin = origin_visit.origin.
   object_id    bigserial not null,    -- short object identifier
   snapshot_branch_id bigint
 );
 
 -- Materialized view of occurrence_history, storing the *current* value of each
 -- branch, as last seen by SWH.
 create table occurrence
 (
   origin    bigint,
   branch    bytea not null,
   target    sha1_git not null,
   target_type object_type not null
 );
 
 
 create table snapshot (
   object_id  bigserial not null,
   id         sha1_git
 );
 
 create table snapshot_branch (
   object_id    bigserial not null,
   name         bytea not null,
   target       bytea,
   target_type  snapshot_target
 );
 
 create table snapshot_branches (
   snapshot_id  bigint not null,
   branch_id    bigint not null
 );
 
 
 -- A "memorable" point in the development history of a project.
 --
 -- Synonyms/mappings:
 -- * git: tag (of the annotated kind, otherwise they are just references)
 -- * tarball: the release version number
 create table release
 (
   id          sha1_git not null,
   target      sha1_git,
   date        timestamptz,
   date_offset smallint,
   name        bytea,
   comment     bytea,
   author      bigint,
   synthetic   boolean not null default false,  -- true if synthetic (cf. swh-loader-tar)
   object_id   bigserial,
   target_type object_type not null,
   date_neg_utc_offset  boolean
 );
 
 -- Tools
 
 create table tool (
   id serial not null,
   name text not null,
   version text not null,
   configuration jsonb
 );
 
 comment on table tool is 'Tool information';
 comment on column tool.id is 'Tool identifier';
 comment on column tool.version is 'Tool name';
 comment on column tool.version is 'Tool version';
 comment on column tool.configuration is 'Tool configuration: command line, flags, etc...';
 
 
 create table metadata_provider (
   id            serial not null,
   provider_name text   not null,
   provider_type text   not null,
   provider_url  text,
   metadata      jsonb
 );
 
 comment on table metadata_provider is 'Metadata provider information';
 comment on column metadata_provider.id is 'Provider''s identifier';
 comment on column metadata_provider.provider_name is 'Provider''s name';
 comment on column metadata_provider.provider_url is 'Provider''s url';
 comment on column metadata_provider.metadata is 'Other metadata about provider';
 
 
 -- Discovery of metadata during a listing, loading, deposit or external_catalog of an origin
 -- also provides a translation to a defined json schema using a translation tool (tool_id)
 create table origin_metadata(
   id             bigserial     not null,  -- PK object identifier
   origin_id      bigint        not null, -- references origin(id)
   discovery_date timestamptz   not null, -- when it was extracted
   provider_id    bigint        not null, -- ex: 'hal', 'lister-github', 'loader-github'
   tool_id        bigint        not null,
   metadata       jsonb         not null
 );
 
 comment on table origin_metadata is 'keeps all metadata found concerning an origin';
 comment on column origin_metadata.id is 'the origin_metadata object''s id';
 comment on column origin_metadata.origin_id is 'the origin id for which the metadata was found';
 comment on column origin_metadata.discovery_date is 'the date of retrieval';
 comment on column origin_metadata.provider_id is 'the metadata provider: github, openhub, deposit, etc.';
 comment on column origin_metadata.tool_id is 'the tool used for extracting metadata: lister-github, etc.';
 comment on column origin_metadata.metadata is 'metadata in json format but with original terms';
 
 -- Keep a cache of object counts
 create table object_counts (
   object_type text,
   value bigint,
   last_update timestamptz
 );