Handelsregister App

Appearance

RPC Functions ​

This document describes the Remote Procedure Call (RPC) functions available in the Handelsregister application database.

store_discovery_results ​

Summary: A worker-facing function that receives the results of a basic discovery search and writes the outcome to the database.

Context: This is the primary entry point for the basic discovery worker after it has queried the Handelsregister API. The function encapsulates all the business logic required to handle the different outcomes (e.g., too many results, a few results, etc.). It ensures that the searches, processing_jobs, entities, and optionally bulk_search_items tables are all updated atomically and consistently based on the result count.

The function supports both regular searches and bulk search operations. When bulk_search_item_id is present in the job data, the function updates the corresponding bulk_search_items entry with the appropriate status and results.

Signature:

sql
store_discovery_results(p_job_id uuid, p_search_id uuid, p_result_count integer, p_results jsonb) RETURNS void

Parameters:

NameTypeRequiredDescription
p_job_iduuidYesThe ID of the processing_jobs entry that the worker processed.
p_search_iduuidYesThe ID of the original search from the public.searches table.
p_result_countintegerYesThe total number of results found by the worker.
p_resultsjsonbYesA JSON array of the companies found. The structure depends on the path taken.

p_results JSON Structure:

A JSON array of objects. Each object must contain the following fields:

  • name (text)
  • legal_form (text)
  • register_court (text)
  • register_number (text)
  • address (text)
  • seat (text)
  • state (text)
  • raw (text)
  • register_type (text)
  • registration_status (text)
  • hrapi_id (text, optional - used for fallback API tracking)
  • si_document_xml (text, only for Path A, can be null)
  • si_document_json (jsonb, only for Path A when using PDF fallback, can be null)

  • For Path A (≤3 results): A complete object including either si_document_xml or si_document_json (or both).

    json
    [{
  "name": "Example GmbH",
  "legal_form": "Gesellschaft mit beschränkter Haftung",
  "register_court": "District Court Berlin (Charlottenburg)",
  "register_number": "123456",
  "address": "Beispielstraße 1, 10117 Berlin",
  "seat": "Berlin",
  "state": "active",
  "raw": "raw response data",
  "register_type": "HRB",
  "registration_status": "currently registered",
  "hrapi_id": "12345",
  "si_document_xml": "<xml>...</xml>",
  "si_document_json": null
}]

    OR with PDF fallback:

    json
    [{
  "name": "Example GmbH",
  "legal_form": "Gesellschaft mit beschränkter Haftung",
  "register_court": "District Court Berlin (Charlottenburg)",
  "register_number": "123456",
  "address": "Beispielstraße 1, 10117 Berlin",
  "seat": "Berlin",
  "state": "active",
  "raw": "raw response data",
  "register_type": "HRB",
  "registration_status": "currently registered",
  "hrapi_id": "12345",
  "si_document_xml": null,
  "si_document_json": {"shareholders": [...], "capital": {...}}
}]

  • For Path B (4-20 results): The object without si_document_xml or si_document_json.

    json
    [{
  "name": "Example AG",
  "legal_form": "Aktiengesellschaft",
  "register_court": "District Court Munich",
  "register_number": "789012",
  "address": "Musterstraße 2, 80331 München",
  "seat": "Munich",
  "state": "active",
  "raw": "raw response data",
  "register_type": "HRB",
  "registration_status": "currently registered",
  "hrapi_id": "67890"
}]

  • For Path C (>20 results): An empty array [].

Core Logic & Behavior:

