CresignSys Learn

CresignSys Learn — Lesson 080

Written by

cresignsys

in

Implement hosting-db-import

This lesson turns the previous design into an actual migration workflow.

The command will be:

sudo hosting-db-import example.com

and the safest first version will support:

sudo hosting-db-import example.com --dry-run
sudo hosting-db-import example.com
sudo hosting-db-import example.com --refresh

The importer will not modify the website.

1. Import Pipeline

hosting-db-import
        │
        ▼
Validate domain
        │
        ▼
Locate website
        │
        ▼
Discover metadata
        │
        ├── Files
        ├── Owner
        ├── WordPress
        ├── Database
        ├── PHP-FPM
        ├── Nginx
        ├── DNS
        ├── SSL
        └── Backup
        │
        ▼
Discovery report
        │
        ▼
Validation
        │
        ▼
Database transaction
        │
        ▼
Verify
        │
        ▼
VERIFIED

2. First Create the Database

Before importing sites, make sure the CHP database exists.

sudo hosting-db-init

Then verify:

sqlite3 /var/lib/cresignsys/chp.db ".tables"

You should see tables such as:

backups
certificate_domains
domains
operations
schema_migrations
services
sites
ssl_certificates

3. Create Discovery Library

Create:

sudo nano /etc/cresignsys/lib/discovery.sh

This library will contain read-only discovery functions.

Structure:

discovery.sh
├── discover_web_root
├── discover_owner
├── discover_application
├── discover_wordpress
├── discover_database
├── discover_php
├── discover_nginx
├── discover_dns
├── discover_ssl
└── discover_backups

4. Discovery Must Be Read-Only

The discovery functions must only perform operations such as:

stat
find
grep
awk
sed
dig
openssl
wp
mysql
nginx -T

They must not perform:

mkdir
rm
mv
cp
systemctl restart
systemctl reload
wp update
mysql ALTER

5. discover_web_root

Use the CHP convention first:

discover_web_root() {
    local domain="$1"

    local root="${WEB_ROOT}/${domain}/public"

    if [[ -d "$root" ]]; then
        printf '%s\n' "$root"
        return 0
    fi

    return 1
}

6. Existing Layouts

Don’t assume every existing site uses:

/storage/websites/domain/public

A future discovery system can search:

/storage/websites/domain/public
/storage/websites/domain
/var/www/domain/public
/var/www/domain

But for the first version, use your current CHP convention.

This reduces the chance of accidentally discovering an unrelated directory.

7. discover_owner

discover_owner() {
    local root="$1"

    stat -c '%U:%G' "$root"
}

Example:

www-data:www-data

8. discover_application

Start with:

discover_application() {
    local root="$1"

    if [[ -f "${root}/wp-config.php" ]] &&
       [[ -d "${root}/wp-admin" ]]; then
        printf 'WORDPRESS\n'
    elif [[ -f "${root}/index.html" ]]; then
        printf 'STATIC\n'
    elif [[ -f "${root}/index.php" ]]; then
        printf 'PHP\n'
    else
        printf 'UNKNOWN\n'
    fi
}

This is intentionally simple.

Later the application detector can become more sophisticated.

9. WordPress Detection

discover_wordpress() {
    local root="$1"

    [[ -f "${root}/wp-config.php" ]] &&
    [[ -d "${root}/wp-content" ]]
}

Then:

if discover_wordpress "$WEB_ROOT_PATH"; then
    WORDPRESS="YES"
else
    WORDPRESS="NO"
fi

10. WordPress Version

If detected:

sudo -u "$SITE_USER" \
wp --path="$WEB_ROOT_PATH" core version

But first verify that:

command -v wp

exists.

If WP-CLI isn’t available:

WordPress:
DETECTED

Version:
UNKNOWN

Don’t fail the entire import simply because WP-CLI isn’t installed.

11. WordPress Database Discovery

Use:

sudo -u "$SITE_USER" \
wp --path="$WEB_ROOT_PATH" config get DB_NAME

Then:

sudo -u "$SITE_USER" \
wp --path="$WEB_ROOT_PATH" config get DB_HOST

