text: -- CREATE SCHEMA components;
  -- SET search_path TO components;
  CREATE TABLE IF NOT EXISTS untracked (id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, ingested_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP, data jsonb NOT NULL);
  
  -- To reduce the index size, only index the fields we query
  CREATE INDEX CONCURRENTLY IF NOT EXISTS idxdata_hashes_pathops ON untracked USING GIN ((data->'hashes') jsonb_path_ops);
  CREATE INDEX CONCURRENTLY IF NOT EXISTS idxdata_lang ON untracked USING GIN ((data->>'lang'));
  CREATE UNIQUE INDEX idx_unique_versions ON untracked((data->>'name'),(data->>'version'),(data->>'lang'));
  
  
  CREATE OR REPLACE FUNCTION find_component_by_hash(hash TEXT)
  RETURNS TABLE (name text, version text) AS $$
  DECLARE
    q text;
  BEGIN
  q = 'SELECT data->>''name'' AS name, data->>''version'' AS version FROM untracked WHERE data->''hashes'' @> ''[{"sha256": ' || to_json(hash) || '}]''';
  RETURN QUERY EXECUTE q;
  END; $$
  LANGUAGE 'plpgsql' STABLE STRICT LEAKPROOF PARALLEL SAFE SECURITY DEFINER;
  
  CREATE OR REPLACE FUNCTION find_component_by_hash(hash TEXT, lang TEXT)
  RETURNS TABLE (name text, version text) AS $$
  DECLARE
    q text;
  BEGIN
  q = 'SELECT data->>''name'' AS name, data->>''version'' AS version FROM untracked WHERE data->''hashes'' @> ''[{"sha256": ' || to_json(hash) || '}]'' AND data->>''lang'' = ' || quote_literal(lang);
  RETURN QUERY EXECUTE q;
  END; $$
  LANGUAGE 'plpgsql' STABLE STRICT LEAKPROOF PARALLEL SAFE SECURITY DEFINER;
  
  CREATE ROLE api_anonymous WITH NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
  GRANT EXECUTE ON FUNCTION find_component_by_hash(hash TEXT) TO api_anonymous;
  GRANT EXECUTE ON FUNCTION find_component_by_hash(hash TEXT, lang TEXT) TO api_anonymous;
  -- GRANT SELECT ON untracked TO api_anonymous;
  