The function first validates the p_job_id to ensure it exists and is in a processing state. It uses a FOR UPDATE lock to prevent concurrent modifications. It then proceeds based on the p_result_count using defined constants:

  • MAX_RESULTS_FOR_AUTO_DOWNLOAD = 3
  • MAX_RESULTS_FOR_USER_SELECTION = 20

  • Path C: Too Many Results

    • Trigger: p_result_count > 20.
    • Actions:
      • Updates public.searches status to result_count_exceeds_limit (if p_search_id is provided).
      • Updates bulk_search_items status to 'failed' with error message if bulk_search_item_id is present in job data.
      • Updates the processing.processing_jobs entry with status completed and a descriptive result message (properly cast to jsonb).
      • No entities are created.

  • Path B: User Selection List

    • Trigger: p_result_count is between 4 and 20.
    • Actions:
      • Updates public.searches status to completed and stores results_count (if p_search_id is provided).
      • For each item in p_results, checks if an entity with the same register_number already exists.
      • If entity exists, updates it with new data; if not, creates a new entity with processing_status set to discovered.
      • Updates hrapi_id if provided (used for fallback API tracking).
      • Creates linking entries in public.search_entities using ON CONFLICT DO NOTHING (only if p_search_id is provided).
      • Updates bulk_search_items status to 'failed' with error message if bulk_search_item_id is present in job data.
      • Marks the processing.processing_jobs entry as completed.

  • Path A: Auto-Download

    • Trigger: p_result_count is between 1 and 3.
    • Actions:
      • Updates public.searches status to completed and stores results_count (if p_search_id is provided).
      • For each item in p_results, checks if an entity with the same register_number already exists.
      • If entity exists, updates it with new data including XML/JSON; if not, creates a new entity.
      • Processing Status Logic:
        • If si_document_json IS NOT NULL: Sets processing_status = 'basic_discovery_complete' (parsing already done by PDF fallback).
        • Otherwise: Sets processing_status = 'xml_ready' (needs XML parsing or waiting for fallback).
      • Sets si_document_retrieved_at to current timestamp when XML or JSON is provided.
      • Updates hrapi_id if provided (used for fallback API tracking).
      • Creates linking entries in public.search_entities using ON CONFLICT DO NOTHING (only if p_search_id is provided).
      • Updates bulk_search_items status to 'processing' if bulk_search_item_id is present in job data.
      • Marks the processing.processing_jobs entry as completed.

  • No Results

    • Trigger: p_result_count is 0.
    • Actions:
      • Updates public.searches status to completed with a results_count of 0 (if p_search_id is provided).
      • Updates bulk_search_items status to 'failed' with error message if bulk_search_item_id is present in job data.
      • Marks the processing.processing_jobs entry as completed.

Return Value: The function returns VOID on a successful transaction. If any error occurs, it throws an exception instead.

Error Handling:

  • The function is transactional. All database writes are performed as a single unit; if any step fails, the entire transaction is rolled back, leaving the database in its original state.
  • Graceful degradation: If the job is not found or is not in a running state, the function silently returns without throwing exceptions. This prevents errors from invalid job states that may occur due to race conditions or worker restarts.

Permissions: This function is intended to be called by a trusted service_role client, such as the backend worker.

retry_shareholder_parsing ​

Summary: A user-facing function that creates a new shareholder parsing job after a previous parsing attempt has failed.

Context: This function is called from the frontend when a user clicks a "retry" button after shareholder parsing has failed. It validates that the entity is in a parsing_failed state, retrieves the document ID from the previous failed job, and creates a new parsing job to retry the operation.

Signature:

sql
retry_shareholder_parsing(p_entity_id UUID) RETURNS JSONB

Parameters:

NameTypeRequiredDescription
p_entity_idUUIDYesThe ID of the entity for which to retry shareholder parsing.

Return Value:

Returns a JSONB object with the following structure:

json
{
  "job_id": "uuid-of-new-job",
  "error": null,
  "retry": false
}
FieldTypeDescription
job_idUUID or nullThe ID of the newly created parsing job, or null if an error occurred.
errorstring or nullError message if the operation failed, null on success.
retrybooleanWhether the client should retry the request (true for transient errors like deadlocks).

Core Logic & Behavior:

  • Entity Status Validation:

    • Trigger: The function is called.
    • Actions:
      • Locks the entity row using FOR UPDATE.
      • Validates that the entity exists.
      • Validates that the entity's shareholder_research_status is parsing_failed.
      • Returns error if validation fails.

  • Document Retrieval:

    • Trigger: Entity validation succeeds.
    • Actions:
      • Queries processing.processing_jobs for the most recent failed shareholder_parsing job for this entity.
      • Extracts the document_id from the job's data field.
      • Returns error if no failed job is found.

  • Entity Status Update:

    • Trigger: Document ID is successfully retrieved.
    • Actions:
      • Updates the entity's shareholder_research_status to parsing.
      • Sets the updated_at timestamp.

  • Job Creation:

    • Trigger: Entity status is successfully updated.
    • Actions:
      • Creates a new shareholder_parsing job in processing.processing_jobs.
      • Sets job status to queued.
      • Stores the document_id in the job's data field as {"document_id": "..."}.
      • Links the job to the entity via entity_id.

