Database Documentation

This document provides documentation for the database, including information about the tables and their structures.

Tables

Users Table

The Users table stores information about users.

Table Structures

Users Table

Creation

The construction of the table aligns with the constraints specified in the Affordable Connectivity Program - National Verify API Specifications document (v2.10) as much as reasonably possible. All columns directly correspond with properties that may be expected by that API except for userId which is internal to the database.

CREATE TABLE Users (
    userId INT IDENTITY(1,1) PRIMARY KEY,

    firstName NVARCHAR(50) NOT NULL,
    middleName NVARCHAR(50) NULL DEFAULT NULL,
    lastName NVARCHAR(50) NOT NULL,

    address NVARCHAR(100) NOT NULL,
    city NVARCHAR(50) NOT NULL,
    state NVARCHAR(2) NOT NULL,
    zipCode NVARCHAR(9) NOT NULL,
    CONSTRAINT zip_code CHECK (LEN(zipCode) = 5 OR LEN(zipCode) = 9),

    dob DATE NOT NULL,
    ssn4 CHAR(4) NOT NULL,
    CONSTRAINT UC_User_UniqueData UNIQUE (firstName, lastName, dob, ssn4),

    contactEmail NVARCHAR(100) NULL DEFAULT NULL,
    contactPhoneNumber CHAR(10) NULL DEFAULT NULL,

    bqpFirstName NVARCHAR(50) NULL DEFAULT NULL,
    bqpLastName NVARCHAR(50) NULL DEFAULT NULL,
    bqpDob DATE NULL DEFAULT NULL,
    bqpSsn4 CHAR(4) NULL DEFAULT NULL,

    eligibilityProgramCode NVARCHAR(50) NOT NULL,
);

Constraints

• The combination of firstName, lastName, dob, and ssn4 identify a user and must be unique, enforced by the UC_User_UniqueData constraint.

Installing the backend locally as a node module

To use the maxsip-backend module as a package, follow these steps:

Installation

Local Installation

To install this module as a local dependency in another project you can use this script:

# Create a compressed package of the backend module
npm pack relative_path/to/module_dir
# Install the module in your project
npm i -s <module_name>-<module_version>.tgz
# Cleanup: Remove the generated .tgz file if no longer needed
rm <module_name>-<module_version>.tgz

For example, from a project at the same level as this module, and using the module name maxsip-backend and with a version set to 1.0.0 this would be:

npm pack ../maxsip-backend
npm i -s maxsip-backend-1.0.0.tgz
rm maxsip-backend-1.0.0.tgz

Imports

The module has 4 exports that can be imported from backend-client:

1. User (Class) - sourced in User.ts
2. IUser (TS type) - sourced in User.ts
3. INewUser (TS type) - sourced in User.ts
4. DbUtil (Class) - sourced in DbUtil.ts

For more information about the nature of these exports, refer to the source code on Github.

Configuration

The module manages configuration using the config package and reads in 3 values:

1. db.server - The path for the azure DB server
2. db.user - The user for the azure DB server
3. db.name - The name for the azure DB
4. db.password - The password for the azure DB

Installing the backend through Github as a node module