[DB-GOV-01] Designing the Government Registry and Access Tokens Table Schema for the SOAP Service

Database: Initialization

Published

October 14, 2026

NoteTask Brief
  • Component: gov_registry_db (PostgreSQL).
  • Task Type: Database / Data Schema.
  • Target Service: Fake Governmental Identity SOAP Platform (main.go).
  • Status: Ready for Implementation

Task Description

To ensure the correct operation of the implemented SOAP handler that validates master tokens and retrieves employee personal data by email, it is necessary to design and execute a DDL migration for the database structure in PostgreSQL.

Step-by-Step Instructions (SQL)

  1. Creating the Authorization Tokens Table: Write an SQL script to create the service_access_tokens table. The table must contain the following fields validated by the source code:
    • token_value (VARCHAR/TEXT, PRIMARY KEY) — for sanitized token values.
    • is_active (BOOLEAN) — token activity flag (utilized when scanning the isTokenActive variable).
  2. Creating the Digital Employees Table: Write an SQL script to create the digital_employees table with the following field configurations based on the GovEmployeeProfile structure:
    • user_id (SERIAL / INTEGER PRIMARY KEY).
    • email (VARCHAR, UNIQUE, indexed for rapid performance lookup during WHERE email = $1).
    • inn (VARCHAR).
    • passport_number (VARCHAR).
    • first_name (VARCHAR).
    • last_name (VARCHAR).
    • digital_signature_status (VARCHAR).
    • insurance_number (VARCHAR) — maps directly to the SNILS structure field.
    • gov_status (VARCHAR) — operational field to store restriction statuses (ACTIVE / BLOCKED).