Error Cases:

Error MessageCauseRetry?
"Entity with id ... does not exist"Invalid entity ID providedNo
"Invalid entity status: ... (expected parsing_failed)"Entity is not in parsing_failed stateNo
"No failed parsing job found for entity"No previous failed parsing job existsNo
"Deadlock detected: ..."Database deadlock occurredYes
Other SQL errorsUnexpected database errorNo

Usage Example:

typescript
const { data, error } = await supabase.rpc('retry_shareholder_parsing', {
  p_entity_id: 'entity-uuid-here'
});

if (error) {
  console.error('Failed to retry parsing:', error);
} else if (data.error) {
  console.error('RPC error:', data.error);
  if (data.retry) {
    // Transient error, retry the request
  }
} else {
  console.log('New parsing job created:', data.job_id);
}

Notes:

  • This function is user-facing and should be called with proper authentication checks.
  • The function uses FOR UPDATE locking to prevent race conditions.
  • The new job will be picked up by the shareholder parsing worker automatically.
  • The function preserves the original document ID, so the same file will be re-parsed.

Permissions: This function should be accessible to authenticated users who have permission to manage the entity. It is not exposed to the public API.

store_xml_parsing_results ​

Summary: A worker-facing function that stores the results of the XML parsing phase.

Context: This function is called by the XML parsing worker after it has successfully extracted structured data from a company's XML document. It updates the target entity with the parsed JSON data, moves the entity to the next stage in the processing pipeline, and marks the parsing job as complete.

Signature:

sql
store_xml_parsing_results(p_job_id uuid, p_entity_id uuid, p_results jsonb, p_address text DEFAULT NULL) RETURNS void

Parameters:

NameTypeRequiredDescription
p_job_iduuidYesThe ID of the xml_parsing job from the processing.processing_jobs table.
p_entity_iduuidYesThe ID of the content.entities record to update with the results.
p_resultsjsonbYesThe structured JSON data extracted from the XML.
p_addresstextNoAn optional updated address for the entity. If NULL, the existing address is kept.

p_results JSON Structure:

A JSON object containing the key-value pairs extracted from the XML document. The structure is flexible but should be consistent.

json
{
  "company_details": {
    "name": "Example Company GmbH",
    "legal_form": "GmbH"
  },
  "registration": {
    "court": "Amtsgericht Charlottenburg",
    "number": "12345 B"
  },
  "capital": "25000 EUR"
}

Core Logic & Behavior:

The function performs the following steps in a single transaction:

  • Job and Entity Validation:

    • Trigger: The function is called.
    • Actions:
      • Locks the job and entity rows using FOR UPDATE to prevent race conditions.
      • Validates that the processing.processing_jobs entry exists, its type is xml_parsing, and its status is running.
      • Validates that the content.entities record exists and its processing_status is xml_parsing_running.
      • Raises an exception if any validation fails.

  • Entity Update:

    • Trigger: Validation succeeds.
    • Actions:
      • Updates the content.entities record.
      • Sets the si_document_json field to the p_results value.
      • Updates the address only if p_address is not NULL.
      • Changes the processing_status to basic_discovery_complete.
      • Updates the updated_at timestamp.

  • Job Completion:

    • Trigger: The entity is successfully updated.
    • Actions:
      • Updates the processing.processing_jobs entry.
      • Sets the status to completed.
      • Stores a success message in the result field.
      • Sets the completed_at timestamp.

Return Value: The function returns VOID on successful completion.