This is preferable to manually parsing wp-config.php because WordPress configuration can contain PHP expressions.

12. Database Password

Don’t retrieve:

DB_PASSWORD

unless absolutely required for a specific read-only verification.

The importer should avoid placing credentials in:

logs
terminal output
CHP database

13. Database Verification

Run:

sudo -u "$SITE_USER" \
wp --path="$WEB_ROOT_PATH" db check

If successful:

Database:
OK

If unsuccessful:

Database:
FAIL

Still continue discovery.

14. PHP-FPM Discovery

The preferred source is the Nginx configuration.

Find:

fastcgi_pass

For example:

fastcgi_pass unix:/run/php/php8.3-fpm.sock;

Extract:

8.3

or:

/run/php/php8.3-fpm.sock

15. Don’t Use Global PHP Version

Avoid:

php -v

as the website PHP version.

It can differ from the PHP-FPM version serving the site.

Correct model:

Nginx
 ↓
PHP-FPM socket
 ↓
PHP version

16. PHP-FPM Verification

Once the socket is discovered:

test -S "$PHP_SOCKET"

If true:

PHP socket:
OK

Then determine the corresponding service:

php8.3-fpm

and check:

systemctl is-active php8.3-fpm

17. Nginx Discovery

The safest source is:

nginx -T

but this produces a large amount of output.

Capture it once:

NGINX_DUMP="$(nginx -T 2>&1)"

Then use the captured output for the discovery process.

Don’t repeatedly execute:

nginx -T

for every field.

18. Search for server_name

You need to locate:

server_name example.com www.example.com;

and then associate it with:

root /storage/websites/example.com/public;

and:

fastcgi_pass ...

This creates a relationship:

DOMAIN
  ↓
SERVER BLOCK
  ├── root
  ├── PHP socket
  ├── SSL
  └── redirects

19. Nginx Configuration Path

Also determine the actual configuration file if possible.

For example:

/etc/nginx/sites-enabled/example.com.conf

Store the path:

nginx_config_path

in CHP metadata.

Don’t copy the entire configuration into SQLite.

20. DNS Discovery

Use:

dig +short A "$DOMAIN"

and:

dig +short AAAA "$DOMAIN"

Then compare with:

SERVER_IPV4
SERVER_IPV6

Possible states:

CORRECT
PENDING
WRONG
MULTIPLE
UNKNOWN

21. CNAME Discovery

dig +short CNAME "$DOMAIN"

If a CNAME exists:

CNAME:
example.com.

The importer should resolve the target and determine the final destination.

22. Nameserver Discovery

dig +short NS "$DOMAIN"

Store this in the discovery result if useful.

You don’t necessarily need to store every nameserver permanently in the domains table.

A future DNS-history table could do that.

23. SSL Discovery

First look for certificate configuration.

For Let’s Encrypt:

/etc/letsencrypt/live/example.com/

Check:

fullchain.pem
privkey.pem

Do not copy:

privkey.pem

into the CHP database.

24. Certificate Information

Read:

openssl x509 \
    -in /etc/letsencrypt/live/example.com/fullchain.pem \
    -noout \
    -issuer \
    -dates \
    -subject

This gives information such as:

issuer
notBefore
notAfter
subject

25. Certificate SANs

Check:

openssl x509 \
    -in /etc/letsencrypt/live/example.com/fullchain.pem \
    -noout \
    -ext subjectAltName

This may show:

DNS:example.com
DNS:www.example.com

Those domains can be mapped to the certificate.

26. SSL State

Calculate:

ACTIVE
EXPIRING_SOON
EXPIRED
MISSING
INVALID

For example:

Certificate:
ACTIVE

Expires:
2026-11-20

27. Backup Discovery

Look under:

/backup/cresignsys/example.com/

If the directory exists:

Backup:
DETECTED

List backup objects.

Don’t assume every file is valid.

28. Backup Metadata

For each backup:

path
timestamp
size
type

Initially:

status=DISCOVERED

Later:

hosting-backup-verify

can change it to:

VERIFIED

29. Build Discovery Object

Bash is not ideal for complex objects.