Error Handling:

  • The function is transactional. If any step fails, the entire operation is rolled back.
  • Graceful degradation: If the job is not found, is not in a running state, is not of type xml_parsing, or if the entity is not found or not in xml_parsing_running state, the function silently returns without throwing exceptions. This prevents errors from invalid states that may occur due to race conditions or worker restarts.

Permissions: This function is intended to be called by a trusted service_role client, such as the backend worker.

record_job_error ​

Summary: Provides a centralized and consistent way for workers to report job failures.

Context: This is the single entry point for marking a job as failed. It ensures that all errors are stored in a consistent, structured format and prevents workers from needing to construct complex JSON objects themselves. It gracefully handles edge cases where a job might not be in a fail-able state.

Signature:

sql
record_job_error(p_job_id uuid, p_error_source text, p_error_type text, p_error_sub_type text, p_error_severity text, p_error_message text) RETURNS void

Parameters:

NameTypeRequiredDescription
p_job_iduuidYesThe ID of the processing_jobs entry that has failed.
p_error_sourcetextYesThe origin of the error (e.g., worker, rpc_function).
p_error_typetextYesA broad categorization of the error (e.g., api_error, validation_error).
p_error_sub_typetextYesA specific, machine-readable code for the error (e.g., api_timeout, job_not_found).
p_error_severitytextYesIndicates the error's impact (1 - 5 (most severe)).
p_error_messagetextYesA detailed, human-readable description of the error.

Core Logic & Behavior:

  • Job Validation:
    • Trigger: The function is called.
    • Actions:
      • Finds the job by p_job_id and locks the row.
      • If the job is not found, it logs a warning and exits.
      • If the job's status is not running, it logs a warning that the job cannot be failed and exits. This prevents race conditions where a job might have been handled by another process.
  • Error Recording:
    • Trigger: The job is found and is in a running state.
    • Actions:
      • Constructs a standard, structured jsonb error object.
      • Updates the job's status to failed, sets the result field to the error object, and records the completed_at timestamp.
      • This update fires the on_job_failure trigger, which handles the retry/final failure logic.

Return Value: The function returns VOID. It does not throw exceptions for invalid job states, allowing workers to "fire and forget" without needing complex error handling for cases they cannot fix.

Permissions: This function is intended to be called by trusted service_role clients, such as backend workers. It uses the default SECURITY INVOKER model.

start_entity_basic_discovery ​

Summary: Initiates the basic discovery process for an entity by updating its processing status from 'discovered' to 'basic_discovery_running'.

Context: This function is part of Path B in the technical flow, where users manually select specific entities from a list of 4-20 discovered results. After a user chooses which entities they want to process further, this function updates the entity's status to trigger the next phase of processing. A database trigger (to be implemented) will automatically create basic discovery jobs for entities with this status.

Signature:

sql
start_entity_basic_discovery(entity_id uuid) RETURNS json

Parameters:

NameTypeRequiredDescription
entity_iduuidYesThe unique identifier of the entity to start basic discovery for

Core Logic & Behavior:

  • Authentication Check:

    • Trigger: Function is called.
    • Actions:
      • Verifies user is authenticated via auth.uid().
      • Returns error if not authenticated.

  • Entity Validation:

    • Trigger: After authentication passes.
    • Actions:
      • Checks if entity exists in content.entities.
      • Validates current processing_status is 'discovered'.
      • Returns specific error for entity not found or invalid status.

  • Status Update:

    • Trigger: All validations pass.
    • Actions:
      • Updates processing_status to 'basic_discovery_running'.
      • Sets updated_at to current timestamp.
      • Uses conditional WHERE clause to prevent race conditions.

Return Value: Returns a JSON object indicating success or failure. On success, includes a confirmation message. On failure, includes specific error details and current status when applicable.

Error Handling:

  • Non-transactional: Returns JSON error responses instead of throwing exceptions.
  • Comprehensive validation: Checks authentication, entity existence, and valid status transitions.
  • Race condition protection: Uses conditional UPDATE to ensure only entities with 'discovered' status are modified.

Permissions: This function uses SECURITY DEFINER and requires user authentication. It's intended to be called by authenticated frontend users who want to process specific entities from their search results.

start_deep_research ​

Initiates the deep research process for an entity by updating its processing status from 'basic_discovery_complete' to 'deep_research_ready' and creating a processing job.

  • Writes to: processing.processing_jobs, content.entities

Function Signature ​

sql
CREATE OR REPLACE FUNCTION start_deep_research(entity_id UUID)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER

Parameters ​

  • entity_id (UUID): The unique identifier of the entity to start deep research for

Return Value ​

Returns a JSONB object with the following structure:

Success Response:

json
{
  "success": true
}

Error Responses:

json
{
  "success": false,
  "error": "Authentication required"
}
json
{
  "success": false,
  "error": "Basic discovery not complete"
}

Notes ​

  • Requires authentication
  • Only works on entities with status 'basic_discovery_complete'
  • Creates a processing job of type 'collection' with status 'queued'
  • Part of the processing pipeline: basic_discovery_complete → deep_research_ready
  • The worker will update the status to 'deep_research_running' when it starts processing

record_file_and_document ​

Summary: A function to register a successfully uploaded file into the database, creating records in both content.files and content.documents.

Context: This is the primary entry point after a file has been successfully uploaded to the storage bucket. The function is designed to handle file deduplication by checking the file's hash. If the file already exists in content.files, it reuses the existing record; otherwise, it creates a new one. It then creates a new content.documents record that links the file to a specific entity and stores its associated metadata.

Signature:

sql
record_file_and_document(
    p_file_hash TEXT,
    p_storage_path TEXT,
    p_file_size_bytes BIGINT,
    p_mime_type TEXT,
    p_entity_id UUID,
    p_original_filename TEXT,
    p_parent_document_id UUID DEFAULT NULL,
    p_display_name TEXT DEFAULT NULL,
    p_hr_document_path TEXT DEFAULT NULL,
    p_document_date DATE DEFAULT NULL,
    p_received_on DATE DEFAULT NULL,
    p_published_on DATE DEFAULT NULL,
    p_created_by TEXT DEFAULT NULL,
    p_type_of_document TEXT DEFAULT NULL,
    p_language_identifier TEXT DEFAULT NULL
) RETURNS UUID

Parameters:

NameTypeRequiredDescription
p_file_hashTEXTYesThe SHA256 hash of the file's content, used for deduplication.
p_storage_pathTEXTYesThe full path to the file in the Supabase storage bucket.
p_file_size_bytesBIGINTYesThe size of the file in bytes.
p_mime_typeTEXTYesThe MIME type of the file (e.g., 'application/pdf').
p_entity_idUUIDYesThe ID of the content.entities record this document belongs to.
p_original_filenameTEXTYesThe original filename from the Handelsregister.
p_parent_document_idUUIDNoIf extracted from an archive, this points to the parent document.
p_display_nameTEXTNoA clean, human-readable name for the document.
p_hr_document_pathTEXTNoThe navigational path from the Handelsregister portal (e.g., "VÖ/1/2").
p_document_dateDATENoThe date printed on the document itself.
p_received_onDATENoThe date the document was received by the register.
p_published_onDATENoThe date the document was officially published.
p_created_byTEXTNoThe source provided by the handelsregister.
p_type_of_documentTEXTNoThe specific type of the document (e.g., 'Gesellschafterliste', 'Jahresabschluss').
p_language_identifierTEXTNoThe language of the document

Core Logic & Behavior:

The function executes the following steps within a single transaction:

  • File Deduplication:

    • Trigger: The function is called.
    • Action: It first queries the content.files table for a row with a matching p_file_hash.
    • If a match is found: It uses the id of the existing file record.
    • If no match is found: It inserts a new row into content.files with the provided file metadata and retrieves the new id.

  • Document Creation:

    • Trigger: After the file ID has been determined (either found or created).
    • Action: It inserts a new row into the content.documents table, linking the document to the entity (p_entity_id) and the physical file (file_id). It populates all provided metadata, including the optional fields.

Return Value: The function returns the UUID of the newly created row in the content.documents table.

Error Handling:

  • The function is transactional. If any step fails (e.g., a required parameter is missing, or a foreign key constraint is violated), the entire transaction is rolled back, ensuring no partial data is left in the database.
  • It relies on database constraints to ensure data integrity. For example, trying to insert a document for a non-existent p_entity_id will fail.