For the first implementation, use variables:

SITE_DOMAIN=""
WEB_ROOT_PATH=""
SITE_USER=""
SITE_GROUP=""
APPLICATION_TYPE=""
PHP_VERSION=""
PHP_SOCKET=""
NGINX_CONFIG=""
DATABASE_NAME=""
DATABASE_HOST=""
DNS_STATE=""
SSL_STATE=""
CERTIFICATE_PATH=""

This keeps the initial importer understandable.

30. Better Future Representation

Eventually the discovery engine can output JSON:

{
  "domain": "example.com",
  "web_root": "/storage/websites/example.com/public",
  "owner": "www-data:www-data",
  "application": "WORDPRESS",
  "php_version": "8.3",
  "php_socket": "/run/php/php8.3-fpm.sock",
  "dns_state": "CORRECT",
  "ssl_state": "ACTIVE"
}

Then the database importer consumes the JSON.

Don’t build that complexity until the basic importer works.

31. Create the Importer

sudo nano /usr/local/bin/hosting-db-import

Start:

#!/usr/bin/env bash

set -Eeuo pipefail

32. Parse Options

Support:

DOMAIN
--dry-run
--refresh

Example:

sudo hosting-db-import example.com --dry-run

Don’t use a large argument parser yet.

A simple loop is enough.

33. Validate Domain

source /etc/cresignsys/hosting.conf
source /etc/cresignsys/lib/common.sh
source /etc/cresignsys/lib/domain.sh
source /etc/cresignsys/lib/database.sh
source /etc/cresignsys/lib/discovery.sh

require_root

DOMAIN="$(normalize_domain "$DOMAIN")"
validate_domain "$DOMAIN"

34. Check Existing Database Record

Before importing:

SELECT id FROM sites WHERE primary_domain = ?

If it exists:

Site already imported.

Then:

normal mode → stop
--refresh → rediscover

35. Discovery

Run:

discover_web_root
discover_owner
discover_application
discover_wordpress
discover_database
discover_php
discover_nginx
discover_dns
discover_ssl
discover_backups

No writes to the website.

36. Display Discovery Report

Example:

CresignSys Site Discovery
=========================

Domain:
learn.cresignsys.com

Web Root:
 /storage/websites/learn.cresignsys.com/public

Owner:
 www-data:www-data

Application:
 WORDPRESS

WordPress:
 DETECTED

PHP:
 8.3

PHP-FPM:
 OK

Nginx:
 DETECTED

DNS:
 CORRECT

SSL:
 ACTIVE

Database:
 DETECTED

Backups:
 12 found

37. Discovery Validation

Before importing, verify minimum requirements:

domain found
web root found
site is recognizable

For example:

[[ -d "$WEB_ROOT_PATH" ]] ||
    die "Web root not found"

38. Don’t Require Every Component

A static site may not have:

PHP
MySQL
WordPress

Therefore:

PHP = NOT_APPLICABLE
Database = NOT_APPLICABLE

should be valid states.

39. Application-Aware Discovery

For WordPress:

WordPress → expected
PHP → expected
Database → expected

For static:

WordPress → N/A
PHP → N/A
Database → N/A

This prevents false errors.

40. Database Transaction

After discovery:

BEGIN;

Then insert site:

INSERT INTO sites (
    primary_domain,
    web_root,
    php_version,
    site_user,
    service_state,
    health_state,
    created_at,
    updated_at
)
VALUES (...);

Use the schema version you actually implemented, including management_state and application_type if those columns have already been migrated.

41. Retrieve Site ID

After insertion:

SELECT last_insert_rowid();

Store:

SITE_ID

For example:

SITE_ID=7

42. Insert Primary Domain

INSERT INTO domains (
    site_id,
    domain,
    domain_type,
    dns_state,
    ssl_state,
    created_at,
    updated_at
)
VALUES (...);

43. Insert Services

For WordPress:

NGINX
PHP_FPM
MYSQL
WORDPRESS

For a static site:

NGINX

only, unless additional services are discovered.

44. Insert SSL

If a certificate exists:

ssl_certificates

record:

site_id
certificate_path
issuer
expires_at
status

Then map:

certificate_domains

to the discovered domains.

45. Insert Backups

For each discovered backup:

backups

record:

site_id
backup_path
backup_type
size_bytes
status
created_at

Use:

DISCOVERED

until verified.

46. Insert Operation

Create:

operations

record:

operation=IMPORT
status=RUNNING
started_at=...

After successful import:

status=SUCCESS
finished_at=...

If something fails:

status=FAILED
message=...

47. Transaction Failure

If database insertion fails:

ROLLBACK;

The existing website remains untouched.

That is the major safety benefit.

48. Verify After Commit

After:

COMMIT;

perform read-only checks:

site exists
domain exists
services exist
SSL record correct
backup records correct

Then:

management_state=VERIFIED

if the schema supports that field.

49. Verify Against Live Server

Compare:

Database:
web_root=/storage/websites/example.com/public

with:

Actual:
directory exists

Compare:

Database:
PHP=8.3

with:

Actual:
PHP socket=8.3

Compare:

Database:
DNS=CORRECT

with:

Current DNS:
CORRECT

50. Import Result

Successful:

CresignSys Database Import
==========================

Domain:
learn.cresignsys.com

Site ID:
7

[OK] Site imported
[OK] Domain imported
[OK] Services imported
[OK] SSL imported
[OK] Backups imported
[OK] Live verification passed

Management:
VERIFIED

No website files were modified.

51. Dry Run

With:

sudo hosting-db-import learn.cresignsys.com --dry-run

the final output should be:

DRY RUN

Discovery successful.

No database changes made.
No website changes made.

52. Refresh

If the site already exists:

sudo hosting-db-import learn.cresignsys.com --refresh

the process becomes:

existing record
      ↓
discover actual state
      ↓
compare
      ↓
update metadata
      ↓
record REFRESH operation

Still:

No website modification.

53. Detect Drift During Refresh

Suppose CHP database says:

PHP=8.3

but actual site uses:

PHP=8.2

The refresh should report:

DRIFT DETECTED

PHP
CHP:    8.3
Actual: 8.2

Then update metadata only if your refresh policy says actual state should replace the stored observation.

54. Desired vs Observed Metadata

This reveals an important improvement.

Instead of only:

php_version

eventually use:

desired_php_version
observed_php_version

For example:

desired: 8.3
observed: 8.2

Then:

hosting-reconcile

can detect drift.

55. Add Desired/Observed Model Later

Don’t redesign the whole schema immediately.

The conceptual model is:

              CHP DATABASE
                   │
        ┌──────────┴──────────┐
        ▼                     ▼
     DESIRED               OBSERVED
        │                     │
        └──────────┬──────────┘
                   ▼
              RECONCILIATION

This will become important when automatic repair is introduced.

56. Import Existing CHP Websites

For your current server, start with:

sudo hosting-db-import learn.cresignsys.com --dry-run

Then:

sudo hosting-db-import learn.cresignsys.com

Then:

sudo hosting-site-status learn.cresignsys.com

Then:

sudo hosting-info learn.cresignsys.com

57. Verify No Files Changed

Before and after import, you can compare:

find /storage/websites/learn.cresignsys.com/public \
    -type f -printf '%p\n' | sort

The importer should not change this list.

For a more rigorous test, record checksums of important files before import and compare afterward.

58. Verify Nginx

Before import:

sudo nginx -t

After import:

sudo nginx -t

The result should remain:

syntax is ok
test is successful

And the importer itself should not have reloaded Nginx.

59. Verify WordPress

Run:

sudo -u www-data \
wp --path=/storage/websites/learn.cresignsys.com/public core is-installed

before and after.

Expected:

success

60. Verify Database

Run:

sudo -u www-data \
wp --path=/storage/websites/learn.cresignsys.com/public db check

before and after.

The importer must not change WordPress tables.

61. Import Safety Checklist

Before considering the importer production-ready:

[ ] Domain validation
[ ] Read-only discovery
[ ] Dry-run
[ ] Database transaction
[ ] Rollback on DB failure
[ ] No website modifications
[ ] No Nginx reload
[ ] No PHP restart
[ ] No WordPress update
[ ] No MySQL schema modification
[ ] No credential logging
[ ] Idempotent import
[ ] Refresh mode
[ ] Post-import verification
[ ] Audit operation recorded

62. Important Security Rule

Never print:

DB_PASSWORD
PRIVATE_KEY
API_TOKEN

to:

terminal
operations.log
errors.log
audit.log

If an exception or command output accidentally contains sensitive information, sanitize it before logging.

63. Import Errors

Use specific error codes.

For example:

SITE_NOT_FOUND
WEB_ROOT_NOT_FOUND
INVALID_DOMAIN
WORDPRESS_DETECTION_FAILED
DATABASE_UNAVAILABLE
NGINX_NOT_FOUND
SSL_DISCOVERY_FAILED
DATABASE_IMPORT_FAILED
VERIFICATION_FAILED

This is better than:

ERROR

64. Example Failure

CresignSys Database Import
==========================

Domain:
example.com

[OK] Web root
[OK] WordPress
[OK] PHP-FPM
[OK] Nginx
[FAIL] Database verification

Reason:
WordPress database connection failed.

Result:
IMPORT NOT PERFORMED

No website changes made.

This is the correct behavior.

65. Don’t Import Partially

If a required database operation fails:

BEGIN
 ↓
INSERT SITE
 ↓
INSERT DOMAIN
 ↓
INSERT SERVICE
 ↓
ERROR
 ↓
ROLLBACK

You should not end with:

site exists
domain missing

66. Operation History

After failure:

operations

should contain:

IMPORT
FAILED

with a safe message:

Database verification failed

Do not put the actual database password or sensitive command output into the message.

67. Importer Architecture

The final structure is:

hosting-db-import
       │
       ├── argument parser
       │
       ├── domain validation
       │
       ├── discovery.sh
       │      ├── filesystem
       │      ├── WordPress
       │      ├── database
       │      ├── PHP
       │      ├── Nginx
       │      ├── DNS
       │      ├── SSL
       │      └── backup
       │
       ├── discovery report
       │
       ├── validation
       │
       ├── database transaction
       │
       └── verification

68. What We Have Achieved

The server can now transition from:

WEBSITES EXIST

to:

CHP KNOWS ABOUT THE WEBSITES

without changing the existing sites.

That gives us:

Existing Websites
       ↓
CHP Database
       ↓
CHP Status
       ↓
CHP Monitoring
       ↓
Optional CHP Management

69. Current CHP Architecture

                         CRESIGNSYS
                             │
                ┌────────────┴────────────┐
                ▼                         ▼
          CONTROL PLANE               DATA PLANE
                │                         │
                ▼                         ▼
          CHP SQLite DB             Website Files
                │                    WordPress
       ┌────────┼────────┐           MySQL
       ▼        ▼        ▼
     Sites   Domains  Backups
       │        │
       ▼        ▼
   Services    SSL
       │
       ▼
  Operations
       │
       ▼
  Reconciliation
       │
       ▼
  Site Status

70. Lesson 080 — Core Principle

The importer is not a provisioning tool.

Its job is:

DISCOVER
   ↓
RECORD
   ↓
VERIFY

not:

DISCOVER
   ↓
CHANGE
   ↓
REBUILD

That separation is what makes migration safe.

Next Lesson — 081

Build hosting-reconcile

Now that CHP has both:

DESIRED/KNOWN STATE

and:

ACTUAL SERVER STATE

we can build the reconciliation engine.

Target:

sudo hosting-reconcile example.com

It will compare:

CHP DATABASE
     │
     ├── Domain
     ├── Web root
     ├── PHP
     ├── Nginx
     ├── SSL
     ├── DNS
     ├── WordPress
     └── Backup
            │
            ▼
       ACTUAL SERVER

and report:

MATCH
DRIFT
MISSING
UNEXPECTED
UNKNOWN

Most importantly, Lesson 081 should remain read-only initially. Only after reconciliation is reliable should CHP be allowed to automatically repair detected drift.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

More posts