Permissions: This function uses SECURITY DEFINER, allowing it to be called by roles that do not have direct INSERT permissions on the underlying content.files or content.documents tables. It is intended to be called by a trusted service_role client or an authenticated user who has successfully completed a file upload.

request_shareholder_research ​

Summary: A user-facing function to initiate shareholder research for a specific entity.

Context: This function is triggered when a user clicks the button to request shareholder information for a company. It validates that shareholder research hasn't already been initiated, updates the entity's status to indicate research is ready to begin, and creates a download job. This is the entry point for a two-stage workflow: download first, then parse.

Signature:

sql
request_shareholder_research(p_entity_id UUID) RETURNS UUID

Parameters:

NameTypeRequiredDescription
p_entity_idUUIDYesThe ID of the entity to research shareholders for.

Core Logic & Behavior:

  • Entity Validation:

    • Trigger: The function is called.
    • Actions:
      • Checks if the entity exists by querying its shareholder_research_status.
      • Raises an exception if the entity is not found.
      • Validates that the current status is not_started.
      • Raises an exception if research has already been initiated (any status other than not_started).

  • Status Update:

    • Trigger: Validation succeeds.
    • Actions:
      • Updates the entity's shareholder_research_status to ready.
      • Sets the updated_at timestamp.

  • Job Creation:

    • Trigger: The entity status is successfully updated.
    • Actions:
      • Inserts a new row into processing.processing_jobs.
      • Sets the job type to shareholder_download (first stage).
      • Sets the job status to queued.
      • Links the job to the entity via entity_id.

Return Value: Returns the UUID of the newly created download job.

Error Handling:

  • The function is transactional. If any step fails, the entire operation is rolled back.
  • Explicit exceptions: Raises exceptions for entity not found or invalid status, allowing the frontend to display appropriate error messages to the user.

Permissions: This function uses SECURITY DEFINER and is intended to be called by authenticated frontend users.

Workflow: This initiates a two-stage process:

  1. Download stage: Worker downloads the shareholder list document and stores it via store_shareholder_download_results
  2. Parsing stage: Automatically created after download, extracts data via LLM and stores via store_shareholder_parsing_results

store_shareholder_download_results ​

Summary: A worker-facing function that records successful download of a shareholder list document and queues it for parsing.

Context: This function is called by the shareholder download worker after it has successfully downloaded a shareholder list document from the Handelsregister and stored it via record_file_and_document. By separating download from parsing, we ensure the expensive file download only happens once, even if LLM extraction fails and needs retry. This respects Handelsregister rate limits.

Signature:

sql
store_shareholder_download_results(p_job_id UUID, p_entity_id UUID, p_document_id UUID) RETURNS void

Parameters:

NameTypeRequiredDescription
p_job_idUUIDYesThe ID of the shareholder_download job from the processing.processing_jobs table.
p_entity_idUUIDYesThe ID of the entity whose document was downloaded.
p_document_idUUIDYesThe ID of the stored document in content.documents.

Core Logic & Behavior:

  • Job and Entity Validation:

    • Trigger: The function is called.
    • Actions:
      • Locks the job and entity rows using FOR UPDATE.
      • Validates that the processing.processing_jobs entry exists, its type is shareholder_download, and its status is running.
      • Validates that the entity's shareholder_research_status is downloading.
      • Silently returns if validation fails (graceful degradation for race conditions).

  • Entity Status Update:

    • Trigger: Validation succeeds.
    • Actions:
      • Updates the entity's shareholder_research_status to downloaded.
      • Sets the updated_at timestamp.

  • Download Job Completion:

    • Trigger: The entity status is successfully updated.
    • Actions:
      • Updates the download job's status to completed.
      • Stores a success message in the result field.
      • Sets the completed_at timestamp.

  • Parsing Job Creation:

    • Trigger: The download job is marked complete.
    • Actions:
      • Creates a new shareholder_parsing job.
      • Sets status to queued.
      • Stores the document_id in the job's data field for the parsing worker.

Return Value: The function returns VOID on successful completion.

Error Handling:

  • The function is transactional. If any step fails, the entire operation is rolled back.
  • Graceful degradation: If the job or entity is not in the expected state, the function silently returns without throwing exceptions, preventing errors from race conditions or worker restarts.

Permissions: This function is intended to be called by a trusted service_role client, such as the backend worker.

store_shareholder_parsing_results ​

Summary: A worker-facing function that stores the extracted shareholder data and completes the shareholder research process.

Context: This function is called by the shareholder parsing worker after it has successfully processed a downloaded shareholder list document through an LLM and extracted structured shareholder information. It validates the job and entity states, inserts all shareholders into the database, and marks the research as complete.

Signature:

sql
store_shareholder_parsing_results(p_job_id UUID, p_entity_id UUID, p_source_document_id UUID, p_shareholders JSONB) RETURNS void

Parameters:

NameTypeRequiredDescription
p_job_idUUIDYesThe ID of the shareholder_parsing job from the processing.processing_jobs table.
p_entity_idUUIDYesThe ID of the entity whose shareholders are being stored.
p_source_document_idUUIDYesThe ID of the source document in content.documents from which the data was extracted.
p_shareholdersJSONBYesA JSON array of shareholder objects.

p_shareholders JSON Structure:

A JSON array of objects, where each object represents a shareholder. Fields differ based on shareholder_type:

For natural persons:

json
{
  "shareholder_type": "natural_person",
  "first_name": "Max",
  "last_name": "Mustermann",
  "date_of_birth": "1980-05-15",
  "residence": "Berlin",
  "share_nominal_amount": 25000,
  "share_percentage": 50.0,
  "sequence_number": 1
}

For organizations:

json
{
  "shareholder_type": "organization",
  "company_name": "Example Holding GmbH",
  "register_court": "Amtsgericht München",
  "register_number": "HRB 12345",
  "seat": "München",
  "foreign_entity": false,
  "share_nominal_amount": 25000,
  "share_percentage": 50.0,
  "sequence_number": 2
}

Complete example:

json
[
  {
    "shareholder_type": "natural_person",
    "first_name": "Max",
    "last_name": "Mustermann",
    "date_of_birth": "1980-05-15",
    "residence": "Berlin",
    "share_nominal_amount": 25000,
    "share_percentage": 50.0,
    "sequence_number": 1
  },
  {
    "shareholder_type": "organization",
    "company_name": "Example Holding GmbH",
    "register_court": "Amtsgericht München",
    "register_number": "HRB 12345",
    "seat": "München",
    "foreign_entity": false,
    "share_nominal_amount": 25000,
    "share_percentage": 50.0,
    "sequence_number": 2
  }
]

Core Logic & Behavior:

  • Job and Entity Validation:

    • Trigger: The function is called.
    • Actions:
      • Locks the job and entity rows using FOR UPDATE.
      • Validates that the processing.processing_jobs entry exists, its type is shareholder_parsing, and its status is running.
      • Validates that the entity's shareholder_research_status is parsing.
      • Silently returns if validation fails (graceful degradation for race conditions).

  • Shareholder Data Insertion:

    • Trigger: Validation succeeds.
    • Actions:
      • Loops through each object in the p_shareholders JSONB array.
      • Inserts each shareholder into content.entity_shareholders.
      • Links each shareholder to the entity via entity_id and to the source document via source_document_id.

  • Entity Status Update:

    • Trigger: All shareholders are successfully inserted.
    • Actions:
      • Updates the entity's shareholder_research_status to complete.
      • Sets the updated_at timestamp.

  • Job Completion:

    • Trigger: The entity status is successfully updated.
    • Actions:
      • Updates the job's status to completed.
      • Stores a success message in the result field.
      • Sets the completed_at timestamp.

Return Value: The function returns VOID on successful completion.

Error Handling:

  • The function is transactional. If any step fails, the entire operation is rolled back.
  • Graceful degradation: If the job or entity is not in the expected state, the function silently returns without throwing exceptions, preventing errors from race conditions or worker restarts.

Permissions: This function is intended to be called by a trusted service_role client, such as the backend worker.